VBA modification if column is not adjacent then it does not work

I had this question after viewing VBA modification help needed from earlier solution by Rgonzo1971 Dynamic Rng.

I have this code below. this code creates named range for columns that are selected meaning on selection.

but the selection has one big problem.  if you select columns that are next to eachother then it works.
but if the you select non-adjacent columns then VBA only creates named range first one and ignores all other selected columns.

in this attached example.  i selected column B and column C and when i run the macro.  it should have 4 named ranges. two helper named ranges and then two named ranges one of column Amount and one for Month.
but it only creates for one of them not the second.

i need help with modifcation of the below . somehow using .area to be able to fix this. but i could not do it myself.
any help is appreciated

 Sub Create_RangeNames1()

    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim rng, rng2 As Range
    Dim cl As Range
    Dim c As Long
    Dim strAddr As Variant
    Dim strShName, strHdrName, strCol As String
    
    Set wbk = ActiveWorkbook
    Set sht = ActiveSheet
    Set rng = Selection
    
    strShName = cleanString(sht.Name)
    
    wbk.Names.Add Name:=strShName, RefersTo:="='" & sht.Name & "'!$1:$1048576"
    wbk.Names.Add Name:=strShName & "Lrow", RefersTo:="=LOOKUP(9.99999999999999E+307,1/(1-ISBLANK('" & sht.Name & "'!$A:$A)),ROW('" & sht.Name & "'!$A:$A))"
    
    For c = rng.Column To rng.Column + rng.Columns.Count - 1
        If Cells(1, c).Value <> "" Then
            'strShName = Replace(sht.Name, " ", "_", 1)
            strHdrName = cleanString(Cells(1, c).Value)
            strCol = Cells(2, c).EntireColumn.Address
            Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
             wbk.Names.Add Name:=strShName & strHdrName, _
                RefersTo:="=INDEX(" & strShName & ",2,MATCH(""" & Cells(1, c) & """,INDEX(" & strShName & ",1,0),0)):INDEX(" & strShName & "," & strShName & "Lrow,MATCH(""" & Cells(1, c) & """,INDEX(" & strShName & ",1,0),0))"
       End If
    Next
End Sub
Function cleanString(text As String) As String
    Dim output As String
    Dim c 'since char type does not exist in vba, we have to use variant type.
    For i = 1 To Len(text)
        c = Mid(text, i, 1) 'Select the character at the i position
        If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
            output = output & c 'add the character to your output.
        Else
            output = output & "_"
        End If
    Next
    cleanString = output
End Function

Open in new window

EE.xlsb
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
See if this creates the two main named ranges that you want. If it does then we can work on the Helper ranges.
29072342.xlsb
0
Martin LissOlder than dirtCommented:
In this version I simplified the code and changed the creation of rData so that it only includes the data rows. That makes it a lot smaller and, I assume, a lot faster.
29050123a.xlsm
0
FloraAuthor Commented:
Martin,

i do not think your answer 42394697 is anyhow related to my question.

also the macro 42394624 gets the column correctly, but it generates dynamic range with offset which is going to suck up the processor on calculation. i want to keep my existing solution with Index COUNTA and OFFSET have alot of drawback and problems.

if my original code cannot be modified to be able to capture the selected columns then it is fine. i wil just live with my original solution.
0
Martin LissOlder than dirtCommented:
i do not think your answer 42394697 is anyhow related to my question.
Sorry, I'm working on several things and got confused. I've attached the correct workbook.
it generates dynamic range with offset which is going to suck up the processor on calculation
I disagree. It's something that's only calculated once and that is when the Named Range is created.

Another thing. While named ranges "rData" and "Lrow" are created in this workbook, they're not used. And even if they are used in some other place you can probably use MySheetName.UsedRange instead of rData, and MySheetName.UsedRange.Rows.Count instead of Lrow.

I hope you don't mind the above comments, I'm just trying to help.
29072342a.xlsb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.