Add a blank row

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

Robert Gillard

I have a worksheet (that starts at c17) and I need to add a blank row, 3
rows up from the bottom row (last row is total, then blank, then line the
new blank row is to go above this line) I would like to use a macro to do
this but each month the bottom row is in a different position.

Can anybody tell me how I can do this

with thanks

Bob
 
Hi Robert
I have a macro cobbled togaether from this group, which seems to do exactly
what you want.
(Take note that it serches for the word "totals" in this case.
It will insert rows (you choose) 2 rows above the totals row, and also all
formulas will be dragged down into the newly inserted rows.
It works great for me, though you may need to tinker. Maybe alter in which
column your "totals" cell is to appear.
Remember to save as regular macro not in this ca
Good luck

Sub Insert_Rows()

' InsertRowsAboveTotals()
' InsertRowsAndFillFormulas(Optional vRows As Long)
' Dim vRows As Integer
' Dim irow As Long, i As Long
' row selection based on "Totals" cell
Columns("B:B").Find(what:="total", after:=Range("B2"), LookIn:=xlValues,
_
lookAt:=xlPart, SearchOrder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=False).Offset(-2, 0).Activate
ActiveCell.EntireRow.Select
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"Enter Number Of Rows To Insert." & vbNewLine & _
"Or 'OK' For Default 10 Rows." & vbNewLine & _
"Or 'Cancel'.", _
Title:="Add Rows", Default:="")
If vRows = False Then Exit Sub
End If
If vRows = "" Then
vRows = 10
End If
ActiveSheet.Select
Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next
'to handle no constants in range to remove the non-formulas
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
irow = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Activate



Cheers
Mathew
End Sub
 
One way:

Select the Total Row, and give it a name. The use a macros similar to this:

Sub InsertNewLine()
Range("TotalLine").Offset(-2).EntireRow.Insert xlShiftDown
End Sub

HTH

-Dave
 
Back
Top