Page Setup / Print Formatting

**vaximily** · 05-07-2010, 01:42 PM

Ok, been mulling over this for a while and I've gotten close, but here's my initial code:

Some pre-info, the macro's before this combine data from about 15 worksheets down to 2 worksheets and format them just the way I want them to look, except that in the process the print area gets totally hosed up. The worksheets are a set width, but can vary in height from a few dozen rows to hundreds.

Here's the code so far:

Please Login or Register  to view this content.

Here's the problem... I don't want the print area to be set to 200 rows, I want it to be set to the last row in the spreadsheet. Now I know how to find the last row containing data, and select the next row in the spreadsheet (the first empty row) using this code:

Please Login or Register  to view this content.

So, any idea how to dynamically set the print area to the last populated row, instead of a defined 200 rows?

Thanks!

**vaximily** · 05-07-2010, 01:58 PM

Also to add to my thought process, if I could somehow select the last row and 500 rows after it, delete those rows, save the file, and clear the print area, it would automatically set the print area to the last row in the worksheet.

Not sure which method will work better / faster / easier, so I'll wait for you guys to respond =)

**vaximily** · 05-07-2010, 03:59 PM

Any takers? =)

**vaximily** · 05-10-2010, 12:00 PM

3 days and still no response =(. Anybody have any ideas?

**excelname** · 06-08-2010, 11:21 AM

"Here's the problem... I don't want the print area to be set to 200 rows, I want it to be set to the last row in the spreadsheet. Now I know how to find the last row containing data, and select the next row in the spreadsheet (the first empty row) using this code:"

Ok. I'm an absolute newb here, but I used to do something similar in Lotus123 macros, maybe before you were born. I dunno.

Go to the top cell of the range you want to select.
Start macro recording.
SHIFT/END/DOWN ARROW
<click the handy Set Print Area icon on the toolbar>
end recording

What I don't know is whether you can pasted recorded macros into the middle of a your code script or whatever you call it. If you can paste this is, so to speak, seems like it might work. Then again, if it is recordable, musn't there be code that can do the same thing? But I guess that was what you were asking for in the first place.

Just trying to help.

**vaximily** · 04-15-2011, 03:01 AM

Originally Posted by excelname

"Here's the problem... I don't want the print area to be set to 200 rows, I want it to be set to the last row in the spreadsheet. Now I know how to find the last row containing data, and select the next row in the spreadsheet (the first empty row) using this code:"

Ok. I'm an absolute newb here, but I used to do something similar in Lotus123 macros, maybe before you were born. I dunno.

Go to the top cell of the range you want to select.
Start macro recording.
SHIFT/END/DOWN ARROW
<click the handy Set Print Area icon on the toolbar>
end recording

What I don't know is whether you can pasted recorded macros into the middle of a your code script or whatever you call it. If you can paste this is, so to speak, seems like it might work. Then again, if it is recordable, musn't there be code that can do the same thing? But I guess that was what you were asking for in the first place.

Just trying to help.

Apparently I never got notified of a response on this. Thanks so much for the suggestion, I put this on hold and just set the number of rows manually in the macro before I run it now, but I will definitely give this a shot in the morning and report back on my findings.

Thanks! =)