Using VBA to select a range within a range.

[GoBow777]

I’m trying to write code to select a range based on the visible data i
the range (D4203) and ignore the blank cells. In other words, I hav
formulas that populate the range to produce the data, so if I have te
items of data, the first ten rows (D4 to D13) will show that data an
the remaining rows will be blank. I just want to select the range o
visible data and not the blank cells.

Code

 

[JLatham]

You said that D4204 have formulas in them, and the .End() method considers
formulas to be something, not empty cells even if there is no result
displayed. Usually such a formula has some test to return "" (zero-length
string) if something wasn't as expected. So I'm assuming your formulas do
that in some fashion, like
=IF(E4>0,E4,"") in D4 or
=IF(ISNA(VLOOKUP(E4,F1:G99,2,FALSE)),"",VLOOKUP(E4,F1:G99,2,FALSE))
or some similar formula with "" involved in it.

If that's the case then this line of code should select what you want.

Range("D4:" & Range("D4").Offset( _
Application.WorksheetFunction.Match("", _
Range("D4204"), 0) - 2, 0).Address).Select

 

[GoBow777]

You said that D4204 have formulas in them, and the .End() metho
considers
formulas to be something, not empty cells even if there is no result
displayed. Usually such a formula has some test to return "
(zero-length
string) if something wasn't as expected. So I'm assuming your formula
do
that in some fashion, like
=IF(E4>0,E4,"") in D4 or
=IF(ISNA(VLOOKUP(E4,F1:G99,2,FALSE)),"",VLOOKUP(E4,F1:G99,2,FALSE))
or some similar formula with "" involved in it.

If that's the case then this line of code should select what you want.

Range("D4:" & Range("D4").Offset( _
Application.WorksheetFunction.Match("", _
Range("D4204"), 0) - 2, 0).Address).Select




:
-

JLatham:

Yes you are correct. A poor explanation on my part.

Very cool, it works perfectly, thank you so much

 

[JLatham]

Glad I got it right. You're welcome.

JLatham:

Yes you are correct. A poor explanation on my part.

Very cool, it works perfectly, thank you so much!