Selecting Rows by Column Value

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

My column "D" has only two values in it, (VALUE1 and VALUE2). My sheet is
sorted by this column. What I want need my macro to do is only select the
rows that have VALUE1 in column "D". Each time I run this the file size
would be different, so the first time my selection might be rows 1 through
1855. The next time it might be rows 1200 through 3500. The selection will
always be grouped together because of the sort.


T.I.A.
Ed
 
set rng = Columns(4).Find("VALUE2")
if not rng is nothing then
range(cells(1,1), rng.offset(-1,0)).EntireRow.Select

Look in help in VBA for the find method to see other arguments that might
affect your results.
 
If you only have two values and they are grouped together/sorted, how could
the area be 1200 to 3500

in any event, if that is the case, the you could use Find to find the first
Value1 as well.
 
Huh??? With 1199 VALUE2's and 2301 VALUE1's in D1:D3500, sorted in
descending order. Did I miss something in your question?

Alan Beban
 
Why would you sort descending? I am sure you can fantasize any number of
colorful scenarios. Your spell binding contributions continue to scintillate
the assembled masses. <g>
 
*I* didn't sort descending; it just seems to me that it's the obvious
way the OP would have VALUE1 in 1200 to 3500 like he said. As far as
spell binding [sic] contributions go, *you're* the one who contributed
the gratuitous (and puzzling) notion that you couldn't see how the OP
could have the situation he said he has.

Alan Beban
 
Huh!!??

--
Regards,
Tom Ogilvy

Alan Beban said:
*I* didn't sort descending; it just seems to me that it's the obvious
way the OP would have VALUE1 in 1200 to 3500 like he said. As far as
spell binding [sic] contributions go, *you're* the one who contributed
the gratuitous (and puzzling) notion that you couldn't see how the OP
could have the situation he said he has.

Alan Beban
Click to expand...
 
What I meant by the question was: more immediately, what is the next
command after selecting the relevant rows? The point of the question was
to raise the issue of whether you relly wanted to select the appropriate
rows, or rather just do something with them.

But be that as it may, something like the following will identify the
starting and ending rows for each value:

Sub test1000()
Dim rng as Range
Dim startRow1 As Long, endRow1 As Long
Dim startRow2 As Long, endRow2 As Long
Set rng = Sheets(4).Range("D:D")
startRow1 = rng.Find("VALUE1", rng(65536)).Row
endRow1 = rng.Find("VALUE1", rng(65536), , , , xlPrevious).Row
startRow2 = rng.Find("VALUE2", rng(65536)).Row
endRow2 = rng.Find("VALUE2", rng(65536), , , , xlPrevious).Row
Debug.Print startRow1; endRow1; startRow2; endRow2
End Sub

Alan Beban
 
Back
Top