PDA

View Full Version : How to speed up loop



kublakhan
08-07-2007, 08:24 AM
The following code works, albeit slowly. Any tips on speeding it up? I'm guessing there must be a way to establish a smaller, discontiguous range that only includes cells that contain data and/or have been formatted.
Sub ColorLoop()

Dim cel As Range
Dim myWorksheet As Range

Set myWorksheet = Range("A1:IV65536")

For Each cel In myWorksheet
If cel.Font.ColorIndex = 5 Then
cel.Interior.ColorIndex = 36
End If
Next cel

End Sub

rory
08-07-2007, 08:26 AM
You could change this:
Set myWorksheet = Range("A1:IV65536")
to this:
Set myWorksheet = ActiveSheet.UsedRange

Regards,
Rory

kublakhan
08-07-2007, 08:45 AM
Works like a charm. Thanks for introducing me to UsedRange--it will come in handy.

rory
08-07-2007, 08:55 AM
You're welcome. You should just be aware that UsedRange is not always what you think it ought to be (Excel sometimes stubbornly remembers ranges that have had formatting applied as still being 'used') but for these purposes it is better than specifying every cell on the worksheet!

mdmackillop
08-07-2007, 09:23 AM
Also check out SpecialCells which you can use to ignore blanks etc.

Bob Phillips
08-07-2007, 12:04 PM
You're welcome. You should just be aware that UsedRange is not always what you think it ought to be (Excel sometimes stubbornly remembers ranges that have had formatting applied as still being 'used') but for these purposes it is better than specifying every cell on the worksheet!

But easily sorted http://www.contextures.com/xlfaqApp.html#Unused