How to print range excluding blank rows with formulas

< Back to Search results

 Category: [Excel]  Demo Available 

How to print range excluding blank rows with formulas




The user is trying to set a print range in a report. The report is built off of a pivot table. The pivot table can change in size each month. In order to build the report the formulas are dragged down far beyond the number of rows ever needed in the report however when setting up the print report macro it includes all of the blank rows that have formulas in it. 



Sub PrintByMallInPlaceDollars()
    Set ms = Nothing
    Dim LastRow As Long
    With Worksheets("By Mall - In Place")
         'LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        LastRow = Sheets("By Mall - In Place").Columns("D").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
        ActiveSheet.PageSetup.PrintArea = "$A$1:$D" & LastRow  ' <<== DIFFERENT HERE
    End With
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Holycow.


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

How to print hidden sheets without viewing the sheet being printed


How to print userform in orientation landscape
How to print to specific network printer
How to use VBA code to print out an area


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)