Named Ranges through VBA

[Hanz77]

I’m hoping someone can see where I’m messing up with my code. I’m trying to name several columns based on their heading name. The headings are in row three and the actual data starts in row 4 and goes down from there.

The problem I’m having is that the named ranges are changing based on the actively selected column. For example, if the column containing Countries is located in column H, my code will identify the column and properly name it “Country”. However, as soon as I select column K, the named references will then refer to column K, then if I select column M the named references will refer to column M.

Following is an example of my code to name two ranges, the actual code is attempting to name about 70 columns. The columns are not always in the same order, which is why I have a find function built in.

Code:
LastRow01 = [A65536].End(xlUp).Row
MyArea01 = "='" & ActiveSheet.Name & "'!R2:R" & LastRow01 & "C" & LastCol01
LastCol01 = Cells.Find(What:="Country", After:=[A3], LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Select
ActiveWorkbook.Names.Add Name:="Country", RefersToR1C1:=MyArea01

LastRow02 = [A65536].End(xlUp).Row
MyArea02 = "='" & ActiveSheet.Name & "'!R2:R" & LastRow02 & "C" & LastCol02
LastCol02 = Cells.Find(What:="Sales Person", After:=[A3], LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Select
ActiveWorkbook.Names.Add Name:="SalesPerson", RefersToR1C1:=MyArea02

Why is the column reference changing after this script has ran? Any thoughts on how to fix are appreciated.

 

[Norie]

Shouldn't you be setting/finding the value for LastColX before you use it when creating MyAreaX?

 

[shg]

How about a little different approach?

    Dim wks As Worksheet
    Dim r As Range
    
    Set wks = ActiveSheet
    With wks
        Set r = .Range(.Rows(3), .Rows(.Cells(.Rows.Count, "A").End(xlUp).Row))
        Application.DisplayAlerts = False
        r.CreateNames Top:=True
        Application.DisplayAlerts = True
    End With