MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert Row


Posted by Andonny on December 21, 2000 2:29 AM

Hi,
I would like to insert a row if there is no blank cell underneath JE and also place an XX on culumn B into the new inserted row. There could be many or only one of those JE which need a row inserted underneath.

A B
1 JE 222
2 XX
3 RI 444
4 XX
5 JE 222
6 JE 222
7 XX
8 RI 444
9 XX

RESULT
A B
1 JE 222
2 XX
3 RI 444
4 XX
5 JE 222
6 XX
7 JE 222
8 XX
9 RI 444
10 XX

Thank you very much for your help
Andonny


Posted by Andonny on December 21, 2000 3:21 AM

Hi,
I keep forgetting that a sample at my webpage might be a bit better:
http://www.multiline.com.au/~wje/row.html

Thanks
Andonny

Posted by JohnLat on December 21, 2000 8:53 AM

Andonny, try this

Public Sub InsertStuff()

Dim InsertingRange As Integer
Dim Counter As Integer

InsertingRange = ActiveSheet.UsedRange.Rows.Count

For Counter = 1 To InsertingRange
If ActiveCell = "JE" Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(0, 1) = "XX"
End If
If ActiveCell <> "JE" Then
ActiveCell.Offset(1, 0).Select
End If
Next Counter

End Sub