• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hiding Rows That Do Not Meet Criteria

M

Mario Cerato

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

Code: 
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
 
Hi Mario, and welcome to the forum! :awesome:

Here's the cleaned up code, but not sure I understand your last question. After filtering, doesn't it only show the data of interest? what blank rows are you seeing? Is your data really in A6:ET1000, or should this be dynamic?

Code: 
Sub PipelineShowAPPROVEDLoans()
   
    Application.ScreenUpdating = False
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
    With ActiveSheet.Range("$a$6:$ET$1000")
        .AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
        .AutoFilter Field:=7, Criteria1:="APPR"
        .AutoFilter Field:=6, Criteria1:="<>"
    End With
    Application.ScreenUpdating = True
End Sub
 
Thanks Luke, 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.
Capture.JPG Capture.JPG
 
Gotcha, try this bit:
Code: 
Sub PipelineShowAPPROVEDLoans()
   Dim lastRow As Long
   
    Application.ScreenUpdating = False
    If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
      ActiveSheet.ShowAllData
    End If
   
    'Find last row of data
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    With ActiveSheet.Range("$a$6:$ET$1000")
        .AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
        .AutoFilter Field:=7, Criteria1:="APPR"
        .AutoFilter Field:=6, Criteria1:="<>"
    End With
   
    'Hide extra rows
    Range(lastRow + 1 & ":" & Rows.Count).EntireRow.Hidden = True
    Application.ScreenUpdating = True
End Sub
 
Luke, thank you for your suggestion but I was able to get it to work with this code here, it is a simpler solution:

Code: 
Dim r As Range
Set r = ActiveSheet.UsedRange
   ActiveSheet.Range("$a$6:$ET$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=7, Criteria1:="APPR"
    ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=6, Criteria1:="<>"
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
 
You must log in or register to reply here.
Back
Top