Select Range Plus blank row above

[hindsight]

I'm trying to select a range from a larger range of data based on a cell value. I'm sure there's an easier way to do this using a Do Loop or something similiar but I'm not too familiar with that concept.



Here's my worksheet:

A B C D E F
1 Mike Blue 1
2 John Blue 2
3 Steve Green 1
4 Bill Green 1
5 Bob Red 2
6 Bob Red 3
7 Jan Red 4
8 Jan Red 5

I want to find the first instance of "red," insert a row between "red" and "green", and then select all rows with "RED" INCLUDING the newly inserted row, as a range.

I've got the following (mainly recorded) code. Bear with me as it is not very elegant.

Range("b1").Select
Cells.Find(What:="RED").Activate
Selection.EntireRow.Insert
Range("b1").Select
Cells.Find(What:="RED").Activate
ActiveCell.Offset(-1, -1).Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select


My problem is that this code will select the blank line and the first row of data but will NOT select to the end of the data. I even changed it to cell "c2" and that didn't work.

Any thoughts?

JB

[BrianB]

Something like this :-

Sub test()
    Dim StartRow As Long
    Dim LastRow As Long
    '-------------------------------
    Cells.Find(What:="RED").Activate
    Selection.EntireRow.Insert
    StartRow = ActiveCell.Row
    LastRow = StartRow + 1
    While UCase(ActiveSheet.Cells(LastRow, 3).Value) = "RED"
        LastRow = LastRow + 1
    Wend
    Range(Cells(StartRow, 3), Cells(LastRow - 1, 3)).Select
End Sub