End Up vs. CurrentRegion.Rows Count

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I never heard of UsedRange. I looked it up in VBA help. Doesn't say much but I'm assuming it selects ALL data in the sheet whether there's blank rows or columns. If this is incorrect please correct me. If this IS correct, how wonderful!

As for avoiding loops, actually I usually do by something like:

MyData=Sheet1.Cells.CurrentRange.Rows
MyRange=MyData.Count
Range("A1:A" & MyRange)...

Is this ok or can it be improved?
 
Upvote 0
UsedRange and End(xlUp) do not necessarily yield the same result. A simple test. Enter something in E10; doesn't matter what, anything will do. Now, check the you get from Usedrange and range("F...").End(xlUp).

Also, if you want to use the End(xlUp) option, use Cells(rows.count,Columns("F").column).end(xlup)
 
Upvote 0
tbardoni said:
I never heard of UsedRange. I looked it up in VBA help. Doesn't say much but I'm assuming it selects ALL data in the sheet whether there's blank rows or columns. If this is incorrect please correct me. If this IS correct, how wonderful!

As for avoiding loops, actually I usually do by something like:

MyData=Sheet1.Cells.CurrentRange.Rows
MyRange=MyData.Count
Range("A1:A" & MyRange)...

Is this ok or can it be improved?
How about this ?

Intersect(Range("A:A"), ActiveSheet.UsedRange)

Also, where did you find CurrentRange ? do you mean CurrentRegion ? and do you want to avoid the possible blanks ? or do you want the whole area of the sheet to be "counted" ?
 
Upvote 0
Thanks Tusharm. I see what you're saying.

Yogi Anand,
That's what my original post asks. Is it better to use your suggestion? I guess I'm worried that all of my data won't be selected in using CurrentRegion. So, I'm looking for a "safer" solution.

I'm just trying to get some opinions on this matter.

Thanks to all!

Edit: Juan, I meant CurrentRegion.
 
Upvote 0
Another method for going thru a collection of cells is to use the Special Cells funciton. It only takes you to the cells that meet the specified criteria.

' Only go to cells that have numbers in them in Col A
For Each c In Range("A:A").SpecialCells(xlCellTypeConstants, 1)

Next c

'ONly go to cells that have number in the entire sheet
For Each c In Cells.SpecialCells(xlCellTypeConstants, 1)

Next c
 
Upvote 0
tbardoni said:
Thanks Tusharm. I see what you're saying.

Yogi Anand,
That's what my original post asks. Is it better to use your suggestion? I guess I'm worried that all of my data won't be selected in using CurrentRegion. So, I'm looking for a "safer" solution.

I'm just trying to get some opinions on this matter.

Thanks to all!

Edit: Juan, I meant CurrentRegion.

If you are trying to pickup the entire range (considering column F) including any potential blanks, then going up from cell F65536 to the first occupied cell that is encountered is indeed foolproof.
 
Upvote 0
Thanks, Yogi. I've been using CurrentRegion, but then it occurred to me that this could be a potential problem. I thank you again for your suggestion.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top