Excel VBA Question

[Moredhel]

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:

    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.

 

[james.miller]

Loops are always slow in VB

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

 

[Moredhel]

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:

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

Perfect, thanks James!

Now back to writing stuff in a real language.

 

[Meeko]

I'd also recommend turning off ScreenUpdating and Calculation before the loop and then back on afterwards. This always improves performance.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

   *** YOUR CODE HERE ***

Application.Calcualtion = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[SimonCHere]

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

 

[Meeko]

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.