Selecting range of data based on values in a particular column

January 29, 2009 11:02 AM

ExcelFilter: How can I select a range of data using VBA in Excel based on the values in a particular column?

I've got a report that exports its data to excel in 4 columns: Name, Crew Station, Hours in last 30 days and Hours in last 60 days. Crew Station, located in Column B, has two values: PILOT and AIRCREW. The macro sorts the data by Column B, with PILOT rows on top and AIRCREW values on bottom. I want to select the AIRCREW range of data (all four columns) only.

posted by squorch to Computers & Internet (3 answers total)

Best answer: Try this. It assumes that you have the worksheet in which the data have been populated is the active sheet, and that your data starts in row #2:

Public Sub getAircrew()

Dim nRow As Long
Dim nStart As Long, nEnd As Long

' Figure out where the "AIRCREW" data starts.
For nRow = 1 To 65536
If Range("B" & nRow).Value = "AIRCREW" Then
nStart = nRow
Exit For
End If
Next nRow

' Figure out where the "AIRCREW" data ends.
For nRow = nStart To 65536
If Range("B" & nRow).Value <> "AIRCREW" Then
nEnd = nRow
Exit For
End If
Next nRow
nEnd = nEnd - 1

Range("A" & nStart & ":D" & nEnd).Select

End Sub

posted by Doofus Magoo at 12:01 PM on January 29, 2009

Response by poster: That works great... I was also working on a brute-force version, but you beat me to the punch. Thanks!
posted by squorch at 12:14 PM on January 29, 2009

Just cos I was wondering... the following should be a bit quicker and doesn't require the sort.

Sub SelectionThing()
Dim FinalSelection As Range
Cells(1, 1).Select
For Each c In Intersect(ActiveSheet.UsedRange, Range("B:B"))
    If c.Value = "Aircrew" Then
        If FinalSelection Is Nothing Then
            Set FinalSelection = Range(Cells(c.Row, 2), Cells(c.Row, 6))
        Else
            Set FinalSelection = Union(FinalSelection, Range(Cells(c.Row, 2), Cells(c.Row, 6)))
        End If
    End If
Next c
If Not FinalSelection Is Nothing Then FinalSelection.Select
End Sub

posted by pompomtom at 7:19 PM on January 29, 2009