Forum Discussion

SWANNY1010's avatar
SWANNY1010
Copper Contributor
Sep 06, 2021

Adding cells to named range vba

I'm trying to create a named range from a search, I have managed to find all the cells, so when I've found them or either at time of finding or or from collection I want to add the cells to a named range
excel
Macros and VBA

1 Reply

  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Sep 07, 2021

    SWANNY1010 

    As you didn`t post any piece of code, please take a look and make your adjustments as needed.

    Sub UpdateNamedRange()
    Dim wb As Workbook
    Dim NamedRange As Name

    Set wb = ActiveWorkbook
    Set NamedRange = wb.Names.Item("myRange")

    ' give an absolute reference:
    NamedRange.RefersTo = "=Sheet1!$C$1:$C$9"

    ' or, resize relative to old reference:
    With NamedRange
        .RefersTo = .RefersToRange.Resize(100, 1)
    End With
End Sub

Resources