How do I loop through a spreadsheet just once?
Posted by RT on October 11, 2001 8:08 AM
I was wondering how to write a macro that would loop through a spreadsheet just one time. There doesn't seem to be an end of file marker...or at least I don't know the code for it...thanks for any help
Posted by Jonathan on October 11, 2001 3:18 PM
It will take a bit of time to loop through all 16,777,216 cells in the sheet, though it can be done. I don't know how fast a processor you have, but my meagre 200MHz takes about 10 minutes to loop through all those cells (I just happened to have tried that yesterday!) What is much faster, and is usually all that is required, is to loop through what is called the "UsedRange". This is the rectangle formed by the most-right used column and the most-down used row. So if, for example, there is data in E5 and that's the furthest right the data goes, and there's data in C10, and that's as far down as the data goes, then UsedRange = A1:E10.
Here's some code that loops through the used range and overwrites each cell in it with the text, "visited." Obviously you may want to do something else on your loop - it's just for demonstration:
Sub LoopSheet()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange
cel.Value = "visited"
Next cel
End Sub
HTH
