locked
AutoFilter Issue RRS feed

  • Question

  • Current worksheet I'm using includes 7 rows of data with header line on row 8 and then the rows of data requiring filtering.   Attempting to have a macro which will toggle on/off and when toggled on, making sure all columns are included in the filtered range for sorting purposes, etc. when changed by end user.   The following macro works well as long as row 7 is not populated.   If Row 7 is populated the macro filters show in row 1.  If I enter another row and have the header line in Row 9 then the filtering defaults to row 8 - a row above the actual headers in this case.  If both Row 7 and 8 are blank it works.   If row 7 is populated and row 8 is blank it works.  What am I doing wrong?  I really don't want to add a blank row just to address this issue and I can't delete any of the rows prior to the columnar headers in row 8.

    Sub FilterMe()
        With Sheets("Sheet1")
            If Not .AutoFilterMode Then
                .Range("$A$8").AutoFilter
            Else
            If .AutoFilterMode Then
            .AutoFilterMode = False
            End If
            End If
        End With
    End Sub

    Appreciate any help...  

    Tried something else -- does the same thing

     With Sheets("Sheet1")
            If Not .AutoFilterMode Then
                .Range("$A$8").Select
                Selection.AutoFilter
            Else
            If .AutoFilterMode Then
            .AutoFilterMode = False
            End If
            End If
        End With

    End Sub


    • Edited by pculber Saturday, August 4, 2012 11:48 PM Another attempt
    Saturday, August 4, 2012 11:23 PM

Answers

  • Here is a tiny toggle macro.  There is arbitrary junk in rows 1 thru 7, headers in row 8, data in rows 9 thru 20:

    Sub Macro1()
        Dim r As Range
        Set r = Range("A8:J20")
        If ActiveSheet.AutoFilterMode Then
            ActiveSheet.AutoFilterMode = False
        Else
            r.AutoFilter
            r.AutoFilter Field:=1, Criteria1:="<50"
        End If
    End Sub


    gsnu201208

    • Proposed as answer by Leo_Gao Monday, August 6, 2012 6:07 AM
    • Marked as answer by Asadulla JavedEditor Monday, August 6, 2012 10:10 AM
    Sunday, August 5, 2012 1:18 AM
  • A little amendment to Gary's Student code. Only the row of column headers is required to set AutoFilter under these conditions and you don't need to know how many rows of data are under the column headers.

    Sub Macro1()
         Dim r As Range
         Set r = Range("A8:J8")      '<---*** One row. Column headers only
         If ActiveSheet.AutoFilterMode Then
             ActiveSheet.AutoFilterMode = False
         Else
             r.AutoFilter
             r.AutoFilter Field:=1, Criteria1:="<50"
         End If
     End Sub


    Regards, OssieMac

    • Proposed as answer by Leo_Gao Monday, August 6, 2012 6:07 AM
    • Marked as answer by Asadulla JavedEditor Monday, August 6, 2012 10:10 AM
    Sunday, August 5, 2012 1:51 AM

All replies

  • Here is a tiny toggle macro.  There is arbitrary junk in rows 1 thru 7, headers in row 8, data in rows 9 thru 20:

    Sub Macro1()
        Dim r As Range
        Set r = Range("A8:J20")
        If ActiveSheet.AutoFilterMode Then
            ActiveSheet.AutoFilterMode = False
        Else
            r.AutoFilter
            r.AutoFilter Field:=1, Criteria1:="<50"
        End If
    End Sub


    gsnu201208

    • Proposed as answer by Leo_Gao Monday, August 6, 2012 6:07 AM
    • Marked as answer by Asadulla JavedEditor Monday, August 6, 2012 10:10 AM
    Sunday, August 5, 2012 1:18 AM
  • A little amendment to Gary's Student code. Only the row of column headers is required to set AutoFilter under these conditions and you don't need to know how many rows of data are under the column headers.

    Sub Macro1()
         Dim r As Range
         Set r = Range("A8:J8")      '<---*** One row. Column headers only
         If ActiveSheet.AutoFilterMode Then
             ActiveSheet.AutoFilterMode = False
         Else
             r.AutoFilter
             r.AutoFilter Field:=1, Criteria1:="<50"
         End If
     End Sub


    Regards, OssieMac

    • Proposed as answer by Leo_Gao Monday, August 6, 2012 6:07 AM
    • Marked as answer by Asadulla JavedEditor Monday, August 6, 2012 10:10 AM
    Sunday, August 5, 2012 1:51 AM
  • Thank you

    Monday, August 6, 2012 1:07 AM
  • Thank you

    Monday, August 6, 2012 1:07 AM