Macro: Insert Row above Totals

[D]

I have been reading the many questions and responses on this but have been
unable to determine the best way to modify the code I have. I have recorded a
macro to insert a row with "inside/outside" borders. This is running fine,
however I would like the row to be inserted above the last line, as opposed
to in the same place every time. I have pasted the code I am currently using
below, any advice would be greatly appreciated.

Sub AddNewLine()
Range("A15").Select
Selection.EntireRow.Insert
Range("A15:AG15").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
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub

 

[akphidelt]

Try adding this to the top and remove the first 3 lines from your current code

Dim last_row as Integer

last_row = Range("A65536").End(xlup)

Range("A" & last_row).Select
Selection.EntireRow.Insert
Range("A" & last_row & ":AG" & last_row).Select

 

[D]

Thanks, I am not getting a 'type mismatch' error however. It seems to be
having trouble with the line last_row = Range("A65536").End(xlup)

 

[Sheeloo]

Insert the following line after Range("A15").Select
Range("A15").Offset(Selection.Rows.Count - 1, 0).Select
and run the macro after selecting all rows in the range
If you don't want to maually select then first line in your macro should
select all the rows.
The above should enable to make it work. Essential part is using the Offset
to reach the correct row.

 

[D]

How do I tell it to select all rows?

Insert the following line after Range("A15").Select
Range("A15").Offset(Selection.Rows.Count - 1, 0).Select
and run the macro after selecting all rows in the range
If you don't want to maually select then first line in your macro should
select all the rows.
The above should enable to make it work. Essential part is using the Offset
to reach the correct row.

 

[akphidelt]

Damn forgot to add .row

it should be

last_row = Range("A65536").End(xlup).row

 

[Sheeloo]

Pl. use the following code (replace Sheet4 with the name of your sheet)

Sub AddNewLine()

Range("A15").Offset(Sheet4.UsedRange.Rows.Count - 1, 0).Select
Selection.EntireRow.Insert
Sheet4.UsedRange.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
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
where sheet1 is the name of the sheet containing your data.