Excel VBA Question

Soldato
Joined
11 May 2004
Posts
4,786
Location
Gloucester
For my sins I'm writing a macro in VBA, and while I've got it working exactly how I want, the performance isn't fantastic. The delay seems to be in a loop where I'm blanking the content of a load of cells, I know the starting cell and the height and width of the area I want to clear in terms of number of cells, so I was wondering if there's an easy way to convert that to a range and tell excel to just wipe the entire range?

The code I have is:
Code:
    For h = 1 To height
        For w = 1 To width
            sourcecell.Offset(h, w).Value = Empty
        Next w
    Next h

sourcecell is a Range object, height and width are integers.
 
Soldato
Joined
17 Aug 2003
Posts
20,001
Location
Woburn Sand Dunes
Loops are always slow in VB:p

you could replace all that with
range(cells(x1,y1),cells(x2,y2)).clear

where x1,y1 is the reference to the top left cell and x2,y2 is the bottom right in decimal

so for example to clear the a range of cells from a1 to g10, it'd be
range(cells(1,1),cells(10,7)).clear

you already know the starting cell (sourcecell) so you shouldnt have any problems clearing the right cells. try it with .select rather than .clear first so you dont clear the wrong cells;)
 
Soldato
OP
Joined
11 May 2004
Posts
4,786
Location
Gloucester
Heh, clearing the wrong ones isn't an issue, the data is extracted from a pivot table, which is populated from an OLAP cube from project server each time it's run first anyway, so accidentally deleting it all wouldn't really matter. :)

The issue now is that clear also clears the conditional formatting, can I just set them to empty?

Edit: Yes I can:

Code:
Range(sourcecell, sourcecell.Offset(height, width)).Value = Empty

Perfect, thanks James! :D

Now back to writing stuff in a real language. :p
 
Last edited:
Associate
Joined
3 Apr 2003
Posts
441
Location
Dunfermline
I'd also recommend turning off ScreenUpdating and Calculation before the loop and then back on afterwards. This always improves performance.

Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

   *** YOUR CODE HERE ***

Application.Calcualtion = xlCalculationAutomatic
Application.ScreenUpdating = True
 
Associate
Joined
24 Jun 2008
Posts
1,168
I'd agree with Meeko although I would say save the Calculation state beforehand and then set it back to what ever it was. it might not have been xlCalculationAutomatic.

Simon
 
Associate
Joined
3 Apr 2003
Posts
441
Location
Dunfermline
I'd agree with Meeko although I would say save the Calculation state beforehand and then set it back to what ever it was. it might not have been xlCalculationAutomatic.

Simon

You speaketh sense mate. All the stuff I work with has calculation as automatic as standard anyway, hence the habit of using that. But your quite right - I'd save the original state in a string and restore it to that rather than assuming Auto.
 
Top Bottom