Before I use a macro to insert totals into an invoice I need to remove the
continuation borders of the template otherwise the macro assumes that the end
of the border is the end of the invoice. Column A has the items, B the
quantity, C the unit price, and D the total. The invoices can run from A1 to
A600 being different each time. Can anyone please help?

How about a couple of alternatives...

#1. Apply data|filter|autofilter to one of your columns.
Filter to show only those non-blank cells
print the sheet
data|filter|show all

#2. Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

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.)

Then once mo
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).

J.Reynolds wrote:

Before I use a macro to insert totals into an invoice I need to remove the
continuation borders of the template otherwise the macro assumes that the end
of the border is the end of the invoice. Column A has the items, B the
quantity, C the unit price, and D the total. The invoices can run from A1 to
A600 being different each time. Can anyone please help?

--

Dave Peterson

Hi Dave,
Many thanks for your help, #1. works fine, since at the end of the worksheet
I have included some rows that hold the subtotal, tax, and total. So
filtering brings the Total box up to the last item.

"Dave Peterson" wrote:

How about a couple of alternatives...

#1. Apply data|filter|autofilter to one of your columns.
Filter to show only those non-blank cells
print the sheet
data|filter|show all

#2. Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

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.)

Then once mo
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).

J.Reynolds wrote:

Before I use a macro to insert totals into an invoice I need to remove the
continuation borders of the template otherwise the macro assumes that the end
of the border is the end of the invoice. Column A has the items, B the
quantity, C the unit price, and D the total. The invoices can run from A1 to
A600 being different each time. Can anyone please help?

--

Dave Peterson

I like to put those subtotals at the top (rows 1, 2, ...)

Then they're always visible, too (well, when I window|freeze panes nicely.)

J.Reynolds wrote:

Hi Dave,
Many thanks for your help, #1. works fine, since at the end of the worksheet
I have included some rows that hold the subtotal, tax, and total. So
filtering brings the Total box up to the last item.

"Dave Peterson" wrote:

How about a couple of alternatives...

#1. Apply data|filter|autofilter to one of your columns.
Filter to show only those non-blank cells
print the sheet
data|filter|show all

#2. Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

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.)

Then once mo
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).

J.Reynolds wrote:

Before I use a macro to insert totals into an invoice I need to remove the
continuation borders of the template otherwise the macro assumes that the end
of the border is the end of the invoice. Column A has the items, B the
quantity, C the unit price, and D the total. The invoices can run from A1 to
A600 being different each time. Can anyone please help?

--

Dave Peterson


--

Dave Peterson