Rick, That didn't quite get it either. It set the print area to all cells
that had border formatting also.

I started playing with a smaller version of the sheet to try a work-around,
and I got it to work. It finds if there is data in the last columns that are
sometimes blank in the none active worksheet. If not it cuts the formulas
out of the corresponding column in the active sheet, sets the print area and
then pastes them back. It is always the columns on the right-hand side so
the CurrentRegion works.

But I have a question I hope someone can answer for my future reference.

The below code works and I will modify it to include the other columns, but
the 'commented out code would not work and I wondered why.

Private Sub Cmd_SelectPrint_Click()
Dim WS As Object
Dim Q As Range
Dim W As Range

Set WS = ThisWorkbook.Sheets("Trucks")
Set Q = Range("Q1:Q65")
Set W = Range("W1:W65")

If WS.Range("O2").Value = ("") Then
Q.Cut
' W.Paste
' W.PasteSpecial paste:=xlPasteAll
W.Select
ActiveSheet.paste

ActiveSheet.Range("C1").CurrentRegion.Select
ActiveSheet.PageSetup.PrintArea = Selection.Address

' W.Cut
' Q.Select
' ActiveSheet.paste

ActiveSheet.Range("W1:W65").Select
Selection.Cut
Range("Q1:Q65").Select
ActiveSheet.paste

End If

End Sub





"Rick Rothstein" wrote in message
...
If I you are saying that your data has no internal blank cells, and that
your data starts in A1, then you can use this macro to set the print area
for only the cells displaying a value...

Sub SetPrintArea()
Dim LastRow As Long, LastCol As Long
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastCol = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column
ActiveSheet.PageSetup.PrintArea = Range("A1"). _
Resize(LastRow, LastCol).Address
End Sub

--
Rick (MVP - Excel)


"CR" wrote in message
...
All of the cells have formulas. Say, A1:AZ100 At times the visible data
only goes to columns AW or AX or AY. There are no blank cells in the rest
of the area .

If CurrentRegion won't work is there another way?


"JLGWhiz" wrote in message
...
Not if you are going to use the CurrentRegion.Address as the print area
parameters. Excel takes the entire range from top left to bottom right
cells as the print range. You would have to do a little manipulating if
you only want to pring the visible data and I am not sure I have the
expertise to tell you exactly how to do it.


"CR" wrote in message
...
I'm setting a print area using CurrentRegion.

Is there a way to have the current region ignore cells that only have a
formula in them?

Thanks
CR