Code error

[hallingh]

I'm trying to set a current named range in my sheet equal to a dimmed range in my macro code. I'm getting a Subscript out of range error, so I know my coding is off. This is what I have:

Public Sub FilterLists_Click()
Dim oRange1 As Range
Dim RangeNum As Long

RangeNum = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

oRange1 = Sheets("Sheets1").Range("A1:A" & RangeNum)
Range("ListName") = oRange1

End Sub

Any help with this would be greatly appreciated. Thanks!

Hank

 

[njimack]

Something like this...

Public Sub FilterLists_Click()
Dim RangeNum As Long

RangeNum = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

ActiveWorkbook.Names.Add Name:="ListName", RefersTo:=Sheets("Sheet1").Range("A1:A" & RangeNum)

End Sub

 

[hallingh]

The reason I wasn't adding a new named range is because I already have a named range called "ListName." Is is not possible to just change the range of ListName?

Thanks a lot for the help.

Hank

 

[njimack]

The reason I wasn't adding a new named range is because I already have a named range called "ListName." Is is not possible to just change the range of ListName?

Thanks a lot for the help.

Hank

The code I posted will overwrite the existing range which in effect is what you're asking for.

 

[hallingh]

Aha, I understand now. Thanks a ton!

Hank