Results 1 to 9 of 9

change named range reference with vba

This is a discussion on change named range reference with vba within the Excel Questions forums, part of the Question Forums category; Hi, I'm struggling to change a named range reference with vba . On worksheet_activate, I want the range to be ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default change named range reference with vba

    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.

  2. #2
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,642

    Default Re: change named range reference with vba

    Maybe this

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

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: change named range reference with vba

    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?

  4. #4
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: change named range reference with vba

    I think I found it.

    The problem was with that line :

    Code:
    .RefersTo.Worksheets("Master").Range ("a1:a" & lr)
    That should be this :

    Code:
    .RefersTo = "=master!$A$1:$A$" & lr
    I think my lr variable was ok.

    Thank you for the help anyway!

  5. #5
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,642

    Default Re: change named range reference with vba

    The problem in your code above was not in the setting of lr.
    It was where i indicated with blue

    M.

  6. #6
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,642

    Default Re: change named range reference with vba

    Quote Originally Posted by nicnad View Post
    I think I found it.

    The problem was with that line :

    Code:
    .RefersTo.Worksheets("Master").Range ("a1:a" & lr)
    That should be this :

    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). _

  7. #7
    Board Regular
    Join Date
    Sep 2011
    Posts
    199

    Default Re: change named range reference with vba

    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!

  8. #8
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,642

    Default Re: change named range reference with vba

    I can not see it now, i have to leave. Later i'll take a look.

    M.

  9. #9
    MrExcel MVP Marcelo Branco's Avatar
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    8,642

    Default Re: change named range reference with vba

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com