Name a range that's been filtered

[Trikageon]

I've been looking everywhere for this answer with no luck, so props to whomever can tackle this!

What I need is the code for naming a range that's been filtered. For example, in my macro I'm telling it to filter like so:

Selection.AutoFilter Field:=2, Criteria1:="320"

Now, what I need to do is name that data to "ThreeTwenty." When recording this macro, visually, it comes out like this:

ActiveWorkbook.Names.Add Name:="ThreeTwenty", RefersToR1C1:= _
"=Data!R85C4:R144C10"

The problem I'm running into here is that the "RefersToR1C1" coordinates will likely change each month, so is there a way for it to determine its own dimensions for that filtered range and/or is there some other cool trick to accomplish the same thing that I'm not currently aware of? Thanks in advance!

 

[ukmikeb]

Hi

Welcome to the forum.

In naming the range are you guaranteed that all the filtered qualifiers are in consecutive rows?

The selection of the Named range will include non-qualifiers unless in VBA you can limit the Named Range to visible cells only.

Good luck.

Mike

 

[Trikageon]

Yes, all of the rows are in consecutive order. I kinda figured it had something to do with a visable option. Is there any way I could trouble you for an example of code that you think might work? One more criteria I forgot to mention is that I only need data from (and including) Column D going to the right. Thanks!

 

[ukmikeb]

Hi

The best way to determinwe the extent of your range is to do a match for "320" twice, once for the start of the range and once for the end of the range, like :-

lngFirstRow = Application.WorksheetFunction.Match("320", your selected Range, 0)
lngLastRow = Application.WorksheetFunction.Match("320", your selected Range, 1)

Then use those results in the formula defining your named range.

This will only work if the "320" value is contiguous in the filtered result - beware!

hth

Mike

 

[Trikageon]

I like your thinking here, the only problem is I'm having trouble determining what I should put for my range in each of those lines you described. If it helps, your code, in this particular example, would start looking for the "320" at B4 and go down until there was no data left to analyze in that column. This would essentially find the rows I need, but the range I would actually want to name would start 2 cells over, in column D, and would go until the yet to be determined row of column J. I imagine the code I need would have something to do with offsetting, but I'm still kinda rookie, so who knows! (and yes, the data would be contiguous) Thanks in advance!

 

[Richard Schollar]

Hi

You could try doing it like this:

Selection.Autofilter Field:=2, Criteria1:="320"

With Activesheet.Autofilter.Range
  .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Name = "WhateverNameYouWant"
End With

 

[ukmikeb]

Hi

Your data range would be defined as :-

Dim Mydatarange as Range
Mydatarange = Range("B4:B" & [B65536].End(3).Row)

Then the match statements become -

lngFirstRow = Application.WorksheetFunction.Match("320", Mydatarange, 0)
lngLastRow = Application.WorksheetFunction.Match("320", Mydatarange, 1)

and the ThreeTwenty definition statement becomes -

ActiveWorkbook.Names.Add Name:="ThreeTwenty", RefersToR1C1:= _
"=Data!R" & lngFirstRow & "C4:R" & lngLastRow & "C10"

hth

Mike

ps
It would probably be better to adapt Richard's solution to your needs because I believe that it will remove any conflict with the ThreeTwenty range definition in subsequent execution of the macro.

 

[Trikageon]

@Richard Schollar: Your code worked perfectly, except it's including columns B & C in the definition and I only need from column D to J. Really close, though!!

@ukmikeb: I'm getting an error that says "Mydatarange = Nothing"

Thanks guys!

 

[Richard Schollar]

Try amending to this:

Selection.Autofilter Field:=2, Criteria1:="320"

With Activesheet.Autofilter.Range
  .Offset(1,2).Resize(.Rows.Count - 1,7).SpecialCells(xlCellTypeVisible).Name = "WhateverNameYouWant"
End With

 

[Trikageon]

Thank you sooo much!! I had to adjust the code a touch, but I got what I was looking for! Here's the final version:

Selection.AutoFilter Field:=2, Criteria1:="320"
With ActiveSheet.AutoFilter.Range
.Offset(1, 3).Resize(.Rows.Count - 1, 7).SpecialCells(xlCellTypeVisible).Name = "ThreeTwenty"
End With

On a separate note, how do you indent your code on this page, btw?