Add row after column of duplicate info

D

Debe

I want to know if there is a way to have Excel
automatically insert a blank row after a column if the
number in the column changes.

Example:
Column B
110011
110011
110011
110345
110347
110347

Would like for it to automatically insert as below:

110011
110011
110011
Insert blank row here
110345
Insert blank row here
110347
110347
Insert blank row here

Can anyone help with this? It would be greatly
appreciated :)

Debe
 
D

Dave Peterson

Data|subtotals can add a line with subtotals between cells with common values.

You can use the outline symbols to show just the subtotal line,
select those visible rows
hit F5 (edit|goto, ctrl-g)
click special
hit the delete key to clear the subtotals
tools|options|view|uncheck outline symbols.

But I don't like putting blank rows into my data. But adjusting the rowheight
to be double gives that same effect and makes other stuff easier
(sorts/pivottables/charts).

But here's macro that will insert that blank row.

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With ActiveSheet
FirstRow = 1
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow - 1, "B").Value = .Cells(iRow, "B").Value Then
'do nothing
Else
.Rows(iRow).Insert
'With .Rows(iRow)
' .RowHeight = .RowHeight * 2
'End With
End If
Next iRow
End With
End Sub

You can comment (.rows(irow).insert) and uncomment the with/end with block to
make it double the rowheight.
 
Top