When I need to For..Next..Loop through an entire column I usually use ActiveSheet.UsedRange.Rows.Count to find where to stop. I’ve always had good luck with this approach.
I am also aware that occasionally Excel thinks the last row exists somewhere, but the row is actually empty. I’ve seen this a few times after importing data. From BeyondTechnology:
The Worksheet object’s UsedRange does not always work because the used range (or “dirty area”) of a spreadsheet may be larger than the area actually populated with your records.
I’m bookmarking the article Identifying the Real Last Cell as an alternative to UsedRange for looping through Columns, Rows, and Ranges.
January 25th, 2005 at 10:54 am
I usually use the following code to loop through a range (which should never have an empty cell):
Dim c as Range
For each c in worksheets(”MySheet”).Range(”A1:A65536″)
If c.Offset(1,0).Value “” Then ‘ double quote marks means empty cell
[Do my code here]
ElseIf c.Offset(1,0).Value = “” Then
Exit loop
Next c
This code will always verify if the next cell immediately following current cell being looped by the code is empty. If so it knows thats the end of the range and shold exit from the loop. However, your range sholud never have an empty cell otherwise the code would leave the loop before ending the range. But if you import lists from other places and you don’t know if there will be empty cells within the imported list, just write a code (For Each Next… Loop) to insert any character like “-” to “fill up” empty cells in the range to make you range without empty cells.
January 25th, 2005 at 9:26 pm
Elton,
The only drawback is I can’t expect the users of something I build to always verify there aren’t any blank cells in the data (there usually is), especially in a dataset of 20 or 30 thousand rows.
If there aren’t any missing data cells, I’d guess this is probably a very fast routine. Thanks for the code.