Insert Row with a macro
 

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

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

"Debi" wrote in message
...
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

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

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

"Debi" wrote in message
...
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

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


"Dave O" wrote in message
oups.com...
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

Thanks, thanks, thanks. This worked beautifully!

Debi

"Bernie Deitrick" wrote:

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

"Debi" wrote in message
...
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