Insert Row with a macro

[Guest]

When I use the following code in an Excel macro to insert a row, it does not
work correctly. Instead of inserting just one row above the TOTAL EXPENSES
row, rows will continue to be inserted until the worksheet runs out of rows.
Could someone please help me.

For Each c In [A1:A150]
If c Like "TOTAL EXPENSES" Then
c.Activate
ActiveCell.EntireRow.Insert
End If
Next

I have to use the "TOTAL EXPENSES" instead of the row number because this is
a report I import from another program and the row number that TOTAL EXPENSES
appears in varies from report to report depending on how many expenses are in
each report.
Thanks

 

[Bernie Deitrick]

Debi,

Step up through the rows from the bottom:

For myRow = 150 To 1 Step -1
If Cells(myRow, 1).Value Like "TOTAL EXPENSES" Then
Cells(myRow, 1).EntireRow.Insert
End If
Next myRow

HTH,
Bernie
MS Excel MVP

 

[Dave O]

Alternatively, since the report varies in length, you could enter the
word "stop" below the last row of the report and run that follows.
That will avoid the step of amending the code each time you run the
report.

Sub InsertRow()
Range("a1").Select
Do Until ActiveCell.Value = "stop"
If ActiveCell.Value = "TOTAL EXPENSES" Then
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If

ActiveCell.Offset(1, 0).Select
Loop

End Sub

 

[Bernie Deitrick]

Debi,

I re-read your message, and if there is only one line with TOTAL EXPENSES,
then you can use the find technique:

Columns("A:A").Find(What:="TOTAL EXPENSES").EntireRow.Insert

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Dave,

You could also simply insert an Exit Sub line in the If Then statement: for
your code -

If ActiveCell.Value = "TOTAL EXPENSES" Then
Selection.EntireRow.Insert
Exit Sub
End If

For the OP's code:

For Each c In [A1:A150]
If c Like "TOTAL EXPENSES" Then
c.EntireRow.Insert
Exit Sub
End If
Next

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, thanks, thanks. This worked beautifully!

Debi

Debi,

I re-read your message, and if there is only one line with TOTAL EXPENSES,
then you can use the find technique:

Columns("A:A").Find(What:="TOTAL EXPENSES").EntireRow.Insert

HTH,
Bernie
MS Excel MVP

When I use the following code in an Excel macro to insert a row, it does not
work correctly. Instead of inserting just one row above the TOTAL EXPENSES
row, rows will continue to be inserted until the worksheet runs out of rows.
Could someone please help me.

For Each c In [A1:A150]
If c Like "TOTAL EXPENSES" Then
c.Activate
ActiveCell.EntireRow.Insert
End If
Next

I have to use the "TOTAL EXPENSES" instead of the row number because this is
a report I import from another program and the row number that TOTAL EXPENSES
appears in varies from report to report depending on how many expenses are in
each report.
Thanks