Help with defining ranges

[rc090320]

Hi

I have a small data set where in column "A" I have key indicators defining what each row means.

What I wanted to know is how can I write a VBA code where it intuitively selects the row range of consecutive "G" values in column "A".

Thanks in advance

Ronnie

 

[Fazza]

Hi Ronnie. Here is one way. Please add your own error checking, etc. Regards, Fazza

Sub test()

    Dim rngFirstG As Range
    Dim rngLastG As Range
    
    With Columns(1)
        Set rngFirstG = .Find(What:="G", After:=.Cells(.Cells.Count), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

        Set rngLastG = .Find(What:="G", After:=.Cells(1), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    End With
        
    Range(rngFirstG, rngLastG).EntireRow.Select

End Sub