Use AutoFilter on the current visible data

Question

The below code is used to set Autofiler data on the active sheet.
It works , But If I later used autofiletr on any column then all the hidden rows are shown again.
My aim is to use a helper column and set filter on value. In advance, thanks for any help.

Option Explicit
Option Compare Text

    Sub AutoFilter_on_visible_data()
    
         Dim ws As Worksheet, arr, i As Long, lastR As Long, HdRng As Range, rng As Range
    
         Set ws = ThisWorkbook.ActiveSheet
         lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
          
         arr = ws.Range("A3:R" & lastR).Value2                          'Place the relevant columns in an array for faster iteration
         
          For i = 1 To UBound(arr)
          
            If ws.Rows(i + 2).Hidden = False Then                       '(i + 2) because Data starts at Row_3
            
                If Not arr(i, 2) Like "*Oil*" And _
                   Not arr(i, 5) Like "*-SYS-14" And _
                   Not arr(i, 6) Like "*Oil" Then
                   
                   addToRange HdRng, ws.Range("A" & i + 2)               'Make a union range of the rows NOT matching criteria...
                      
                End If
             End If
           Next i
           
          Application.ScreenUpdating = False
             If Not HdRng Is Nothing Then HdRng.EntireRow.Hidden = True      'Hide not matching criteria rows.
          Application.ScreenUpdating = True
    End Sub
    
    Private Sub addToRange(rngU As Range, rng As Range)
        If rngU Is Nothing Then
            Set rngU = rng
        Else
            Set rngU = Union(rngU, rng)
        End If
    End Sub

Is the headers row on the second, or on the third one? I cannot understand only looking to your code... — FaneDuru, Apr 09 '23 at 14:02
I am now out of office, the header-row is on the second one, I will test today on my laptop and feed you back. — Waleed, Apr 09 '23 at 15:00

FaneDuru · Accepted Answer · 2023-04-10T08:46:20.117

2

Please, try using the next adapted code. It assumes that the headers exist on the second row. It accepts processing ranges containing rows being hidden by AutoFilter, not only manually. ShowAllData does not unhide the rows made hidden manually, and it must be placed after the filtered row processing:

Sub AutoFilter_on_visible_data()
         Dim ws As Worksheet, arr, i As Long, lastR As Long, lastCol As Long, arrH, rngH As Range, rng As Range
         Const helpH As String = "HelpColumn"
         
         Set ws = ThisWorkbook.ActiveSheet
         lastR = ws.Range("A" & ws.rows.count).End(xlUp).row
         lastCol = ws.cells(2, ws.Columns.count).End(xlToLeft).column 'last column, supposing that the header exists on the second row
         
         Set rngH = ws.rows(2).Find(what:=helpH, LookIn:=xlValues, Lookat:=xlWhole)
         If Not rngH Is Nothing Then
            lastCol = rngH.column
        Else
            lastCol = lastCol + 1
            ws.cells(2, lastCol).Value = helpH
        End If

         arr = ws.Range("A3:R" & lastR).Value2          'Place the relevant columns in an array for faster iteration
         ReDim arrH(1 To UBound(arr), 1 To 1)
         
          For i = 1 To UBound(arr)
             If ws.rows(i + 2).Hidden = False Then          '(i + 2) because Data starts at Row_3
            
                If Not arr(i, 2) Like "*Oil*" And _
                    Not arr(i, 5) Like "*-SYS-14" And _
                    Not arr(i, 6) Like "*Oil" Then
                   
                    arrH(i, 1) = "HH"                                        'Make a helper array to filter on it.
                   
                 End If
              End If
           Next i
           
           If Not ws.AutoFilterMode Then rng.AutoFilter 'autofilter the resized range
           ws.AutoFilter.ShowAllData
           
           lastR = ws.Range("A" & ws.rows.count).End(xlUp).row        'recalculate the last row after showing all
           Set rng = ws.Range(ws.cells(2, 1), ws.cells(lastR, lastCol)) 'to use it for filterring
           
           'clear the previous HH strings not included in the helper column because of the filter:
           ws.Range(ws.cells(3, lastCol), ws.cells(lastR, lastCol)).ClearContents
           
           'Drop the arrH content at once:
           ws.cells(3, lastCol).Resize(UBound(arrH), 1).Value2 = arrH
           
           'Filter on the helper column:
           rng.AutoFilter field:=lastCol, Criteria1:="HH", Operator:=xlFilterValues
End Sub

edited Apr 10 '23 at 08:46

answered Apr 09 '23 at 14:24

FaneDuru

38,298
4
19
27

