Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.printing,microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default Imported data... the incredible growing/shrinking spreadsheet

Hey all,

I'm working on a spreadsheet with several worksheets. Each worksheet
pulls data from a local database when the sheet is opened. I'm having
printing difficulties...

The spreadsheet is configured something like this:
BULK BULK ALFALFA SEEDS OG 06555 1.277 _________

where the columns containing BULK, BULK ALFALFA SEEDS OG, 0655, and
1.277 are from the database, and I've formatted the column to the right
_______ so my stores can use the sheet to take inventories.

The thing is, the length of the list isn't always the same - sometimes
they will pull 150 items, sometimes 500+.

Originally, I'd set up the last column to have cell borders on the
bottom to emulate a blank space. I did this from the top row, down to
row 10,000 to try to cover the maximum data space the table might take
up. I have a feeling that's what's getting me in trouble. When users
print, it dumps 50 pages to the printer instead of the required 5-7.

I know you can specify which pages you wish to print, but my users are
fairly technologically challenged and I cringe to throw extra
instructions their way.

Maybe this is a two-tiered question:

1) Is there a way to make the print area automatically grow/shrink
depending on the length of the sheet? I.e., if the range of cells is
A3..E165 one time, and the next time I refresh data, its A3..E227. A
formula of some kind in the print area under print options? I'm kind of
lost - help would be greatly appreciated.

2) Is there perhaps a better way?

Regards

Ben
  #2   Report Post  
Posted to microsoft.public.excel.printing,microsoft.public.excel.programming
external usenet poster
 
Posts: 4,339
Default Imported data... the incredible growing/shrinking spreadsheet

Hi,
Try this - based on testing columnA for end of data in worksheet
"MyData"

Worksheets("MyData").Activate
ActiveSheet.PageSetup.PrintArea = "$A$1:$e$" & Cells(Rows.Count,
1).End(xlUp).Row


HTH

"Ben Adler" wrote:

Hey all,

I'm working on a spreadsheet with several worksheets. Each worksheet
pulls data from a local database when the sheet is opened. I'm having
printing difficulties...

The spreadsheet is configured something like this:
BULK BULK ALFALFA SEEDS OG 06555 1.277 _________

where the columns containing BULK, BULK ALFALFA SEEDS OG, 0655, and
1.277 are from the database, and I've formatted the column to the right
_______ so my stores can use the sheet to take inventories.

The thing is, the length of the list isn't always the same - sometimes
they will pull 150 items, sometimes 500+.

Originally, I'd set up the last column to have cell borders on the
bottom to emulate a blank space. I did this from the top row, down to
row 10,000 to try to cover the maximum data space the table might take
up. I have a feeling that's what's getting me in trouble. When users
print, it dumps 50 pages to the printer instead of the required 5-7.

I know you can specify which pages you wish to print, but my users are
fairly technologically challenged and I cringe to throw extra
instructions their way.

Maybe this is a two-tiered question:

1) Is there a way to make the print area automatically grow/shrink
depending on the length of the sheet? I.e., if the range of cells is
A3..E165 one time, and the next time I refresh data, its A3..E227. A
formula of some kind in the print area under print options? I'm kind of
lost - help would be greatly appreciated.

2) Is there perhaps a better way?

Regards

Ben

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
The Incredible Shrinking Chart LarryP Excel Discussion (Misc queries) 1 March 29th 10 11:02 PM
scroll automatically as data is imported into a spreadsheet Light Meter Reader Excel Discussion (Misc queries) 1 February 27th 09 12:36 AM
Growing/Shrinking/Selective Chart Data Rob Charts and Charting in Excel 12 November 4th 08 03:32 PM
HOW DO I KEEP DATA (SPREADSHEET EXCEL) FROM SHRINKING WHEN SAVED? DINGY Excel Discussion (Misc queries) 0 October 10th 06 05:47 AM
Pivot table cruft and incredible shrinking workbooks Paul Keenan Excel Programming 0 June 15th 04 03:55 PM


All times are GMT +1. The time now is 03:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"