VBA Help - Dynamically Update Named Range to Lastrow

[Johnny Thunder]

Hi Guys, working on a script to dynamically update a named range for my data to the last row when a report is ran and hitting a roadblock with my code.

I am getting an error on the code, most likely because the syntax is wrong so I was hoping someone may know how to do this.

I am using the Usedrange line to find the last active row in my workbook but its not working. My data currently goes to row 5000.

Any help is appreciated

Sub UpdateRanges()


Dim sht As Worksheet
Dim wb As Workbook
Dim nr As Name
Dim lastR As Long


lastR = sht.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
With ActiveWorkbook.Names("Data1")
        .Name = "Data1"
        .RefersTo = "=Query1!$A$14:$AC$" & lastR & """"
        
    End With


End Sub

 

[Dryver14]

If you are using Column A then try

LastR = Cells(Rows.count, 1).end(xlup).row (adjust the 1 to whichever column number you are checking)

 

[Dryver14]

Also,which sheet is sht? if it is the active sheet then say activesheet.cells(rows.count,1.end(xlup).row

 

[Johnny Thunder]

Worked like a charm!

New Code:

Sub UpdateRanges()


Dim sht As Worksheet
Dim wb As Workbook
Dim nr As Name
Dim lastR As Long


Set sht = Sheets("Query1")




lastR = sht.Cells(Rows.Count, 2).End(xlUp).Row
    
With ActiveWorkbook.Names("Data1")
        .Name = "Data1"
        .RefersTo = "=Query1!$A$14:$AC$" & lastR & ""
        
    End With


End Sub

Also,which sheet is sht? if it is the active sheet then say activesheet.cells(rows.count,1.end(xlup).row

 

[Dryver14]

Nice one