MrExcel Publishing
Your One Stop for Excel Tips & Solutions

adding rows


Posted by Ian M on August 01, 2001 6:46 AM

is there a way to add a row using an if statement. I want to be able to insert a row if the row above it meets a certain criteria


Posted by Mark W. on August 01, 2001 7:03 AM

Not with an IF() function. Excel functions only
return values to the cells in which they're entered.
Functions and don't take action; however, commands
such as Insert | Rows do take action. If you want
to test a condition and then perform a command
then you'll need to create and use a macro.

Posted by Cory on August 01, 2001 7:11 AM

Sure! Here's one that I use to look at a column of numbers and insert a row where there's a difference. ex:

col A
11
11
11
14
14
2
8
8
8

This code inserts a line after each group of numbers:

Private Sub PutARowIn()
Range("A2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Insert
ActiveCell.Offset(2, 0).Select
End If
Loop
End Sub

Have Fun!

Cory

Posted by Ian M on August 01, 2001 8:14 AM

Thanks alot! just one more question though. is it possible to change your code around so that it can look for a few spacific words and then put a space after it?


Posted by Ian M on August 01, 2001 8:16 AM

oops! i dont mean a space i mean adding a row. : This code inserts a line after each group of numbers

Posted by Cory on August 03, 2001 3:00 PM

Ian, try this... It may be a little buggish, but it worked on my mockup:

Private Sub CommandButton1_Click()
Range("A2").Select
Do Until ActiveCell.Value = Empty
Cells.Find(What:="your specific words", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Offset(1, 0).Value = Empty Then
Exit Sub
End If
ActiveCell.Offset(1, 0).EntireRow.Insert
Loop
Range("A1").Select
End Sub


Cory