Inserting a row based on a condition

[Andries]

Hi

I want to insert a row if a certain condition is met...for e.g. I want to insert a row between "CLOSED" and ""O/B"

Thank you

Excel Workbook
	G	H	I
16	Code	Open/Closed	data
17	206	CLOSED	32
18	206	CLOSED	39
19	*	CLOSED	3
20	*	CLOSED	8
21	*	O/B	48
22	252	O/B	28
23	252	O/B	124
REQUEST FORM

 

[Manohar M G]

Hi....
Check this out...!!!!


Private Sub CommandButton1_Click()
R1 = 2
Do While ActiveSheet.Cells(R1, 2).Value <> ""
Closed_O_B = ActiveSheet.Cells(R1, 2).Value
If Closed_O_B = "CLOSED" Or Closed_O_B = "O/B" Then
ActiveSheet.Rows(R1 + 1).Select
Selection.Insert Shift:=x1Down, CopyOrigin:=xlFormatFromLeftOrAbove
R1 = R1 + 1
End If
R1 = R1 + 1
Loop
End Sub

 

[Peter_SSs]

I want to insert a row if a certain condition is met...for e.g. I want to insert a row between "CLOSED" and ""O/B"

I suspect that we shouldn't need to loop through every row. A bit more detail about what the data is like, or could be like, would help get an efficient answer.

If we find the first occurrance of "O/B", can we just add a row above that?

Or do we need to check that the cell above contains "CLOSED" as well?

Could your columns look like this?
CLOSED
OPEN
O/B
CLOSED
O/B

or this?
OPEN
OPEN
O/B
CLOSED
O/B

etc

 

[Andries]

Hi Please explain this code I do not understand...I am a beginner

thx

 

[Peter_SSs]

Dries

Perhaps you missed my questions but for your sample data, it could be a one-pass code like this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> InsertRow()<br>    <SPAN style="color:#00007F">Dim</SPAN> OB <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Range("H16", Range("H" & Rows.Count).End(xlUp))<br>        <SPAN style="color:#00007F">Set</SPAN> OB = .Find(What:="O/B", After:=.Cells(1, 1), LookIn:= _<br>            xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, _<br>            MatchCase:=False, SearchFormat:=False)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> OB <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        OB.EntireRow.Insert<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>