macro - find two items, insert row

[psrs0810]

Is there a way to find two items in different columns, select that row and insert.

So in column B I am looking for "A Total". In column D I am looking for "M". where those two are on the same row to insert a row.

How can this be achieved without out concatenating the information?

Thank you

 

[Marc]

This should do the trick.

Sub InsertRow()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual

'Loop backwards through the rows in the range that you want to evaluate.

Set ws = ActiveSheet
For i = 1 To Cells.SpecialCells(xlLastCell).Row
If ws.Cells(i, 2).Value = "A Total" And _
ws.Cells(i, 4).Value = "M" Then
ws.Cells(i, 1).EntireRow.Insert
i = i + 1
End If
Next
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[psrs0810]

Perfect!

 

[psrs0810]

quick question - what if I wanted to insert two rows?

thanks

 

[Marc]

My initial code did not account for the additional rows added.

This should work better and insert two rows as it starts at the bottom of the existing data.

Sub InsertRow()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual

'Loop backwards through the rows in the range that you want to evaluate.

Set ws = ActiveSheet
For i = Cells.SpecialCells(xlLastCell).Row To 1 Step -1
If ws.Cells(i, 2).Value = "A Total" And _
ws.Cells(i, 4).Value = "M" Then
ws.Cells(i, 1).EntireRow.Insert
ws.Cells(i, 1).EntireRow.Insert
End If
Next
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub