Select Filtered Data

[WizzardOfOz]

I set-up a macro to filter data. Everything was working well. Person came back to say it's no longer working.

'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

    if .AutoFilter.Range.Columns(1).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

[snb]

The information you provided is rather scarce.

Filtering 1 row will never result in more than 1 row.

Sub M_snb()
  With Sheets("Data")
    .ShowAllData
    With .Range("A1:S100")
        For j = 2 To ubound(oTrends,2)
            If oTrends(i, j) <> "" Then .AutoFilter j, oTrends(i, j)
        Next
        .Offset(1).Copy Cells(1, 100)
        .AutoFilter
    End With
    sn=cells(1,100).currentregion
  End With
End Sub

[WizzardOfOz]

Thanks but I already tried more rows and that's not the problem. The filtering part works fine.
Googled and tried most things I found. The only one that gives the correct rows is including the .columns(1) but then the selection fails

[snb]

Did you use my code ?

Because the ubound(sn) will return the correct number of 'visible rows' as you put it.

Post a sample workbook to avoid any misunderstanding.

[WizzardOfOz]

Snb, no your code does not work. I tried the following

 With Worksheets("Data")
            .AutoFilterMode = False
            .Range("A:S").AutoFilter
            For j = 2 To k
                If oTrends(i, j) <> "" Then .Range("A:S").AutoFilter Field:=j, Criteria1:=oTrends(i, j)
            Next

'put a break here and check the options

iRows = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count     'this works 9 rows returned
jCols = .AutoFilter.Range.Rows(1).SpecialCells(xlCellTypeVisible).Count            'this is just checking, also works 19 cols


If iRows > 1 Then

    oFiltered = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Value2    '1 row returned

    oFiltered = .AutoFilter.Range.Value2        'all 270 rows returned        
        'as per http://www.ozgrid.com/forum/showthread.php?t=60879

    oFiltered = .AutoFilter.Range.Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Value2  '1 column returned 
        'as per http://www.contextures.com/xlautofilter03.html#Copy
        
    oFiltered = .Range("A1:S" & iRows).SpecialCells(xlCellTypeVisible).Value2 '1 row returned   
        'as per http://social.msdn.microsoft.com/For...forum=exceldev
        
    oFiltered = .Cells.CurrentRegion                 'all 270 rows returned   
        'as per snb
End If

[snb]

Why did you ignore my request ?

[WizzardOfOz]

Because it's not my data to share. The code should fail/work irrespective of the original data

[WizzardOfOz]

Why did you ignore my request ?

I used a small sample set
A B C
abc Yes 1
def Yes 2
abc No 3
def No 4
def No 5

Modified code as above and still it either returns 1 or all data (visible and invisible)

[WizzardOfOz]

Solved the problem by doing it all in a memory array and not using inbuilt filtering.
For those interested here is the 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