Removing column borders from an invoice

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 more:
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 more:
> 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 more:
> > 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