Iterating on transparent visible cells in VBA
I am trying to do something weird and it may not be the only way and it is probably wrong. I want to iterate over the visible cells (which is the result of the AutoFilter function from VBA).
That's what I've done before, and it works, but not as much as I want:
Sheets("MySheet").Range("$A$3:$AI$10191").AutoFilter Field:=12, Criteria1:=myList, Operator:=xlFilterValues
. myList is a list of String, for example: Dim myList() as String
This currently doesn't match what I want: I don't want this to accept empty cells, and I also want the content of myList () to be accepted approximately (something like "& myList & ").
To do this, 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 : I want to do this for all the elements of my list. I was thinking about iterating through my list (myList), but every time I do a new iteration, it just doesn't accept the result of the previous iteration . In fact, I just want to filter out the "already filtered" line ...
I've already tried method .SpecialCells(xlCellTypeVisible)
, but it takes all of the cells, not just the visible .... (here the complete code with visible cells: Sheets("MySheet").Range("$A$3:$AI$10191").SpecialCells(xlCellTypeVisible).AutoFilter Field:=12, Criteria1:="*"&myList(0)&"*", Operator:=xlFilterValues
)
I thought of something similar:
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 is just filtered after the last rule .AutoFilter
. (it filters on i=UBound(myList)
because each .AutoFilter erases the job of the previous one ...)
If you have any ideas ... Thanks, Clement.
source to share
To try the advanced filter, you can try. Adjust it as needed ...
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
source to share