How to set named range in macro?

[davegb]

I have a macro that is called each time a different sheet in the
workbook is selected. The macro is supposed to define the determined
range as a named ramge, "FilterRange".

Sub CtyShtFltRng()
Dim lCol as Long
Dim lRow As Long
Dim wActSheet As Worksheet
Set wActSheet = ActiveSheet


Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
Set wActSheet.Names("FilterRange") = rCtyShtFltRng <---ERROR
wActSheet.Range("FilterRange").Select

End Sub


I'm getting an "wrong number of arguments or invlalid property
assignment" at the marked line. I've tried about 10 or 12 variations on
this line, but none worked. Can someone tell me how to make this work?
Thanks!

 

[Tom Ogilvy]

Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

 

[Don Guillett]

see if this works?

Sub namerange()
Range("b3").CurrentRegion.Name = "rCtyShtFltRng"
End Sub

 

[RB Smissaert]

Sub CtyShtFltRng()

Dim lCol As Long
Dim lRow As Long
Dim wActSheet As Worksheet
Dim rStart As Range
Dim rCtyShtFltRng As Range

Set wActSheet = ActiveSheet
Set rStart = Range("B3")

lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column

Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))

rCtyShtFltRng.Name = "FilterRange"
wActSheet.Range("FilterRange").Select

End Sub


RBS

 

[davegb]

Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

Thanks, Tom, worked like a charm, as always.

 

[Steve]

I think the problem was that you were setting the worksheet object to be a
named range

Try this

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rCtyShtFltRng As Range

Set rStart = Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = Range(rStart, Cells(lRow, lCol))
ActiveWorkbook.Names.Add Name:="FilterRange",
RefersToR1C1:=rCtyShtFltRng
Range("FilterRange").Select
End Sub

 

[davegb]

see if this works?

Sub namerange()
Range("b3").CurrentRegion.Name = "rCtyShtFltRng"
End Sub

Thanks, Don, but I chose the column and row to determine the range
because I have blanks and merged cells in the sheet.

 

[Jim Thomlinson]

Your syntax needs a little tweaking. Also since you declare the worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub

 

[davegb]

Your syntax needs a little tweaking. Also since you declare the worksheet
object you should be sure to reference it in all of you Range and Cells
references. It does not make a difference in this case since it is the active
sheet but it is a good habit to get into... I also declared your range
objects assuming that they were not declared publicly somewhere else. If they
are just remove the declarations.

Sub CtyShtFltRng()
Dim lCol As Long
Dim lRow As Long
Dim rStart As Range
Dim rCtyShtFltRng As Range
Dim wActSheet As Worksheet

Set wActSheet = ActiveSheet
With wActSheet
Set rStart = .Range("B3")
lRow = rStart.End(xlDown).Row - 1
lCol = rStart.End(xlToRight).Column
Set rCtyShtFltRng = .Range(rStart, .Cells(lRow, lCol))
.Names.Add Name:="FilterRange", RefersTo:=rCtyShtFltRng
.Range("FilterRange").Select
End With
End Sub

Thanks for your reply, Jim. This is very interesting and making me
curious. The code Tom sent me worked fine, on 6 of the 9 sheets that
call this routine when they are activated. But on 3 of the sheets, it
was hanging up, with a "Wrong number of arguments or invalid property
assignment" error on the line

rCtyShtFltRng.Name = wActSheet.Name & "!FilterRange"

I was struggling with figuring out why when you gave your reply. I
tried your code and it works on all 9 sheets. It must have something to
do with "referencing it in all of your range and cell references". But
why would it work on some sheets but not on others?
I think you're referring to the periods you placed in that line
referencing back to the "with wActSheet" statement. Somehow, some of
the sheets figured out which sheet I was referring to, and the others
couldn't. Very strange. Do you, or anyone else, have any ideas on this?
As to your reference about declaring the variables, some of them are
declared publicly, as you surmised.

 

[Tom Ogilvy]

I suspect the names of the sheets that fail have spaces in the sheet name.
So adjust to

rCtyShtFltRng.Name = "'" & wActSheet.Name & "'!FilterRange"

so that the sheet name will be enclosed in single quotes

'my sheet'!FilterRange

It will still work on the already working sheets.

Jim avoids the problem by using Names.Add with an object referencing the
sheet as a qualifier, so it is implicitely added as a sheet level name.

 

[davegb]

I suspect the names of the sheets that fail have spaces in the sheet name.
Exactly!

So adjust to

rCtyShtFltRng.Name = "'" & wActSheet.Name & "'!FilterRange"

so that the sheet name will be enclosed in single quotes

'my sheet'!FilterRange

It will still work on the already working sheets.

Jim avoids the problem by using Names.Add with an object referencing the
sheet as a qualifier, so it is implicitely added as a sheet level name.

Thanks again. Can you elaborate on this? What does "object referencing
the sheet as a qualifier" mean? What is a "sheet level" name?

 

[Tom Ogilvy]

Bob Phillips has a discussion of name on his site:

http://www.xldynamic.com/source/xld.Names.html

Dim sh as Worksheet
set sh = Activesheet
msgbox sh.name

sh is a reference to a worksheet.

 

[davegb]

Bob Phillips has a discussion of name on his site:

http://www.xldynamic.com/source/xld.Names.html

Dim sh as Worksheet
set sh = Activesheet
msgbox sh.name

sh is a reference to a worksheet.

Thanks again, Tom!