MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Named Ranges Using Auto Filter


Posted by Kurt Nichols on July 17, 2000 7:02 PM

Hello, I need to be able to name a range after using Auto Filter and then save it. The ranges that are selected can vary in size, but it is based on only one Criteria.

HELP!!! If anybody can help I would greatly appreicate it.

Thanks

Kurt


Posted by Kurt on July 18, 0100 5:01 AM

Thanks Ryan,

I have several columns in a large spreadsheet. I want to be able to auto filter on one column and then select the cells based on that filter and then save a named range. All automatically. I know this has to do with dynamic ranges, and this is my first experience with it.

Thanks again.


Kurt

If you need more follow up my email address is kdnichols@hotmail.com

Posted by Kurt on July 19, 0100 7:38 AM

Hello Ryan,

One Last Question. It works except for one minor detail.

Where do I tell AutoFilter to turn off at?

It still picks up the entire worksheet. It saves the range
but it still saves the entire worksheet.

Thanks again,

Kurt

Posted by Ryan on July 19, 0100 8:39 AM

Kurt,

I'll have to see the rest of your code to know what's going on. If you want to post it I'll take a look!

Ryan

Posted by Kurt on July 18, 0100 8:59 AM

Here's the code I have so far:

Sub Custom1()
Const strTaskname = "Name a Range"
Dim RngName As String

' Start AutoFilter and look for CD Number 2

Range("A1").AutoFilter Field:=6, Criteria1:="2"

RngName = InputBox(prompt:="Enter the name for the " & _
"new Range:", _
Title:=strTaskname)
'RngName = myVar
ActiveWorkbook.Names.Add Name:=" ", RefersToR1C1:="=Sheet2!R1C1:R5704C9"

End Sub

How do I pass the named range to the Add Name?

This is the only part that I lack.

Kurt

Posted by Ryan on July 18, 0100 11:21 AM

I think this is what you are asking for, just how to pass the name inputed into the inputbox onto the Add Name Function. Do you also need a dynamic range? Just wondering b/c you have the range inputed in your example. Let me know.

Ryan
ActiveWorkbook.Names.Add Name:=RngName, RefersToR1C1:="=Sheet2!R1C1:R5704C9"

Posted by Kurt on July 19, 0100 11:55 AM

Ryan,

Here it is again. Thanks for your help!!

Sub Select_Store_Number()
Const strTaskname = "Name a Range"
'Dim RngName As String


' Start AutoFilter and look for CD Number 2

Range("A1").AutoFilter Field:=1, Criteria1:="4"
ActiveCell.CurrentRegion.Select
ThisWorkbook.Names.Add Name:="NewName", _
RefersTo:="CurrentRegion", Visible:=True

RngName = InputBox(prompt:="Enter the name for the " & _
"new Range:", _
Title:=strTaskname)
'RngName = myVar
ActiveWorkbook.Names.Add Name:=RngName, RefersToR1C1:="=Sheet2!R1C1:R5704C9"

End Sub

Posted by Kurt on July 18, 0100 1:05 PM

Thanks ever so much Ryan. Yep that was it!!!

Have a great day and G_d bless you!!!

Thanks, Thanks, Thanks, Thanks.....


Kurt

Posted by Ryan on July 17, 0100 7:17 PM

Kurt,

Give us some more info.....

Ryan