Auto Filter Table Range

[julhs]

Naively thought; .Range("D8:D16"). AutoFilter would limit the filter to JUST that range.

However it is filtering the whole of Col D (D:D).
What do I need to change so it ONLY filters "D8:D16"

Sub AutoFilterTable1Column1CellValue()
        With ThisWorkbook.ActiveSheet
             .Range("D8:D16").AutoFilter Field:=1, Criteria1:="" & .Range("B8").value
     End With
End Sub

 

[TedX]

Hi Julhs, first up, I am a RANK beginner, so anything I say is to be treated with the greatest suspicion. I tried your code and it worked for me exactly as you have written it, provided I understand exactly what it is you are trying to accomplish. The only thing I will say is that the next cell in the table, beneath D16 (D17) must be empty for it to work. Here is my first image:

Then I run your macro and get the second image:

The reason D17 must be empty is that if you put any content in there, it extends the table from D8:D17, not counting the header. Now, as I look at this, I doubt that it is anything like what you are after, but my corny attempt might inspire others to actually come up with the real solution.

 

[TedX]

I also mucked around trying to understand the code a bit more and managed to write it slightly differently and come up with the same result.

Sub CreateFilter()
    
    With ThisWorkbook.ActiveSheet
        Range("D8:D16").AutoFilter
        ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=1, Criteria1:="" & .Range("B8").Value
    End With
    
End Sub

Of course, I still have no idea what I'm doing, so please forget the above. I hope someone else who knows what he's doing comes to your rescue soon

 

[HaHoBe]

Hi julhs,

I doubt AutoFilter will not support this directly

Working around this might be adding a helper column and using that as the second filter (Code must be adapted as my data starts on A1):

Sub AutoFilterTable1Column1CellValue_mod()
'https://www.mrexcel.com/board/threads/auto-filter-table-range.1225852/
  Dim lngNewCol As Long
  With ThisWorkbook.ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Cells(1, .Columns.Count).End(xlToLeft)
      If .Value = "Count" Then .EntireColumn.Delete
    End With
    lngNewCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    .Cells(1, lngNewCol).Value = "Count"
    .Range(.Cells(2, lngNewCol), .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, lngNewCol)).FormulaR1C1 = "=--SUBTOTAL(3,R2C4:RC[-11])"
    .UsedRange.Rows(1).AutoFilter Field:=4, Criteria1:="" & .UsedRange.Range("D2").Value
    .UsedRange.Rows(1).AutoFilter Field:=lngNewCol, Criteria1:="<=8", Operator:=xlAnd
  End With
End Sub

Ciao,
Holger

 

[julhs]

TedX
Thanks for that.
Your comment regarding D17 needing to be “Blank” (which it was supposed to be) made me examine contents of D17.
Unbeknown and unseen by me; there was a full stop in D17.!!!!

Just another example of not being able to see the wood from the trees

Many thanks

 

[julhs]

HaHoBe
Appreciate your possible alternative suggestion.

As you will see, there was a school boy/blind man’s error!!!!!
That’s why I could'nt understand why in ANY search re: "Filtering"; they used a “Range”, but mine “Filtered” the whole column.

I’m currently tiptoeing/testing things that are part of solving a previously unsuccessful method; your example may come in useful?

 

[TedX]

TedX
Thanks for that.
Your comment regarding D17 needing to be “Blank” (which it was supposed to be) made me examine contents of D17.
Unbeknown and unseen by me; there was a full stop in D17.!!!!

Just another example of not being able to see the wood from the trees

Many thanks

Thank you Julhs, you are my first ever person that I helped, which strangely coincides with my 100th message. It's usually me getting the help. For years I tried tutorials and watching YouTube videos and I guessed they helped a bit but I have learned more from this forum than via any other method. Of course, I still have years to go, but my intention is to try and learn by analysing what other real people get into trouble with and then the marvellous responses they get. I have right from day one found that there are usually several ways to resolve an issue. No one way only, that's a key for me because it shows me that VBA is more than just the sum of several values in a few cells. Best wishes to you in the UK from a sunburnt Aussie Downunder.

 

[julhs]

I myself have posted loads of questions but only managed to provide a hand full of “Solutions”.

It is VERY gratifying when you do, at least it shows I have learnt SOMETHING in my VBA/Excel journey through trial and error and masses of help from the forum.