Avatar of Flora Edwards
Flora Edwards
Flag for Sweden

asked on 

VBA modification help needed from earlier solution by Rgonzo1971 Dynamic Range

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


Rgonzo1971 was so kind helping with this great piece of code.

Sub Create_RangeNames1()
     '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 & "'!" & Cells(2, c).Address & ",0,0,SUMPRODUCT(MAX(('" & sht.Name & "'!" & strCol & "<>"""")*ROW('" & sht.Name & "'!" & strCol & ")))-1,1)"
       End If
    Next
End Sub

Open in new window


lets say from the attached file .  i selected column E and run the above code.  it created named range with this formua.
=OFFSET(Sheet1!$E$2,0,0,SUMPRODUCT(MAX((Sheet1!$E:$E<>"")*ROW(Sheet1!$E:$E)))-1,1)

i need help with modifying this, so that it creates the column dynamic formula  like this.  

so the "ORDER ID" inside Match function should take from column header name

=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))-1,1)
EE.xlsb
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Flora Edwards

8/22/2022 - Mon