The code goes in the standard module1 in the VB editor. If you want it to
print immediately after setting the print area then use this modified code
below.

You can tell if the code is effective by the dotted line that outlines the
print area.
Note that it uses ActiveSheet as the Worksheet object. Be sure that the
sheet you want to print from is the active sheet when you run the code.

Sub prtArea()
Dim lr As Long, bRng As String
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, 1) < "" Then
bRng = Cells(i, 1).Address
Exit For
End If
Next
ActiveSheet.PageSetup.PrintArea = "A1:" & bRng
ActiveSheet.PrintOut
End Sub

To avoid printing unnecessary pages while testing this, use the
FilePageSetUpOptionsMain and in the lower right area of the dialog box,
check the Print Preview. That will allow you to see what is about to be
printed before it actually prints and if you do not want to continue the
action, you can cancel without printing anything. Then when you are
satisfied with the procedure, you can go back and uncheck the print preview.








"hshayhorn" wrote:

Where do I paste the code? I pasted it on the worksheet but it doesn't seem
to work. I still get more pages than I think I should. If I do a control down
arrow on column A there is only 70 rows of data but I still get 12 pages when
I print.

"JLGWhiz" wrote:

This will set your print area for the visible values of column A only. If
you want more columns to print, it will require modification. It will adjust
according to only the cells that show values.

Sub prtArea()
Dim lr As Long, bRng As String
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -1
If Cells(i, 1) < "" Then
bRng = Cells(i, 1).Address
Exit For
End If
Next
ActiveSheet.PageSetup.PrintArea = "A1:" & bRng
End Sub




"hshayhorn" wrote:

I have a repeating IF statement running down column A for 200 rows. I'm
making an assumption that I will not need more than 200 rows ever but I have
no idea how many rows I may need. My issue is thta because of the 200 rows
being used by the formula when I print the worksheet it prints all 200 rows
now just the rows that have real data. If there a way besides having the user
set the print area to only print rows with real data??