Is there any way a macro can be created for various print area selections?

[Steve]

Hi,

I'm looking to see if anyone knows of a way to have a macro select a
print area of a constant width (columns A thru K) but with a variable
length. Our MRP system will dump our BOM's into Excel and all of the
BOM's vary in the number of rows used. Some can be 3 pages while
others will be 15 pages. Currently we have to set up a print area to
capture the information we want printed under columns A thru K
everytime we go to print a BOM.


Please advise,

Steve

 

[Dave Peterson]

If you hit ctrl-end, do you go way past where you think the data should end?

If yes, you could use the techniques at Debra Dalgleish's site to reset that
lastused cell.

Then excel will, er, should be smart enough to stop after that last row. (You
may still want to change the columns to print to A:K.)

http://contextures.com/xlfaqApp.html#Unused

 

[Steve]

I actually have used the macro that is in the link you've suggested to
clean up the file because the file size was way to large. However,
when I hit the ctrl-end key, I'm still taken to cell K2005. Which is
still quite a way's from where my data ends. Before I used the macro
it went to the last row of the spreadsheet. So, I'm almost there....
Any suggestions?

 

[Dave Peterson]

It sounds like you really have data in those cells...

(saved from a previous post)

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<>""),ROW(Sheet1!$A$1:$A$2000))

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

 

[Steve]

Dave,

Thanks for all the help. It took me a while to figure out how to make
this work in a macro so that I can automate everything. I do have one
last question though... This macro I have will now do everything I
need it to do, except I need a command to add at the end of it that
will close the dialogue box for the "Print Preview".



Thanks,

Steve

 

[Dave Peterson]

You might be able to do something by sending an Escape key (with Sendkeys), but
I guess I don't understand why you'd want to.

If you think you have to popup print preview, then how would you ever know when
the user was ready to dismiss that view.

 

[Steve]

Dave,

Thanks again for all your help. Somehow I managed to figure out what
it was in the macro that was causing the "Print Preview" dialogue box
to appear and I just removed it.


Best Regards,

Steve