Macro to Auto-adjust print area in a multiple sheets (excel 2010)

I have a multiple sheets (tabs) where each sheet have variable print range. 1 sheet may need to print 1000 rows, others may need 10 rows only to print. The good thing is that, my column range is fixed at B:G. My criteria of including a row will be the last cell under column G with actual value. It's because all of these columns are populated with vlookup formulas. It should not inculde a row with a value of "" under column G.

Hi there,

Try inserting the following code into the "ThisWorkbook" VBA CodeModule:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Const sCOLUMN_TO_CHECK  As String = "G:G"
    Const sPRINT_AREA       As String = "$B$1:$G$"

    Dim rLastCell           As Range
    Dim iLastRow            As Integer
    Dim wks                 As Worksheet

    For Each wks In ThisWorkbook.Worksheets

        Set rLastCell = wks.Columns(sCOLUMN_TO_CHECK).Cells(1, 1)

        Do While rLastCell.Offset(1, 0).Value <> vbNullString
            Set rLastCell = rLastCell.Offset(1, 0)
        Loop

        iLastRow = rLastCell.Row

        wks.PageSetup.PrintArea = sPRINT_AREA & iLastRow

    Next wks

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi,

I pasted the above code to "This Workbook" but if I print preview, I'm still seeing hundreds of pages instead of only 1 as per the criteria. It means that nothing happens. I'm still hopeful that someone can help me. thanks.

hi, can anyone help me?