Selecting Rows by Column Value
 

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

Selecting Rows by Column Value
 

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.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Selecting Rows by Column Value
 

What is supposed to happen after the rows are selected?

Alan Beban

Ed wrote:
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

Selecting Rows by Column Value
 

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Selecting Rows by Column Value
 

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

Tom Ogilvy wrote:
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.

Selecting Rows by Column Value
 

The selceted rows will be made to into a .csv file.



"Alan Beban" wrote in message
...
What is supposed to happen after the rows are selected?

Alan Beban

Ed wrote:
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

Selecting Rows by Column Value
 

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

--
Regards,
Tom Ogilvy

Alan Beban wrote in message
...
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

Tom Ogilvy wrote:
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.

Selecting Rows by Column Value
 

*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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

Alan Beban wrote in message
...

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

Tom Ogilvy wrote:

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.

Selecting Rows by Column Value
 

Huh!!??

--
Regards,
Tom Ogilvy

Alan Beban wrote in message
...
*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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy

Alan Beban wrote in message
...

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

Tom Ogilvy wrote:

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.

Selecting Rows by Column Value
 

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

Ed wrote:
The selceted rows will be made to into a .csv file.



"Alan Beban" wrote in message
...

What is supposed to happen after the rows are selected?

Alan Beban

Ed wrote:

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