Seperating Rows

  • Thread starter Thread starter Kevin G
  • Start date Start date
K

Kevin G

I have an array of data. I want to insert lines in
between rows where the value in column "A" changes. For
instance, the value in cell A1 through A10 is 522, then
A11 goes to 524. I want to insert a blank row to seperate
the two. The code that I wrote to attempt that failed
miserably. Not sure where I went wrong, but here's what I
did:

Sub sepLATAs()
Range("a3:a150").Select
Dim acell As Range

For Each acell In Selection

If acell.Value <> acell.Offset(-1, 0) Then
acell.EntireRow.Insert
On Error Resume Next
End If
Next acell

End Sub

Any help would be appreciated.

Thanks,

Kevin G
 
Kevin,

Try code like the following:

Dim RowNdx As Long
For RowNdx = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(RowNdx, "A") <> Cells(RowNdx - 1, "A") Then
Rows(RowNdx).EntireRow.Insert
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Kevin,

Not sure if this is of interest to you, but Excel has a SubTotal function
under the Data menu. This will separate your groups and give you subtotals.

Once you get used to using it, you can record and modify a macro to do it
automatically.

steve
 
Back
Top