OzGrid

How to print range excluding blank rows with formulas

Requirement:

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. 

Solution:

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
        ActiveWindow.ActiveSheet.PrintPreview
         
    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:

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.

Gallery