Set print range

[sandra]

I want a macro to set a print range and then print it.
The number of columns and rows would be based on what is
the last row with text in it and what is the last column
with text in it.
Is there a way to do this without using Visual Basic, for
I know nothing of that?

 

[Guest]

If you delete the name "Print_Area", then what gets printed should be exactly what you're describing.

 

[Sandra]

I didn't ask my question clearly. In sheet 2, the cells
in column A are "+sheet1!a1", "+sheet2!a2", etc. In
other cells there are formulas: Choose(sheet1!
b1,"trained","untrained") If something is entered in
sheet 1, it appears in sheet 2 (as text). I have
formulas over many cells in sheet 2 to allow for a few
entries, or many entries, in sheet 1. I want to print
only the cells in Sheet 2 that have entries in sheet 1.

Thank you for replying.

-----Original Message-----
If you delete the name "Print_Area", then what gets

printed should be exactly what you're describing.

 

[Tom Ogilvy]

You will have to hide rows in the print area which you don't want printed.
Since you are in programming, you recognize that you need to use a macro to
hide rows.

 

[Sandra]

But one user will have maybe 150 rows with data to print.
Another will have 5 rows. I don't want the one with 5
rows to have 2 or 3 blank pages printing after his rows
of data. Is there no way to set the print area according
to whether a cell has text in it, or just a formula?
Thanks!

 

[Tom Ogilvy]

It might be useful to use the before print event to identify what to print.

 

[mudraker]

Try this code

Macro assumes row 1 has an entry in the last column and column A has a
entry in the last row of the area you wish to print


Sub SetPrintArea()
Dim lRow As Long
Dim iCol As Integer

iCol = Sheets("sheet1").Range("iv1").End(xlToLeft).Column
lRow = Sheets("sheet1").Range("a65536").End(xlUp).Row

Sheets("sheet2").PageSetup.PrintArea = "a1:" & Cells(lRow
iCol).Address
Sheets("sheet2").PrintOut Copies:=1, Collate:=True
Sheets("sheet2").PageSetup.PrintArea = Cells
End Su

 

[Sandra]

I'm afraid I'm very ignorant. What is a "before print
event"?
Thanks

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/events.htm
for an introduction into event procedures