How to select column by header

**jsp_1951** · 10-31-2011, 12:59 PM

Is there a simple way to select a column based on a text string in the header row?

Thanks,
Jim

**Paul** · 10-31-2011, 02:31 PM

Hi Jim,

What do you mean "select"? A specific text string can be searched for and found using formulas or VBA, but what are you trying to do?

Thanks!

**arlu1201** · 10-31-2011, 02:44 PM

Do you mean locate the text in the header and then select the column where you find the text?

**jsp_1951** · 11-01-2011, 05:03 AM

Sorry for not being clear. I want to find then select the entire colum that has a particular name in row 1. The column may not be in the same place (A to ZZ) in different workbooks, but it will always have the same header name.

I want to select specific named columns to perform operations. It may be to copy to another worksheet or to delete based on the header name.

**arlu1201** · 11-01-2011, 07:14 AM

Use this code considering that your header is in row 1.

Dim cellcol As Long
    
    Rows("1:1").Select
    Cells.Find(What:="Name of column", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
    cellcol = ActiveCell.Column

The output will come as a number.

**Paul** · 11-01-2011, 12:13 PM

This macro will find the cell header you input and select the entire column. If not found, it will tell you so.

Sub findcol()
Dim ans As String
ans = InputBox("Find column header:", "Column Header Search", "")
If ans <> "" Then
    On Error GoTo notfound
    Range("1:1").Find(What:=ans, After:=Range("A1"), LookIn:=xlValues, Lookat:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
        SearchFormat:=False).EntireColumn.Select
    On Error GoTo 0
End If
Exit Sub

notfound:
MsgBox "That entry was not found in the header row.  Please try again."
End Sub

Hope that helps!