Excel macro to insert rows if a cell does not equal the value above that cell

[JSD]

I have a spreadsheet listing columns of information for many different
people. If more than one row exists for the same person, I'd like to
insert 2 rows after the row so that the group is separated from the
next group. For example, here's how the data appears now:

LastName Account# Balance
Martin 1 500.00
Martin 2 750.00
Smith 5 100.00
Thomas 9 900.00

Here's what I'd like it to look like after running the macro:

LastName Account# Balance
Martin 1 500.00
Martin 2 750.00


Smith 5 100.00


Thomas 9 900.00

Any thoughts?

Thanks a bunch for the help!

 

[Bob Umlas Excel MVP]

If you want to SEE extra space (rather than actually HAVE
2 blank rows), then changing the row heights for Smith and
Thomas would do the trick, and this can be done easily
with formulas. In an unused column, say in G3, enter:
=IF(G2<>G3,1,true) and fill down as far as necessary.
Then select column G, use Edit/Goto Special, select
Formulas and DESelect text,logical,and Errors (leaving
only numbers), then click OK. Now, all the 1's are
selected. With this selection, go to Format/Row/Height,
and change the height to 30, then clear (or hide) column G.

 

[Bernie Deitrick]

JSD,

In the macro below, change the myCol = 3 to the column number that you want
to base the insertion on. A=1 , B=2, etc.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRow As Long
Dim myCount As Long
Dim myCol As Integer

myCol = 3
myCount = ActiveSheet.UsedRange.Rows.Count

For myRow = myCount - 1 To 1 Step -1
If Cells(myRow, myCol).Value <> Cells(myRow + 1, myCol).Value Then
Range(Cells(myRow + 1, myCol), Cells(myRow + 2, myCol)).EntireRow.Insert
End If
Next myRow
End Sub

 

[Myrna Larson]

I'd just like to add that this solution is the one I prefer. Adding blank rows
can create all sorts of headaches with formulas, etc.