change named range reference with vba

nicnad

Board Regular
Joined
Sep 12, 2011
Messages
199
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" :

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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Maybe this

Code:
Private Sub worksheet_activate()
    Dim lr As Long
 
    lr = Sheets("master").[COLOR=blue]Columns(1)[/COLOR]. _
    Find(What:="*", After:=[A1], SearchOrder:=xlByRows, searchDirection:=xlPrevious).Row   
   With ActiveWorkbook.Names("no_m")
        .RefersTo [COLOR=blue]=[/COLOR] "=master![COLOR=blue]$A$1:$A$[/COLOR]" & lr
   End With
End Sub

M.
 
Upvote 0
Works Great thank you very much.

I am using this kind of code multiple times in my workbook. :

Code:
lr = Sheets("master").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, searchDirection:=xlPrevious).Row
Range("a1:a" & lr).select

Why when I use it for select, resize or loop task it works great and it does not work in the case above?
 
Upvote 0
I think I found it.

The problem was with that line :

Rich (BB code):
.RefersTo.Worksheets("Master").Range ("a1:a" & lr)

That should be this :

Rich (BB code):
.RefersTo = "=master!$A$1:$A$" & lr

I think my lr variable was ok.

Thank you for the help anyway!
 
Upvote 0
The problem in your code above was not in the setting of lr.
It was where i indicated with blue

M.
 
Upvote 0
I think I found it.

The problem was with that line :

Rich (BB code):
.RefersTo.Worksheets("Master").Range ("a1:a" & lr)

That should be this :

Rich (BB code):
.RefersTo = "=master!$A$1:$A$" & lr

I think my lr variable was ok.

Thank you for the help anyway!

And also in

lr = Sheets("master").Columns(1). _
 
Upvote 0
Thank you Marcelo for the reply.

I have another problem :

Is there a way to bring a autofilter custom dialog box that refers to another sheet via VBA?

In my example, the code would be fired from sheets("Update") and would refer to sheets("master") data.

Here is my code so far (not working) :

Code:
Application.ScreenUpdating = False
worksheets("master").activate
Worksheets("master").Range("A1:A14").Select
Application.Dialogs(xlDialogFilter).Show
worksheets("Update").activate
Application.ScreenUpdating = True

Thank you for your help!
 
Upvote 0
Maybe something like

In Sheet Update code page
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Call aTest
    End If
End Sub

In a standard module
Code:
Sub aTest()
    Application.ScreenUpdating = False
    With Worksheets("master")
        .Activate
        .Range("A1:A14").Select
        Application.Dialogs(xlDialogFilterAdvanced).Show
    End With
   Application.ScreenUpdating = True
End Sub

When the value in Sheet Update A1 changes the sub aTest() is called

M.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top