MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Setting print areas


Posted by Brian M. on April 12, 2001 1:29 AM

How can I automatically set print areas so that only columns and rows with data in are printed


Posted by RS on April 12, 2001 2:39 AM


Sub Print_NonBlank_RowsColumns()
Dim c As Integer, r As Long
Dim cx As Integer, rx As Long
c = Cells.SpecialCells(xlCellTypeLastCell).Column
r = Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For cx = 1 To c
If Application.CountA(Columns(cx)) = 0 Then Columns(cx).Hidden = True
Next
For rx = 1 To r
If Application.CountA(Rows(rx)) = 0 Then Rows(rx).Hidden = True
Next
ActiveSheet.PrintOut
With Cells
.EntireRow.Hidden = False
.EntireColumn.Hidden = False
End With
Application.ScreenUpdating = True
End Sub


Posted by Brian M. on April 12, 2001 3:39 AM

Thanks, it worked perfectly.