Insert a row

  • Thread starter Thread starter da
  • Start date Start date
D

da

Hi
Is there a way to insert a blank row after each date?
Thanks

2-Jul
3-Jul
4-Jul
5-Jul
6-Jul
7-Jul
8-Jul
9-Jul
10-Jul
11-Jul
12-Jul
13-Jul
14-Jul
15-Jul
16-Jul
17-Jul
18-Jul
19-Jul
20-Jul
21-Jul
22-Jul
23-Jul
24-Jul
25-Jul
26-Jul
27-Jul
28-Jul
29-Jul
30-Jul
31-Jul
 
Why would you want to?

Will make sorting and filtering etc. extremely difficult.

If just for appearance, simply double the row heights.

But................manual method.

In B1 enter 1

Right-click and drag down to Jul 31, release and "Fill series"

Copy those cells and paste below the ones you just filled.

Data>Sort on column B

Delete column B.

VBA method.

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value <> Cells(X - 1, 1).Value Then
If Cells(X, 1).Value <> "" Then
If Cells(X - 1, 1).Value <> "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
Hi,

Right click your sheet tab, view code and paste the code below in. Change
the column to the desired one and run the code

Sub insertrow()
MyColumn = "A"
For X = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
Rows(X).Insert
Next X
End Sub

Mike
 
Back
Top