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

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