none
Force Autofilter on resized currentRegion areas RRS feed

  • Question

  • Hi and excellent new year 2019


    I try to optimize the updating Excel file processing time from an Access application so I want to apply autofilter on a resized currentRegion.


    This code below is a part of a routine which add / update Excel file with Access recordset.

        

    With xlsWkSheet
                 .AutoFilterMode = True
                 
                 lXlsRowNumber = .Cells(.Rows.Count, .Range("ColRef").Column).End(xlUp).Row
                 Set oXlsCurrentRegion=.Range("A1").CurrentRegion.Resize(RowSize:=lXlsRowNumber)
                 
                 With oXlsCurrentRegion
                    .AutoFilterMode = True
                 
                    lIdxCol = .Range("ColCrit1").Column
                    .AutoFilter Field:=lIdxCol, Criteria1:=IIf(Nz(oRecSet![ValueAccess], "") = "", "=", oRecSet![ValueAccess]) 
                    End If
                    
                    Set xlsRangeAutoFilter = .SpecialCells(xlCellTypeVisible)
                    
                End With


    When "=" is applied on autofilter because oRecSet![ValueAccess] is null, on left top corner of Excel file, it's displayed « 0 Of 1047585 records found »


    When I apply autofilter on blank file with only headers I expected « 0 of 1 records founds »


    I hoped that the filter was only applied on oXlsCurrentRegion areas but apparently not!



    Is there a way to apply blank criteria only on XlsCurrentRegion areas?


    Thanks by advance

    • Edited by technet65 Sunday, January 6, 2019 10:44 AM
    Saturday, January 5, 2019 7:41 AM

Answers

  • I found on The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method this code

    Sub AutoFilter_Range()
    
       'AutoFilter is a member of the Range object
       'Reference the entire range that the filters are applied to
       'AutoFilter turns filters on/off when no parameters are specified
      
       Sheet1.Range("B3:G1000").AutoFilter
      
       'Fully qualified reference starting at Workbook level
       ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
    
    End Sub

    Autofiltermode and filtermode have to be disabled  as we can read here How to use AutoFilters in Excel VBA Macros

     So the complete cod should be 
    Sub AutoFilter_Range()
     
     With ActiveSheet
       .FilterMode = False
       .AutoFilterMode = False 
     
      .Range("B3:G1000").AutoFilter
     End with
    End sub
    • Marked as answer by technet65 Sunday, January 6, 2019 10:43 AM
    • Edited by technet65 Sunday, January 6, 2019 10:44 AM
    Saturday, January 5, 2019 9:04 PM

All replies

  • I found on The Ultimate Guide to Excel Filters with VBA Macros – AutoFilter Method this code

    Sub AutoFilter_Range()
    
       'AutoFilter is a member of the Range object
       'Reference the entire range that the filters are applied to
       'AutoFilter turns filters on/off when no parameters are specified
      
       Sheet1.Range("B3:G1000").AutoFilter
      
       'Fully qualified reference starting at Workbook level
       ThisWorkbook.Worksheets("AutoFilter Guide").Range("B3:G1000").AutoFilter
    
    End Sub

    Autofiltermode and filtermode have to be disabled  as we can read here How to use AutoFilters in Excel VBA Macros

     So the complete cod should be 
    Sub AutoFilter_Range()
     
     With ActiveSheet
       .FilterMode = False
       .AutoFilterMode = False 
     
      .Range("B3:G1000").AutoFilter
     End with
    End sub
    • Marked as answer by technet65 Sunday, January 6, 2019 10:43 AM
    • Edited by technet65 Sunday, January 6, 2019 10:44 AM
    Saturday, January 5, 2019 9:04 PM
  • I tested this code below

    Private Sub CommandButton1_Click()
    
    With ActiveSheet
       .FilterMode = False
       .AutoFilterMode = False
        
        Set oRange = .Range("A2:E8")
        With oRange
            .AutoFilter Field:=5, Criteria1:="="  
        End With     
     End With
    End Sub
    

    And it works perfectly as we can see on the screen copy below

    • Marked as answer by technet65 Sunday, January 6, 2019 10:43 AM
    • Unmarked as answer by technet65 Sunday, January 6, 2019 10:43 AM
    • Marked as answer by technet65 Sunday, January 6, 2019 10:43 AM
    • Unmarked as answer by technet65 Sunday, January 6, 2019 10:45 AM
    Sunday, January 6, 2019 10:43 AM