Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.
Code:
Range(Cells(4, 4), Cells(4, 4).End(xlDown)).Select |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
Quote:
Yes you are correct. A poor explanation on my part. Very cool, it works perfectly, thank you so much! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select a range? | Excel Worksheet Functions | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
I can't select a range in VBA | Excel Discussion (Misc queries) | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Select Using Range | Excel Discussion (Misc queries) |