Named Ranges Using Auto Filter

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

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

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

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

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"

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

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

Have a great day and G_d bless you!!!

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

Kurt

Kurt,

Give us some more info.....

Ryan