How can I execute my macro for every row in worksheet

S

StateOfMichigan

I'm a macro newbie...

In my worksheet, I want to use a macro to find the word "RETENTION" and
insert a blank (new) row after this word until all the rows in the worksheet
are processed.

Any suggestions? Thanks.
 
F

Frank Kabel

Hi
try the following macro. It tests column A and inserts a row if
the value is 'RETENTION'
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 Cells(row_index, "A").Value ="RETENTION" then
Cells(row_index + 1, "A").EntireRow.Insert (xlShiftDown)
End If
Next
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
news:[email protected]...
 
S

StateOfMichigan

Thanks very much, Frank. This works for me ;)

Frank Kabel said:
Hi
try the following macro. It tests column A and inserts a row if
the value is 'RETENTION'
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 Cells(row_index, "A").Value ="RETENTION" then
Cells(row_index + 1, "A").EntireRow.Insert (xlShiftDown)
End If
Next
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

im Newsbeitrag
Click to expand...
 
Top