13 X 545 Worksheet?

[dreing]

Is there any way to cut the size of the worksheet in excel to a specific size such 13 Columns by 545 rows? I am not an experienced programer but, I am trying to idiot proof a sheet that I will be sharing with others who are evn less competent than myself.


The second question is: Is it possible to have excel search for the first empty cell in a column, and set the print area to end there?

Basically, I have a pricing program for items whose prices change daily. My sheet is designed to allow them to enter 500 or so items onto a single order sheet. Since I have it programmed to calculate prices, lines that conatin no data, also have $0.00 in the computation fields. So when I ask to print, it gives me to the last cell of the defined area even though there are no entries for 100 rows prior. I don't know if I am making sense, but I want the end user to be able to click print and have excel print everything up to and including their last data entry, but no more.

[shg]

Welcome to the forum.

You can hide the unneeded rows.

[teylyn]

Hi,

You can select all columns to the right of your table and hide them Format - Columns - Hide. Same goes for rows. Select all rows below your table and go Format - Rows - Hide.

Then select the table and make it the print area. Then go to Insert - Name - Define, select the name Print_Area and change the "Refers to" to this:

=OFFSET(Sheet1!$A$1:$F$1,0,0,COUNTA(Sheet1!$A:$A))

This will set the print area to only the number of rows that have a value in column A. Careful, do not leave blank rows between data rows, because that will mess up the print area.

hope that helps

[dreing]

Ok I was having a brain cramp...yes hide the rows...duh.

Now the other problem.... Using 2007. Did the define name like you said but I needed to use column B for the test. I changed all the "A"s to "B" s but not working. Can you parse for me? This would save me time.

I also have one more issue: I have the sheet configured with a locked horizontal split (top portion appears on all pages) when you it the up button from a cell from the first line after the split it jumos to the end of the sheet since I only have three rows of cells where the user can make inputs. Any way to make it stop when it gets to the bottom or top?

[teylyn]

OK

13 columns starting with column A and counting the entries in Column B will be the formula

=OFFSET(Sheet1!$A$1:$M$1,0,0,COUNTA(Sheet1!$B:$B))

As to the other part with the split, I don't have 2007 in front of me, so, can't really say.

[dreing]

yes that works BUT.... Top section has merged cells can we start the count at B14? I tried to tinker with it, but it didn't work.

I appreciate all the help thus far. And that scrolling issue is really annoying and it is the one really gaping whole in the idiot proofing.

[dreing]

I figured it out...Thanks for your help... You are AWESOME!!! Can I trouble you with 1 more?

Any way to (going along with the work book metaphor) tear a sheet from the "workbook" and with a click send it via email to a designated address? Or is that too complicated for someone like me. I know where to put VB code, just have no idea how to write it. Haven't written code since my apple II plus days.

[teylyn]

I'm only dabbling in VBA, not very good at it. I think this might warrant a new post in the Programming section of the forum. If it can be done, someone there will point you in the right direction. Just keep the description to the "email a worksheet" part.

[dreing]

Thanks a million you guys rock!!!!