Automatically hide rows with "FALSE" values

[PapaRainman]

Hi Guys!

Needing your help with this.

What I would want to do is to:

1. Hide the rows that has the value of "FALSE" as a result of an IF and VLOOKUP formula.
2. Select the cells and set the print area.
3. Print as PDF - I am already good with this :D

Thank you.

 

[Yongle]

welcome to the forum

The image in post#1 does not include row & column identifiers
- amend "F" if necessary

Sub HideRows()
    Const X = "F"
    Dim r As Long, Rng As String
    Rng = "@"
    On Error Resume Next
    For r = 2 To Cells(Rows.Count, X).End(xlUp).Row
        If Cells(r, X).Value <> CVErr(xlErrNA) Then If Cells(r, X) = "False" Then Rng = Rng & "," & X & r
    Next r
    Hide Cells, False
    If Not Rng = "@" Then Hide Range(Replace(Rng, "@,", "")), True
End Sub

Private Sub Hide(aRange As Range, HideIt As Boolean)
    aRange.EntireRow.Hidden = HideIt
End Sub

 

[PapaRainman]

Hi Yongle,

Thank you for your reply. I have included the image with the identifiers.

Also, here is my code as of now. I'm having trouble where should I place your code.

Sub SelectAndCreatePDF()
Dim i As Integer, PDFindex As Integer
    Dim PDFfileName As String
    Dim LR As Long, cell As Range, rng As Range

    Range("A1:H12").Select
      ActiveSheet.PageSetup.Orientation = xlLandscape
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF
      ActiveSheet.PageSetup.PrintArea = "A1:H12"
      With Application.FileDialog(msoFileDialogSaveAs)
      
      PDFindex = 0
        For i = 1 To .Filters.Count
            If InStr(VBA.UCase(.Filters(i).Description), "PDF") > 0 Then PDFindex = i
        
        Next
        .Title = "Save sheet as PDF"
        .InitialFileName = PDFName
        .FilterIndex = PDFindex
        
        If .Show Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        End If
      
End With

End Sub

Appreciate your help with this.

 

[PapaRainman]

welcome to the forum

The image in post#1 does not include row & column identifiers
- amend "F" if necessary

View attachment 2580

Sub HideRows()
    Const X = "F"
    Dim r As Long, Rng As String
    Rng = "@"
    On Error Resume Next
    For r = 2 To Cells(Rows.Count, X).End(xlUp).Row
        If Cells(r, X).Value <> CVErr(xlErrNA) Then If Cells(r, X) = "False" Then Rng = Rng & "," & X & r
    Next r
    Hide Cells, False
    If Not Rng = "@" Then Hide Range(Replace(Rng, "@,", "")), True
End Sub

Private Sub Hide(aRange As Range, HideIt As Boolean)
    aRange.EntireRow.Hidden = HideIt
End Sub

I forgot to quote your reply. Sorry.

 

[Yongle]

If .Show Then
   Call HideRows
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If

 

[PapaRainman]

If .Show Then
   Call HideRows
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.SelectedItems(1), _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If

Thank you so much Yongle! I should have created a separate button instead to do hide rows. I'm getting compiled error when merging it.

Also, if I am planning to make a reset button to unhide the rows, can you please point me to the right direction?

 

[Yongle]

I am planning to make a reset button to unhide the rows, can you please point me to the right direction?

This makes all rows visible

 ActiveSheet.Cells.EntireRow.Hidden = False

 

[PapaRainman]

This makes all rows visible

 ActiveSheet.Cells.EntireRow.Hidden = False

Thank you very much Yongle! You’re a genius!

 

[Fluff]

@PapaRainman
For future reference while we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Automatically hide rows with "FALSE" values - OzGrid Free Excel/VBA Help Forum

Hi Guys! It's my first time here in OzGrid and would want to seek for your help. What I would want to do is to: 1. Hide the rows that has the value of "FALSE" as a result of an IF and VLOOKUP formula. 2. Select the cells and set the print area.…

www.ozgrid.com