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
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))"
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 & "_"
cleanString = output