# Auto Filter Table Range



## julhs (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

julhs said:


> 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.!!!!
> ...



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 (Jan 3, 2023)

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.


----------

