MrExcel Publishing
Your One Stop for Excel Tips & Solutions

inserting a row -with a twist? help


Posted by George A. on May 16, 2001 11:56 AM

i have looked at this topic and it is close I just
can't figure it out for my problem.

13820.html

I am trying to insert AND copy the last similar row
(based on only a couple of data points) in a group
(kinda like a subtotal).

A B C D E
Dog Big Black 40lbs $100
Dog Big Black 50lbs $120
Dog Big Black 80lbs $100
Dog Big Gray

I would want a 4th row of Dog Big Black inserted before
the Dog Big Gray and only copying the first three cells
(Dog Big Black). I don't need the cells after column C.

And if possible to make the row bold.

thanks so much


Posted by Brian P on May 16, 2001 3:24 PM


George

see if this works

Sub trythis()
Range("a2").Select
x = 2
MyCell = Cells(x, 1).Value & Cells(x, 2).Value & Cells(x, 3).Value

For x = 2 To 100
MynextCell = Cells(x + 1, 1).Value & Cells(x + 1, 2).Value & Cells(x + 1, 3).Value
If MyCell <> MynextCell Then
Cells(x + 1, 1).EntireRow.Insert
Range(Cells(x, 1), Cells(x, 3)).Copy Cells(x + 1, 1)
Range(Cells(x + 1, 1), Cells(x + 1, 3)).Font.Bold = True
MyCell = MynextCell
Else
End If

Next x

End Sub