none
Hiding Rows That Do Not Meet Criteria RRS feed

  • Question

  • Hello community, reaching out for a tweak to my code below. I am trying to cleanup the view once the criteria is executed. I would like to hide rows that do not meet the criteria to not have to see a bunch of blank rows after criteria is filtered.

    Sub PipelineShowAPPROVEDLoans()
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
        ActiveSheet.Range("$a$6:$ET$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
        ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=7, Criteria1:="APPR"
        ActiveWindow.SmallScroll Down:=-15
        ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=6, Criteria1:="<>"
    End Sub



    MEC

    Wednesday, November 2, 2016 4:17 PM

Answers

  • Hi MEC,

    For filter empty rows which have gridlines, I think you could try below code.

        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=2, Criteria1:="B2"
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=1, Criteria1:="<>"

    For hiding empty rows which are not used, we could try to set the Hidden property.

    Here is a simple code:

    Dim r As Range
    Set r = ActiveSheet.UsedRange
    r.Select
        Selection.AutoFilter
        Selection.AutoFilter
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=1, Criteria1:="<>"
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=2, Criteria1:=Array("B5", _
            "B6", "B7", "B8", "B9"), Operator:=xlFilterValues
    'Debug.Print r.Rows.Count, Rows.Count
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mecerrato Friday, November 4, 2016 7:51 PM
    Friday, November 4, 2016 4:27 AM

All replies

  • Hi MEC,

    >> I would like to hide rows that do not meet the criteria to not have to see a bunch of blank rows after criteria is filtered.

    What do you mean by a bunch of blank rows? Could you share us a screen shot about your data and your expected result? A simple excel file would be much helpful.

    For setting multiple criterial, I think you need to add Operator, something like below:

        ActiveSheet.Range("$A$1:$H$3").AutoFilter Field:=1, Criteria1:="1"
        ActiveSheet.Range("$A$1:$H$3").AutoFilter Field:=3, Criteria1:="<>1", _
            Operator:=xlAnd

    In addition, if your issue has been resolved, I would suggest you mark the solution as answer, then other community members could find the solution easily and know whether a thread is resolved or not.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 3, 2016 6:29 AM
  • Thanks Edward, my criteria is giving me the proper result. I just want to HIDE the extra rows, I have attached a JPG of what I have now and what I am trying achieve. 

    MEC


    • Edited by mecerrato Thursday, November 3, 2016 2:20 PM
    Thursday, November 3, 2016 2:16 PM
  • Hi MEC,

    For filter empty rows which have gridlines, I think you could try below code.

        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=2, Criteria1:="B2"
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=1, Criteria1:="<>"

    For hiding empty rows which are not used, we could try to set the Hidden property.

    Here is a simple code:

    Dim r As Range
    Set r = ActiveSheet.UsedRange
    r.Select
        Selection.AutoFilter
        Selection.AutoFilter
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=1, Criteria1:="<>"
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=2, Criteria1:=Array("B5", _
            "B6", "B7", "B8", "B9"), Operator:=xlFilterValues
    'Debug.Print r.Rows.Count, Rows.Count
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mecerrato Friday, November 4, 2016 7:51 PM
    Friday, November 4, 2016 4:27 AM
  • Thank you Edward, this worked great, just tweaked it a bit, I removed the "r.Select" and "Selection.Autofilter" lines:

    Dim r As Range
    Set r = ActiveSheet.UsedRange
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=1, Criteria1:="<>"
        ActiveSheet.Range("$C$1:$D$13").AutoFilter Field:=2, Criteria1:=Array("B5", _
            "B6", "B7", "B8", "B9"), Operator:=xlFilterValues
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True


    MEC

    Friday, November 4, 2016 7:52 PM