ExcelBanter - Insert a row

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Insert a row (https://www.excelbanter.com/excel-discussion-misc-queries/247243-insert-row.html)

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.

DataSort 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

On Mon, 2 Nov 2009 10:50:07 -0800, da wrote:

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

In a helper column number number 1 through 30 twice, then sort on the
helper column.

da wrote:

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

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

"da" wrote:

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