Solved the problem by doing it all in a memory array and not using inbuilt filtering.
For those interested here is the code
Code:
iResult = 2
For iTrend = 1 To lTrends
Application.StatusBar = "Checking " & oTrends(iTrend, 1)
'run the filter
ReDim oFiltered(1 To lRow, 1 To lColumn) 'make space
iFilter = 0
For iRows = 1 To lRow
bMatch = False
For j = 2 To lColumn
If Not (IsEmpty(oTrends(iTrend, j)) Or oTrends(iTrend, j) = "") Then
bMatch = oTrends(iTrend, j) = oData(iRows, j)
If Not bMatch Then Exit For
End If
Next j
If bMatch Then 'add the row
iFilter = iFilter + 1
oFiltered(iFilter, 1) = oTrends(iTrend, 1) & "|" & iRows 'add the row no
For j = 2 To lColumn
oFiltered(iFilter, j) = oData(iRows, j)
Next j
End If
Next iRows
'dump it out
If iFilter > 0 Then
Worksheets("Filtered").Cells(iResult, 1).Resize(iFilter, lColumn) = oFiltered
iResult = iResult + iFilter
End If
Next iTrend
I suspect the problem is an Excel setting somewhere. The fact that it used to work and now doesn't
iRows = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count 'this works 9 rows returned
iRows = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count 'this doesn't work 1 rows returned
Bookmarks