WizzardOfOz
New member
- Joined
- Sep 4, 2013
- Messages
- 184
- Reaction score
- 0
- Points
- 0
- Location
- Australia
- Excel Version(s)
- Office 365
I set-up a macro to filter data. Everything was working well. Person came back to say it's no longer working.
The problem is that it is returning the incorrect number of visible rows (only header=1) even if it has data. If I replace the second last line with
This returns the correct numbered of visible rows but I can't return the filtered range (remains just the header).
Column(1) is a date so it always has data but some of the other columns could all be empty. I suspect that the empty columns are impacting the xlCellTypeVisible
Thanks
Code:
'add the filters
With Worksheets("Data")
.AutoFilterMode = False
.Range("A1:S1").AutoFilter
For j = 2 To k
If oTrends(i, j) <> "" Then .Range("A1:S1").AutoFilter Field:=j, Criteria1:=oTrends(i, j)
Next
'return the filtered data
If .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
oFiltered = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Value2
The problem is that it is returning the incorrect number of visible rows (only header=1) even if it has data. If I replace the second last line with
Code:
if .AutoFilter.Range.[B][COLOR=#ff0000]Columns(1)[/COLOR][/B].SpecialCells(xlCellTypeVisible).Count > 1
This returns the correct numbered of visible rows but I can't return the filtered range (remains just the header).
Column(1) is a date so it always has data but some of the other columns could all be empty. I suspect that the empty columns are impacting the xlCellTypeVisible
Thanks