Hi,
I'm struggling to change a named range reference with vba. On worksheet_activate, I want the range to be changed to include the last row.
Here is my code so far. The range already exist in the workbook and it is named "no_m" :
What am I doing wrong?
Thank you for your help.
I'm struggling to change a named range reference with vba. On worksheet_activate, I want the range to be changed to include the last row.
Here is my code so far. The range already exist in the workbook and it is named "no_m" :
Code:
Private Sub worksheet_activate()
lr = Sheets("master").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, searchDirection:=xlPrevious).Row
With ActiveWorkbook.Names("no_m")
.RefersTo.Worksheets("Master").Range ("a1:a" & lr)
End With
End Sub
What am I doing wrong?
Thank you for your help.