CODEWITHSUNDEEP
vbaexcel
Clement B
Clement B

Reputation: 218

Iterate on autofiltered visible cells on VBA

I'm trying to do something weird, and it's maybe not the only way, and it's probably the wrong one. I want to iterate on the visible cells (which is the result of the AutoFilter function of VBA).

Here's what I've done before, and it works, but not how I want : Sheets("MySheet").Range("$A$3:$AI$10191").AutoFilter Field:=12, Criteria1:=myList, Operator:=xlFilterValues . myList is a list of String, like this : Dim myList() as String Currently, this does not fit with what I want : I do not want this to take the blank cells, and I also want the myList() content be taken approximativly (something like ""&myList&"").

To do that, I tried one simple line of code and it works : Sheets("MySheet").Range("$A$3:$AI$10191").AutoFilter Field:=12, Criteria1:="*"&myList(0)&"*", Operator:=xlFilterValues

My problem is : I want to do that for all the elements of my list. I've been thinking about iterating through my list (myList) but everytime I'm doing a new iteration, it simply does not take the result of the previous iteration . In reality, I just want to filter on the "already filtered" rows... I already tried the method with .SpecialCells(xlCellTypeVisible) but it takes all the cells, and not only the visible one.... (here's the full code with visible cells : Sheets("MySheet").Range("$A$3:$AI$10191").SpecialCells(xlCellTypeVisible).AutoFilter Field:=12, Criteria1:="*"&myList(0)&"*", Operator:=xlFilterValues)

I've been thinking on something like that : 

For i =0 to UBound(myList)
    Sheets("MySheet").Range("$A$3:$AI$10191").SpecialCells(xlCellTypeVisible).AutoFilter Field:=12, Criteria1:="*"&myList(i)&"*", Operator:=xlFilterValues
Next i

But it just filters following the last .AutoFilter rule. (it filters on i=UBound(myList) , because each .AutoFilter is erasing the job of the previous one... )

If you have any ideas... Thanks, Clément.

Upvotes: 3

Views: 364

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

To try the Advanced Filter, you can give this a try. Tweak it as required...

Sub AdvancedFilter()
Dim wsData As Worksheet, wsCriteria As Worksheet
Dim myList() As String
Dim i As Long, lr As Long
Dim Rng As Range, Cell As Range

Application.ScreenUpdating = False

Set wsData = Sheets("MySheet")
If wsData.FilterMode Then wsData.ShowAllData
lr = wsData.UsedRange.Rows.Count

On Error Resume Next
Set wsCriteria = Sheets("Criteria")
wsCriteria.Cells.Clear
On Error GoTo 0

If wsCriteria Is Nothing Then
    Sheets.Add.Name = "Criteria"
    Set wsCriteria = ActiveSheet
End If

'Assuming myList has already been populated

'Writing the column header (column 12) on Criteria Sheet in A1
wsCriteria.Cells(1, 1) = wsData.Cells(1, 12)

'Adding wild card to each element in myList
For i = 1 To UBound(myList)
    myList(i) = "*" & myList(i) & "*"
Next i

'Writing myList on Criteria Sheet
wsCriteria.Range("A2").Resize(UBound(myList)).Value = Application.Transpose(myList)

'Using Advanced Filter on Data Sheet with Criteria on Criteria Sheet
wsData.Range("A1").CurrentRegion.AdvancedFilter xlFilterInPlace, wsCriteria.Range("A1").CurrentRegion

'Deleting the Criteria Sheet as it is not required now
Application.DisplayAlerts = False
wsCriteria.Delete
Application.DisplayAlerts = True

'Setting Rng as visible cells in column A
On Error Resume Next
Set Rng = wsData.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible)

If Not Rng Is Nothing Then
    For Each Cell In Rng
        'Do your stuff here with visible range
    Next Cell
End If
If wsData.FilterMode Then wsData.ShowAllData
Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions