Here is one way of doing it.

Private Sub CommandButton1_Click()

intRow = 1: intCol = 1

Do While Cells(1, intCol) < ""
intCol = intCol + 1
Loop

Do While Cells(intRow, 1) < ""
intRow = intRow + 1
Loop

If intCol 1 Then
Range("a1:" & Chr(intCol + 63) & intRow - 1).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$" _
& Chr(intCol + 63) & "$" & intRow - 1
Range("A1").Select
End If

End Sub


The code starts in cell A1. If you want to start in a different cell you
will have to use ActiveCell.Address as the starting point.



"Mark" wrote in message
om...
It used to be easy in the "old" days to capture a macro in which you
anchor the upper left most cell of the target range and then hold
shift and press the right arrow, then the down arrow to highlight a
range of cells.

You could then make this the print area, change formatting, etc.

I haven't learned the syntax for such in Excel's VB. It would be
insightful to see how to write this in a macro (module) and also in
the sheet where it could be executed with an active-x conrol.

Thanks in advance for any examples, suggestions, etc.

Mark