when the column text is different, how to insert one row below eac

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with column, Date, Name, Amount. only a few columns, but
a few thousand rows with same Name, different Date and different Amount.
Sample below:
Date Name Amount
4/1/07 John 100
4/1/07 Michael 200
4/2/07 John 110
4/2/07 Michael 220
4/4/07 John 130
4/4/07 Michael 230
First I will sort the above by Name then by Date, then I insert one blank
row just below John.
When I have a hundred different Names, How to automatically insert one blank
row when the Name is different?
 
-First sort the above by Name then by Date
-Create a helper column in (column c)

In C2: =COUNTIF($B$3:B3,B3)=1
Copy all the way down
AutoFilter all the TRUE >> select all the filter rows using the row numbers
 
Sub InsertRow_At_Change()
Dim i As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 2) <> Cells(i, 2) Then _
Cells(i, 2).Resize(1, 2).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord Dibben MS Excel MVP


On Mon, 23 Apr 2007 15:02:04 -0700, Greensky Young <Greensky
 
Thanks so much. It works. If the name, John only appears once. John will not
separate itself.

Thanks again.
 
Thanks.
Please tell me how to use this "Sub" in my spreadsheet.
Is this a macro?
Thanks again.
 
Back
Top