I had this question after viewing VBA modification help needed from earlier solution by Rgonzo1971 Dynamic Range
Rgonzo1971 helped me with the code below.
it created dynamic ranges where column and rows are both dynamic.
i used the named ranges created by this macro inside the formulas and it really killed my excel processors and went i did some research, it was becuase of use of offset.
Now, i have found a better way of creating dynamic range, i have uploaded a copy of workbook with my ideal dynamic range.
however, i could not modify the code below, so that it creates the dynamic range as per the attached workbook using INDEX and not offset.
so here are the steps.
lets say, i selected the four columns from A to D from the attached workbook.
1- the macro first should create a named range for entire sheet. named name of sheet sht.name which is "Data" refers to =Data!$1:$1048576
so in VBA code below it would be something like =sht.Name & "!$1:$1048576
2- second it should create a lastrow named range named as sheet name & Lrow so the named range name will be for this example DataLrow and it will refer to =LOOKUP(9.99999999999999E+
so in the VBA code it would be something like =LOOKUP(9.99999999999999E+
.Name & "!$A:$A)),ROW(sht.Name & "!$A:$A))
these two abovementioned named ranges are as helper named ranges to be used inside the actual named ranges
3- the rest will be the loop to create the actual named range for each selected column, the named range name will be strShName & strHdrName and they should refer to =INDEX(Data
,2,MATCH(""" & Cells(1, c) & """,INDEX(Data
,MATCH(""" & Cells(1, c) & """,INDEX(Data
please note that Data
inside INDEX and DataLrow
inside Index are the named ranges created in 1 & 2 steps outside the loop.
i tried by myself , but i could not modify the code. any help is appreciated.
'Creates dynamic named ranges based on header row information only Selection
Dim wbk As Workbook
Dim sht As Worksheet
Dim rng, rng2 As Range
Dim cl As Range 'Object
Dim c As Long
Dim strAddr As Variant
Dim strShName, strHdrName, strCol As String
Set wbk = ActiveWorkbook
Set sht = ActiveSheet
Set rng = Selection
For c = rng.Column To rng.Column + rng.Columns.Count - 1
If Cells(1, c).Value <> "" Then
strShName = Replace(sht.Name, " ", "_", 1)
strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
strCol = Cells(2, c).EntireColumn.Address
Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
wbk.Names.Add Name:=strShName & strHdrName, _
RefersTo:="=OFFSET(" & sht.Name & "!$A$2,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1,SUMPRODUCT(MAX((OFFSET(" & sht.Name & "!$A:$A,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1)<>"""")*ROW(OFFSET(" & sht.Name & "!$A:$A,0,MATCH(""" & Cells(1, c) & """," & sht.Name & "!$1:$1,0)-1))))-1,1)"