To put it bluntly I very often avoid Loops, they are far too
slow in many cases. A common mistake we all make when first learning VBA is to use Loops when we really shouldn't. Take the simple example below for instance. It Loops through a range and places the word "Blank" in each blank cell within a
used range, i.e it assumes the last occupied cell is D500
Sub WrongWay()
Dim Bcell As Range
For Each Bcell In Range("A1:D500")
If IsEmpty(Bcell) Then Bcell = "Blank"
Next Bcell
End Sub
Now compare the above code to this one:
Sub RightWay()
If WorksheetFunction.CountA(Range("A1:D500")) = 0 Then
MsgBox "All cells are empty", vbOKOnly, "OzGrid.com"
Exit Sub
End If
On Error Resume Next
Range("A1:D500").SpecialCells(xlCellTypeBlanks) = "Blank"
On Error GoTo 0
End Sub
No comments:
Post a Comment