We help IT Professionals succeed at work.
Get Started

VBA modification help needed to revise the formula

Last Modified: 2017-11-13
I have this best code.  also in the attached workbook.

if you select the column A and then run this macro. it will add a dynamic named range  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

i need help with modification of VBA code that instead of creating =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)  I need the VBA to Create this =OFFSET(Sheet1!$A$2,0,0,SUMPRODUCT(MAX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A)))-1,1)  

how can this code below be modified?

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 = 1 To rng.Columns.Count
        If Cells(1, c).Value <> "" Then
            strShName = Replace(sht.Name, " ", "_", 1)
            strHdrName = Replace(Cells(1, c).Value, " ", "_", 1)
            strAddr = Split(Cells(1, c).Address, "$")
            strCol = "$" & strAddr(1) & ":$" & strAddr(1)
            Set rng2 = sht.Range(Cells(, c), Cells(1, c).End(xlDown))
             wbk.Names.Add Name:=strShName & strHdrName, _
                RefersTo:="=OFFSET('" & sht.Name & "'!" & Cells(1, c).Address & ",0,0,COUNTA('" & _
                sht.Name & "'!" & strCol & "),1)"
       End If
End Sub

Open in new window

Watch Question
Top Expert 2016
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE