excel macro


Posted by Dennis Hands on September 23, 2001 9:18 PM

I am trying to write a macro that reads a column and for every "YES" I would like it to insert a row. Any ideas?



Posted by George on September 24, 2001 6:09 AM


If you want to insert a row below each "YES" :-

Sub Insert_Row_Below()
Dim col As Range, cell As Range
Set col = Range([A1], [A65536].End(xlUp)) 'change column ref as required
Application.ScreenUpdating = False
For Each cell In col
If cell.Value = "YES" Then cell.Offset(1, 0).EntireRow.Insert
Next
Application.ScreenUpdating = True
End Sub


If you want to insert a row above each "YES" :-

Sub Insert_Row_Above()
Dim col As Range, x As Long, y As Long
Set col = Range([A1], [A65536].End(xlUp)) 'change column ref as required
x = col.Cells.Count
For y = x To 1 Step -1
If Cells(y, 1).Value = "YES" Then Cells(y, 1).EntireRow.Insert
Next
End Sub