This code would do it for currently selected sheet - and if that sheet
happens to be blank, it will clear the print area. Problem is determining
which column is always going to be the longest, and which row will always be
widest. For the example, I've simply assumed that column A is always
longest, row 1 always widest:
Sub SetPrintArea()
Dim printRange As String
'assumes:
' always start print area at A1
' column A is always longest
' row 1 always widest
'if sheet is blank, PrintArea cleared
printRange = ""
If Range("A65536").End(xlUp).Row > 1 Or _
Range("IV1").End(xlToLeft).Column > 1 Then
printRange = "$A$1:" & _
Cells(Range("A65536").End(xlUp).Row, _
Range("IV1").End(xlToLeft).Column).Address
End If
ActiveSheet.PageSetup.PrintArea = printRange
End Sub
and if you wanted to adjust all sheets in the workbook before printing, this
would do the same thing when associated with the Workbook_BeforePrint() event:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim printRange As String
Dim anySheet As Worksheet
For Each anySheet In Worksheets
printRange = ""
If anySheet.Range("A65536").End(xlUp).Row > 1 Or _
anySheet.Range("IV1").End(xlToLeft).Column > 1 Then
printRange = "$A$1:" & _
anySheet.Cells(Range("A65536").End(xlUp).Row, _
Range("IV1").End(xlToLeft).Column).Address
End If
anySheet.PageSetup.PrintArea = printRange
Next
End Sub