John
John

Reputation: 822

Select a column based on various possible header names

What's the most efficient way of selecting a column based on a variety of different possible header names? For example, the following gives me the column with header "school":

Rows("1:1").Select
Selection.Find(What:="School", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
Range(ActiveCell, ActiveCell.Offset(6536, 0)).Select

However, "school" could be "college" in another workbook, or "institution" in another. Should I just place the above code within an if-then-else statement and replace "school" with the other possibilities, or is there a more efficient way? And yes, this assumes that none of the possible header names co-exist within the same workbook.

Upvotes: 0

Views: 111

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

Find is already very efficient. What's not efficient is all those Select's.

I suggest you wrap your Find Header logic into a Function, and refactor your code to avoid Select.

Private Function GetColumn(Header() As Variant, _
  Optional NumRows As Long = 0, _
  Optional ws As Worksheet = Nothing, _
  Optional wb As Workbook = Nothing) As Range

    Dim rng As Range, cl As Range
    Dim i As Long

    If wb Is Nothing Then
        Set wb = ActiveWorkbook
    End If
    If ws Is Nothing Then
        Set ws = wb.ActiveSheet
    End If

    Set rng = ws.UsedRange.Rows(1)
    For i = LBound(Header) To UBound(Header)
        Set cl = rng.Find(What:=Header(i), _
          After:=rng.Cells(1, 1), _
          LookIn:=xlValues, _
          LookAt:=xlWhole, _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, _
          MatchCase:=False)
        If Not cl Is Nothing Then
            With ws
                If NumRows = 0 Then
                    Set GetColumn = Range(cl, .Cells(.Rows.Count, cl.Column).End(xlUp))
                Else
                    Set GetColumn = Range(cl, .Cells(NumRows, cl.Column))
                End If
                Exit Function
            End With
        End If
    Next
    Set GetColumn = Nothing
End Function

Call it like this

Dim rng As Range
Dim Headers() As Variant
Headers = Array("School", "Institution", "College")


' Active Workbook, Active Sheet
Set rng = GetColumn(Headers, 6536)

' All rows in specified column
' Specified sheet in Active workbook
Set rng = GetColumn(Headers, , Worksheets("SomeSheetName"))

Upvotes: 1

Related Questions