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
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.
            output = output & "_"
        End If
    cleanString = output
End Function

Open in new window

Who is Participating?
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.
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.
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.
FloraAuthor Commented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.