Automatically insert blank line

B

bludovico

Is there any way to program excel to look for a change in data in
range of cells and insert a blank line? FOr example, I have a repor
where numbers start with either 600 or 100, to delineate plan
locations. Can I program excel to insert a blank line after the 600'
and before the 100's
 
J

jeff

Hi,

Try some code like this. (assumes 100, 600 in Col A)

Sub InsertLine()
Dim r As Range
Dim c As Variant
Set r = Range("a1:A23")
oldC = ""
For Each c In r
If j > 0 Then
If oldC <> c.Value Then
c.Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
End If
End If
oldC = c.Value
j = j + 1
Next c
End Sub

jeff
 
G

Gord Dibben

Sub InsertRow_At_Change()
Dim I As Long
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For I = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(I - 1, 1) <> Cells(I, 1) Then _
Cells(I, 1).Resize(1, 1).EntireRow.Insert
Next I
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

Gord Dibben Excel MVP
 
D

Dave Peterson

Just a thought--instead of actually inserting a row, you could double the
rowheight for the top cell. Then filters/charts/pivottables (and other stuff)
will still work easily.
 
Top