Hidden rows exists because there are already an filter is applied for any column, I tried your code and (1) it will output wrong result with consecutive runs, sure I can clear `lastCol` before run the macro, but it is not the optimal method. (2) I thought you will find a better way than mine when checking for the visible range than `If ws.rows(i + 2).Hidden = False Then` , on your another answer [Link](https://stackoverflow.com/questions/75663287/hide-the-visible-duplicate-cells-by-using-a-helper-column?noredirect=1&lq=1) you have used a tricky way to handle this point. – Waleed Apr 10 '23 at 05:51
@Waleed 1. I cannot understand why. Please, specify how do you proceed after running it once. Theoretically, 'arrH` should also include hidden rows, meaning that an eventual "HH" on a row you hide does not matter. When `ReDim1` it everything is empty (hidden rows included). 2. I did not care about "checking for the visible range". I took your way, since you said that the code works. Then, my "another answer" does something different and **I also took the checking method you tried** there. Anyhow, both methods are the same in terms of being fast and elegance... – FaneDuru Apr 10 '23 at 07:04
@Waleed I wrote the above comment without testing anything, only looking to the code. I made two tests now and it looks that it behaves (in my case) as it should. If I still miss something, you can use `Else` in the `If` statement (`If Not arr(i, 2) Like "*Oil*" And ...`) and place there `arrH(i, 1) = ""`. But it looks to ,me being useless. Try the next testing way, please: Insert `Debug.Print UBound(arrH):Stop` immediately after ReDim it. How many rows does it show? Not hidden rows included? – FaneDuru Apr 10 '23 at 07:12
@Waleed Don't you **only manually** hide rows? – FaneDuru Apr 10 '23 at 07:18
Please consider this scenario: Firstly, I run your code without setting autofilter at any column (Filter arrows still appear on excel) , then I set filter on any single column e.g `"A"` , then I run the macro again , the result is **AutoFilter is cleared from column `A`** and only set on `lastCol` , the supposed result is to keep filter on column `A` and also add filter on `lastCol` – Waleed Apr 10 '23 at 07:28
@Waleed So you **do not only manually hide rows**... You use preliminary filters. This is a little different than I understood you do. I will try adapting it to accomplish what you need for the described (preliminary filtering) scenario. – FaneDuru Apr 10 '23 at 07:50
@Waleed Please, test the version posted after **Edited**. It should be working for filtered rows, too... – FaneDuru Apr 10 '23 at 08:00
Now the the edited answer works perfectly as supposed – Waleed Apr 10 '23 at 08:09
Please, now the edited answer is the working one ,But I observed that you always leave also your initial code (not working one) , for me (as a normal user) I got conflicted (with no offence) when I search for an answer. – Waleed Apr 10 '23 at 08:16
1

@Waleed Not exactly... I usually let the first version there, only if it can be of any help for somebody else. Now, I am afraid that nobody will be interested, so I will delete it. – FaneDuru Apr 10 '23 at 08:45
@Waleed OK. If not already solved, I will look at it... – FaneDuru Apr 11 '23 at 10:55
regarding this question [link](https://stackoverflow.com/questions/75983960/extract-one-numbers-from-string-with-condition) the `regex` answer is not fully reliable , Kindly Please, is your vast experience with VBA, especially arrays can help? – Waleed Apr 11 '23 at 13:07
@Waleed I am not sure I can get you... What non-reliability are you talking about? The fact that using the function as UDF is not so fast or something else? I am leaving now my office... – FaneDuru Apr 11 '23 at 14:02
I didn't test it for speed yet, because it extracts some wrong numbers. – Waleed Apr 11 '23 at 16:26
to save your time regarding this question [Link](https://stackoverflow.com/questions/75983960/extract-one-numbers-from-string-with-condition) the regex answer is now accepted , **Although I am sure it was can be fulfilled by using Arrays** – Waleed Apr 11 '23 at 18:27
@Waleed I do not see how to solve the issue using arrays. Only to create a function using standard VBA (`InStr` folowed by reversed iteration) and process an array extracted from the first column, the result being placed in another array and dropped of the code end. I can play with such a function/solution, but I have some doubts that it would be faster then using `RegEx`... – FaneDuru Apr 11 '23 at 19:00
you are correct and using the `regex function` inside an array is very fast. – Waleed Apr 12 '23 at 05:59
1

@Waleed Anyhow, for the sake of challenging, I will prepare an answer using standard VBA. But it (as `RegEx` solution) will process (only) the strings of pattern you show... – FaneDuru Apr 12 '23 at 06:30

score 1 · Answer 2 · answered Apr 09 '23 at 17:39

"My aim is to use a helper column and set filter on value", I agree, this column can be updated by a formula that will take the filter values from the three cells above the columns, so you can easily modify the filtering parameters. Possible changes in the checked columns will immediately update the auxiliary column and by pressing the button you will be able to filter the rows again. The code for the button should be in the sheet module, while the formula will be in another book module. You can also set Sub No_Filters(..) to another button to unset the filter.

Option Explicit

Private Sub CommandButton1_Click()
   Dim lastR As Long, lastCol As Long, r As Range
   Application.ScreenUpdating = False
   Call No_Filters(Me)
   lastR = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
   lastCol = Me.Cells(2, Me.Columns.Count).End(xlToLeft).Column
   Set r = Me.Range(Me.Cells(2, 1), Me.Cells(lastR, lastCol))
   r.AutoFilter Field:=lastCol, Criteria1:="1"
End Sub


'Copy In a module the code below:

'this is the formula for the helper column (returns 0 or 1)
Public Function checkCrit(a As String, crita As String, b As String, critb As String, c As String, critc As String) As Integer
    checkCrit = ((a Like crita) And (b Like critb) And (c Like critc)) * -1
End Function

'this is a sub to unset the filter if it is on
Public Sub No_Filters(ByRef r As Worksheet)
   With r
      If .FilterMode Then
         .ShowAllData
      End If
   End With
End Sub

Use AutoFilter on the current visible data

2 Answers2