Results 1 to 9 of 9

Thread: Select Filtered Data

  1. #1
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365

    Select Filtered Data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.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

  2. #2
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    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

  3. #3
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    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

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    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.

  5. #5
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    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 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
    Last edited by WizzardOfOz; 2014-10-01 at 03:20 AM.

  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    Why did you ignore my request ?

  7. #7
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Because it's not my data to share. The code should fail/work irrespective of the original data

  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    Quote Originally Posted by snb View Post
    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)

  9. #9
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •