how would i select a group of rows based on a column value

[Brian Walker]

This question has to be one of the most frequently asked question and
I am sorry I have to ask it agin.. but I have been unable to find an
acceptable solution.

What I am currently doing is sorting a sheet by a particualr column.
Then manually selecting all the rows from top to bottom that have the
same value in a particular column... After that I trigger my macro and
it does it's thing. (cuts and pastes in a new sheet)

What I'd like for my macro to do.... Select the rows by it's self.

What I'd like the macro to do is..

if D2 = D1 add row 2 to the selection
if D3 = D1 add row 3 to the selection
if D4 = D1 add row 4 to the selection
untill D? does not = D1 then break the loop and continue on with the
rest of the macro...

here is what I am doing now..

Sub weeklyreport()

' some one please help me put a line of code or a sub that will select
my data
Selection.Cut
Sheets.Add

Call paste ' a non relevant sub routine
Call pageset ' a non relevant sub routine
Call adddata ' a non relevant sub routine
Call mailme ' a non relevant sub routine
Call shift ' a non relevant sub routine

End Sub



Any ideas?

 

[Earl Kiosterud]

Brian,

Try this:

Dim TestRow As Long, SelectRow As Long
TestRow = 2 ' starting row
SelectRow = 1 ' bottom row extent to select
Do While Cells(TestRow, 4) <> ""
If Cells(TestRow, 4) = Range("D1") Then
SelectRow = TestRow
TestRow = TestRow + 1

Else
Exit Do
End If
Loop
Range(Cells(1, 1), Cells(SelectRow, 1)).EntireRow.Select

' Optional: Is next cell out of sequence?
If Cells(SelectRow, 4) > Cells(SelectRow + 1, 4) _
And Cells(SelectRow + 1, 4) <> "" Then
MsgBox "Column D has a sequence error", , "Aaaahhh!"
End If