lr=cells(rows.count,"H").end(xlup).row+1

Should have stopped with this info but it just needed cleaning up

Sub MakeTotals()'try this
'
lr=cells(rows.count,"H").end(xlup).row+1
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet
Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle = xlNone
with workSheets("Report (zonderArb)")
.. Range("H" & lr).formula="=sum(h1:h" & lr & ")"
.. Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
.. Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous
.. Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous
.. Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous
.. Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous
.. Range("AF" & lr).").borders.LineStyle = xlContinuous

.. Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
.. Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
.. Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"

.. Range("G" & LR)= "Totals"

With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

end with

'Sheets("Instructions").Select
' Range("K61").Select
ActiveWorkbook.Save
End Sub



--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
Sorry if I've been difficult. The code is below. The table is comprised of
41 columns of which some contain dates, some text, and some numbers. Plus
at strategic points in the table the columns have borders. Because I do
not know in advance how many rows are involved the borders are done as
part of the macro on a column basis without defining the number of rows.
But then some of the borders have to be removed so that the print looks
okay after the total line has been inserted. In the macro below I have
arbitrarily selected row 460 to be the header row, because I don't know
how to make it an inputbox or, alternative, to get the spreadsheet to
calculate the last row itself. So I have been running the macro, and then
manually deleting blank rows between the last data row and the header row
460. So now I'm trying to do it properly and learn as well !!!

thanks for all of your help, it is appreciated ... Roger






Sub MakeTotals()
'
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
'
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet

Worksheets("Report (zonderArb)").Activate
Rows("460:660").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Report (zonderArb)").Select
Range("H460").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-457]C:R[-1]C)"
Range("H460").Select
Selection.Copy
Range("I460:AE460").Select
ActiveSheet.Paste
Range("A460:G460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("H460:O460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("P460:W460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("X460:AE460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AF460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M460:N460").Select
Selection.NumberFormat = "0.00"
Range("U460:V460").Select
Selection.NumberFormat = "0.00"
Range("AC460:AD460").Select
Selection.NumberFormat = "0.00"
Range("G460").Select
ActiveCell.FormulaR1C1 = "Totaals"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Sheets("Instructions").Select
Range("K61").Select
ActiveWorkbook.Save
End Sub



"Duncan" wrote in message
oups.com...
But you really really should post your code and perhaps we could help
further?, its hard when you cant see the code to suggest the solution.

Duncan


Duncan wrote:

Does this help?

Dim LastRw
LastRw = Range("A1").End(xlDown).Offset(1, 0).Select
'Activecell is now Column A of the Last Row + 1 (blank line)
ActiveCell.Value = "This is the last empty cell in Column A"


Regards

Duncan


Roger wrote:

That's the problem, the spreadsheet (or code) doesn't know which line
is the
last line +1 for the totals so up to now I have been setting the macro
to
put the totals on line 650 irrespective of the number of rows in the
table,
and then (afterwards) manually deleting the blank rows before I print
the
table. I know how to count the rows, and how to input the number using
InputBox, but I don't know how to use it in a line of code which needs
to
refer to a particular cell or several columns (but not all in the
line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the
totals
then excel will already know the row.. Perhaps you should copy/paste
your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There
are
always 14 columns. I have a macro which formats the columns and adds
a
total line to the last line of the table + 1. The columns have
different
formats, so they need to be formatted one by one so using the macro
is
quicker and easier. If I use RowNr = InputBox ("input the row number
for
the totals) in the macro I can get the RowNr into the code, but how
can I
use it, Rownr, to define a particular row or cell, please ?

Roger