Forum Discussion

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 ra...

excel

Macros and VBA

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