I have built an Invoice worksheet that has a top portion (A1:K19) that will remain static; this is the top portion of the invoice. Below Row 19 starts the individual item lines that will be populated with what items are ordered.
I have created a macro for the user that will automatically format the invoice into a 1 page PDF; however I now want to make it where the user can populate the line items and not have to worry about removing or hiding the empty rows left over. The line item range is (A20:J59) 40 rows. I have tried a few things, but the cells are not technically empty, they have formulas.
Question: What type of VBA code would allow me to auto hide the empty rows that are empty (they have VLOOKUP formulas in them) in the invoice, but still show the bottom of the invoice for totals (which is part of the print area) and once exported to PDF would unhide so they user would see the invoice template in its standard format.
Below is the current VBA script I have written.
Sub Save_Quote_As_PDF() Application.ScreenUpdating = False For i = 20 To 59 If ActiveSheet.Cells(i, 9) = "" Then ActiveSheet.Cells(i, 9).EntireRow.Hidden = True End If Next i Dim PdfFilename As Variant PdfFilename = Application.GetSaveAsFilename( _ InitialFileName:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("N2").Value, _ FileFilter:="PDF, *.pdf", _ Title:="Save As PDF") If PdfFilename <> False Then With ActiveSheet.PageSetup .Orientation = xlPortrait .PrintArea = "$A$1:$K$78" .PrintTitleRows = ActiveSheet.Rows(19).Address .Zoom = False .FitToPagesTall = False .FitToPagesWide = 1 End With ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=PdfFilename, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=False, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=True End If For i = 20 To 59 If ActiveSheet.Cells(i, 9) = "" Then ActiveSheet.Cells(i, 9).EntireRow.Hidden = False End If Next i Application.ScreenUpdating = True End Sub