Does 'CurrentRegion' Include Cells With Formulas?

[DerekK]

I'm testing out some VBA on a sheet with only two rows: one row for headings, one row for data.

My VBA includes a For Each statment with a CurrentRegion property but the code seems to search the entire sheet, rather than just the "CurrentRegion." I know this because I've included an If Then statement with Else MsgBox "Not applicable" and it seems to want to display this message a full 65,536 times!

Therefore, might "CurrentRegion" include any formulas, as I have formulas copied all the way down to the last row? If so, how do I make sure the For Each applies only to rows where I have actual data?

If not, then what might be the problem?

Hope you can help...



-Derek

 

[snowblizz]

CurrentRegion should include everything until a blank row and column. So yes it appears your problem is having formulas "all the way".

You could most simply exclude "blank" formulas with an if check against the cells .Value property. It will still go through them but you shouldn't notice it.
I think UsedRange would operate under a similar limit as well. I think it includes formatted cells as well.

 

[DerekK]

Kiitos, jollaisia sir

I wound up substituting "End(xlUp)" in the code and it worked perfectly!