I need a macro to set the print area of a sheet that may vary greatly in the
number of rows - in Visual Basic, the macro seems to only accept this
instruction as a specific reference to a cell range. I cannot pre-format the
sheets or tel it to 'fit to' as again, the size varies so much that fitting
to 1x1 might render the sheet completely unintelligible.

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


"Dave D" wrote:

I need a macro to set the print area of a sheet that may vary greatly in the
number of rows - in Visual Basic, the macro seems to only accept this
instruction as a specific reference to a cell range. I cannot pre-format the
sheets or tel it to 'fit to' as again, the size varies so much that fitting
to 1x1 might render the sheet completely unintelligible.