using formuals

[hshayhorn]

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??

 

[BobT]

Assuming you can base the print area selection on another column (e.g. sure
column A has 200 values but column B will always - and only - have a value
for each row we want to print), then you can set up a "Set_My_Print_Area"
macro like this:

Sub Set_My_Print_Area()

Dim StartCell As String
StartCell = "A1" 'Whatever the upper left corner of the page range will be.
Range("B1").Select 'the column I mentioned earlier that we can use as our
test.
'Find the last row
Selection.End(xlDown).Select

'Now set the print area. Change the "G" to whatever the last column is you
want to print.
ActiveSheet.PageSetup.PrintArea = StartCell & ":" & "G" & ActiveCell.Row

End Sub

 

[JLGWhiz]

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]

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]

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
File>PageSetUp>Options>Main 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.

 

[JLGWhiz]

P.S. You have to run the code for it to work. In Excel, from the menu bar,
Click Tools>Macro>Macros then click the name of the macro to run, when it
appears in the small window at the top of the dialog box, click Run.

 

[JLGWhiz]

Out of curiosity, have you tried manually setting the print area by selecting
the area you want to print and then File>PrintArea>Set Print Area ?

 

[hshayhorn]

That actually works great except that there is more an 1 column in the
worksheet that needs to be printed. I need all the columns in that row.
Column P is my last column on that sheet.