Select column where header matches X and define name-CodePudding

I'm trying to set up a bit of code to select the column in my workbook where the Column Header matches "Start Date" and declare this as a named range for use in formulae.

I have tried several ways - I can currently search header row for matching cell using Selection.Find, and then ActiveCell.EntireColumn.Select, but I am struggling to convert this to naming it as a range since the code from the macro recorder refers to a column letter.

Can/should I set a variable comprised of two distinct actions (Find then Select)? Or is there a simpler and cleaner way to do this?

CodePudding user response：

Solved it! (Sorry for formatting, on mobile)

Rows("1:1").Find(What:=" Start Date", After:=ActiveCell, LookIn:= _
       xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
       xlNext, MatchCase:=False, SearchFormat:=False).Select
    ActiveCell.EntireColumn.Select
    ActiveWorkbook.Names.Add Name:="Start_Date", RefersTo:=Selection
 ActiveWorkbook.Names("Start_Date").Comment = ""

I'm still new to VBA so working on not having Select, but this is a start. Any improvements welcome.

CodePudding user response：

Avoiding Select:

Dim f As Range, ws As Worksheet, nm As Name

Set ws = Activesheet

Set f = ws.Rows(1).Find(What:=" Start Date", _
         LookIn:=xlFormulas2, LookAt:=xlPart, _
         MatchCase:=False, SearchFormat:=False)
'make sure you got a match
If Not f Is Nothing Then
    Set nm = ws.Parent.Names.Add(Name:="Start_Date", RefersTo:=f.EntireColumn)
    nm.Comment = ""
else
    Msgbox "Header not found!"
end if

edit:

Dim c as range, rng as range

for each c in activesheet.usedrange.rows(1).cells
    if c.value like "*CHECK*" then
        If rng is nothing then
            Set rng = c
        else
            set rng = application.union(rng, c)
        end if
    end if
next c

if not rng is nothing then
    'create a Name using `rng`
end if