Select Filtered Data

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.

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
 

snb

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
The information you provided is rather scarce.

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

Code:
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

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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

New member
Joined
May 15, 2013
Messages
376
Reaction score
0
Points
0
Website
www.snb-vba.eu
Excel Version(s)
2020
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

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Snb, no your code does not work. I tried the following

Code:
 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 [COLOR=#ff0000][B]9 rows[/B][/COLOR] 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    [B][COLOR=#ff0000]'1 row returned[/COLOR][/B]

    oFiltered = .AutoFilter.Range.Value2        '[B][COLOR=#ff0000]all 270 rows returned [/COLOR][/B]       
        'as per [URL]http://www.ozgrid.com/forum/showthread.php?t=60879[/URL]

    oFiltered = .AutoFilter.Range.Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Value2  [B][COLOR=#ff0000]'1 column returned[/COLOR][/B] 
        'as per [URL]http://www.contextures.com/xlautofilter03.html#Copy[/URL]
        
    oFiltered = .Range("A1:S" & iRows).SpecialCells(xlCellTypeVisible).Value2[B][COLOR=#ff0000] '1 row returned [/COLOR][/B]  
        'as per [URL]http://social.msdn.microsoft.com/Forums/office/en-US/19328ea2-fd1a-427c-98e3-b096ab62e5f2/vba-code-to-filter-data-copy-and-paste-in-new-sheet?forum=exceldev[/URL]
        
    oFiltered = .Cells.CurrentRegion                 [B][COLOR=#ff0000]'all 270 rows returned[/COLOR][/B]   
        'as per snb
End If
 
Last edited:

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
Because it's not my data to share. The code should fail/work irrespective of the original data
 

WizzardOfOz

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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
 
Top