Insert a Row

M

Matt B

Hi,

Somewhere in column A, a cell contains the text "sales". Within a
macro I want to insert 3 rows above the row that has "sales" in it.

Is this possible?

TIA

Matt
 
F

Frank Kabel

Hi
try the following macro.
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If lcase(Cells(row_index+1, "A").Value) ="sales" Then
Cells(row_index + 1, "A").resize(3,1).EntireRow.Insert _
(xlShiftDown)
End If
Next
End Sub
 
J

JE McGimpsey

One way:

Public Sub InsertAboveSales()
Dim rFound As Range
Set rFound = Columns(1).Find( _
What:="sales", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then _
rFound.Resize(3, 1).EntireRow.Insert
End Sub
 
Top