Using VBA to select a range within a range.
 

I’m trying to write code to select a range based on the visible data in the range (D4:D203) and ignore the blank cells. In other words, I have formulas that populate the range to produce the data, so if I have ten items of data, the first ten rows (D4 to D13) will show that data and the remaining rows will be blank. I just want to select the range of visible data and not the blank cells.
I’ve tried different variations based on the code provided but the code selects the entire range. Can any one help with this?

Using VBA to select a range within a range.
 

You said that D4:D204 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(E40,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("D4:D204"), 0) - 2, 0).Address).Select




"GoBow777" wrote:


Im trying to write code to select a range based on the visible data in
the range (D4:D203) and ignore the blank cells. In other words, I have
formulas that populate the range to produce the data, so if I have ten
items of data, the first ten rows (D4 to D13) will show that data and
the remaining rows will be blank. I just want to select the range of
visible data and not the blank cells.

Code:
--------------------
Range(Cells(4, 4), Cells(4, 4).End(xlDown)).Select
--------------------

Ive tried different variations based on the code provided but the code
selects the entire range. Can any one help with this?




--
GoBow777

JLatham:

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

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

Using VBA to select a range within a range.
 

Glad I got it right. You're welcome.

"GoBow777" wrote:


JLatham;878699 Wrote:
You said that D4:D204 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(E40,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("D4:D204"), 0) - 2, 0).Address).Select




"GoBow777" wrote:
-

I€„¢m trying to write code to select a range based on the visible
data in
the range (D4:D203) and ignore the blank cells. In other words, I
have
formulas that populate the range to produce the data, so if I have
ten
items of data, the first ten rows (D4 to D13) will show that data
and
the remaining rows will be blank. I just want to select the range of
visible data and not the blank cells.

Code:
--------------------
Range(Cells(4, 4), Cells(4, 4).End(xlDown)).Select
--------------------

I€„¢ve tried different variations based on the code provided but the
code
selects the entire range. Can any one help with this?




--
GoBow777
-

JLatham:

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

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




--
GoBow777