Hide empty rows before TOTAL row when save file as PDF

Hi Kalil

Not sure I understand your question fully (and you didn't upload a file) but the attached file includes the revised code below.

If you click the peach coloured button "Create pdf (without blank rows)", it will create a pdf of the data but skip any rows which are all blank.

Revision 17 June 2023 (to find blank cells in B:F only)

It hides those by checking if the number of blanks cells is the same as the number of cells in the row of the range B:F. Once the pdf is printed, all rows are unhidden.

Second revision 17 June 2023 (using user file and keeping rows 1 to 20)

The code now clears blank rows between row 20 and the last (TOTAL) row a file and the users file sheets are used.

Changes are in bold including a better way to determine the last used row (of column A):

Public Sub Save_Columns_As_PDF()

    Dim mainFolder As String, subfolder As String, PDFfile As String
    Dim lastRow As Long
    Dim n As Long

    mainFolder = "C:\Users\pc\Desktop\ARCHIVE\"
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"

    With ActiveWorkbook.Sheets("INVOICE")
        subfolder = mainFolder & .Range("G1").Value & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        subfolder = subfolder & UCase(Format(Date, "mmm")) & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        PDFfile = subfolder & .Range("G6").Value & ".pdf"

        ' get last used row in column A
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row
        ' don't show user when rows are hidden
        Application.ScreenUpdating = False

        ' loop down rowsfrom row 21 to before total
        For n = 21 To lastRow - 1
            ' look in B:F
            Set ChkRow = Intersect(.Rows(n), .Range("B1:F" & lastRow))
            ' hide row if all are blank cells
            If WorksheetFunction.CountBlank(ChkRow) = ChkRow.Cells.Count Then
                .Rows(n).Hidden = True
            End If
        Next n
        ' print non-blank range
        With .Range("A1:G" & lastRow)
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            'unhide blank rows
            .EntireRow.Hidden = False
        End With

        Application.ScreenUpdating = True
    End With

    MsgBox "Created " & PDFfile, vbInformation

End Sub

The user will just see the message that the file has been created.

Note that your choice of white font on a yellow background is low contrast and may be difficult to read for some people. I'd suggest you change the font to black or make the cell background a darker colour.

Other users should note that cells G1 and G6 are used to create the folder path and file name respectively.

Hope this is what you need. If so, please remember to mark this Answer as Selected.

Hello Kalil,

I took a similar approach as John did except my code assumes that all cells B:F, below the last row in "B" with data, are empty. Also, at the end of the code it enters a new row above the TOTAL row (point #2 in your original post)

Public Sub Save_Columns_As_PDF()

' macro modified by Willied24 for teachexcel.com

    Dim mainFolder As String, subfolder As String, PDFfile As String
    ' Dim lastRow As Long
'========================================
' new
Dim LR_A As Long   ' last blank/empty row in "A"
Dim LR_B As Long   ' last blank/empty row in "B"

LR_A = (Cells(Rows.Count, 1).End(xlUp).Row) - 1
LR_B = (Cells(Rows.Count, 2).End(xlUp).Row) + 1
'========================================

mainFolder = "C:\Users\pc\Desktop\ARCHIVE\"

' not required because line above includes ending "\"
'If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"

Application.ScreenUpdating = False

    With ActiveWorkbook.Sheets("SH1")
        subfolder = mainFolder & .Range("G1").Value & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        subfolder = subfolder & UCase(Format(Date, "mmm")) & "\"
        If Dir(subfolder, vbDirectory) = vbNullString Then MkDir subfolder
        PDFfile = subfolder & .Range("G6").Value & ".pdf"

'========================================
 ' hide the blank rows
Worksheets("SH1").Range("LR_B:LR_A").EntireRow.Hidden = True
'========================================

' new version
.Range("A1:G" & LR_A).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityMinimum, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

' un-hide the hidden rows
Worksheets("SH1").Range("LR_B:LR_A").EntireRow.Hidden = False
'========================================
' insert new row above "TOTAL" row
Rows(LR_A + 1).Insert , xlFormatFromLeftOrAbove
'========================================
Application.ScreenUpdating = True
    End With

    MsgBox "Created " & PDFfile, vbInformation

End Sub

If you prefer this method, please mark the answer as seleted.

Cheers   :-)