End Up vs. CurrentRegion.Rows Count

[Todd Bardoni]

What is everyone's opinion on the following. Often, I have to loop through large amounts of data inserting, say, formulas to the last row. I'm trying to figure what is overall best to use. I've been using something such as the following:

MyRegion=Sheet1.Cells.CurrentRegion.Rows
MyRows=MyRegion.Count

But, now I'm thinking for numerous reasons that this isn't so good. It might not select the entire range due to blank rows or columns, I think its kind of slow, etc.

Would it be better to use:

For Each ThisCell2 In ActiveSheet.Range("F1:F" & Range("F65536").End(xlUp).Row) (courtesy of phantom1975)

Just wondering.

 

[Tom Urtis]

Hi Yogi, one note of caution !!

You wrote:
"then going up from cell F65536 to the first occupied cell that is encountered is indeed foolproof."

Versions of Excel before 97 (Excel5 / Excel95, for instance, which is still in use by some) only have 16,384 rows in their worksheets, so the reference to 65,536 would return an error. And who knows, Microsoft might wake up and offer future versions with more than 65,536 rows (not rumored for the upcoming release of Office 2003 this summer unfortunately).

So, the more "foolproof" method would be (for column F in this example):
Cells(Rows.Count, 6).End(xlUp)

 

[Todd Bardoni]

How is it the best spreadsheet software in the world has some of the biggest downfalls (ie, 65,536 rows?) And how come Microsoft doesn't correct this? All that money and they can't fix this? Must be laziness...

Anyway, enough spouting off. Thanks for the input, Tom.

 

[Ekim]

See:
http://www.xl-logic.com/pages/vba.html

Download: "range_var_size.zip"

The file puts a new menu item in Excel "Selection_Demo" (disappears when you close the file).

Also from the same web site, look at "range_manip.zip".

HTH

Mike

 

[Yogi Anand]

So, the more "foolproof" method would be (for column F in this example):
Cells(Rows.Count, 6).End(xlUp)

Good point Tom -- I have seen you use this in some other posts as well. Thank You!