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 comments total)

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

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

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