Adding a blank row between values

[Rick]

I would like to be able to automate the row adds.

rows 1-3 are alike, rows 4-9 are alike, row 10 is alone, rows 11-15 are
alike. How do I add blank rows between these groups of varying values without
selecting the top row in each value and performing an insert row?

some of these sheets have hundreds of values.

i.e. what i have:

001
001
002
002
002
003
003
003
003

What I want:
001
001

002
002
002

003
003
003
003

etc...

thanks in advance
Rick

 

[IanC]

Sub test()

c = 1 ' for column A
For r = 1000 To 2 Step -1 ' for data starting at row 1 and ending at row
1000
If Cells(r, c).Value <> Cells(r - 1, c).Value Then
Rows(r).Insert
r = r - 1
End If
Next r

End Sub

 

[Dave Peterson]

Excel has a feature data|subtotals that does close to what you want. It also
provides the ability to sum/count (some kind of summary) for fields in that
group.

If you've tried that and don't like it, ...

Just a suggestion, I wouldn't insert an empty row between the groups. It can
make other things more difficult to do
(charting/sorting/filtering/pivottabling(?)).

I'd just increase the rowheight so that the next group looks double spaced.

Option Explicit
Sub testme01()
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
For iRow = LastRow To 2 Step -1
If .Cells(iRow, "a").Value <> .Cells(iRow - 1, "a").Value Then
.Rows(iRow).RowHeight = .Rows(iRow).RowHeight * 2
'.Rows(iRow).Insert
End If
Next iRow
End With
End Sub

If you really want to insert the row, then delete the .rowheight line and use
the .insert line (remove the apostrophe that makes it a comment.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)