0

I'm using a filter on a sheet in Excel. All I want to do is uncheck "empty cells" in field 1 of my filterrange. I know how to uncheck the "empty cells" but the existing filter (made by user) must stay active.

Code I'm using to get the name of the filterrange:

Dim obj As ListObject
Dim Tabel As String
Dim filter As Boolean
filter = False
For Each obj In ActiveSheet.ListObjects
    Tabel = obj.Name
    Exit For
Next

Code that I use to uncheck "empty cells" when no filter is applied:

ActiveSheet.ListObjects(Tabel).Range.AutoFilter field:=1, Criteria1:="<>"

What I prefer to get is the actual filter used in field 1, add the criteria:="<>" do my thing en reapply the filter applied by the user.

Thing I was trying:

Dim ActieveFilters As String
Dim FilterArray()
Dim LaatsteFilter As Long
Dim i As Long
With ActiveSheet.ListObjects(Tabel).AutoFilter.Filters(1)
    ReDim FilterArray(1 To .Count)
    For f = 1 To .Count
        With .Item(f)
            If .On Then
                FilterArray(f) = Criteria1
            End If
        End With
    Next f
End With

Active filters

Joete
  • 39
  • 8
  • Can you show shots of your data and what the desired outcome would be? What does empty mean in this context? – QHarr Nov 20 '17 at 10:00
  • I added an image in the original post (Active filters). There you see the filters used in the worksheet. I want to turn off "(Lege cellen)" (it's dutch), but then the user has allready turned off other checkboxen, then they have to stay off and the checkbox "(Lege cellen)" must be turned off also and turned on later. But when I know how to turn it off, I think I'll find a way to turn it back on myself... – Joete Nov 20 '17 at 10:32
  • See here: https://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter – QHarr Nov 20 '17 at 11:00
  • Can't get it working... I found another way to get what I want, ignoring empty cells. Not the finest way, because it has to loop 1100 extra cells sometimes, but it works for now – Joete Nov 21 '17 at 16:14
  • Ok. I am going to have another look just been a very busy day today. – QHarr Nov 21 '17 at 16:19

0 Answers0