How to automatically insert multiple rows & insert dates into new rows

bcragg · Apr 20, 2011

I saved a screen print on google docs that illustrates my question. Below is the link. I couldn't figure out how to upload an image.

 https://docs.google.com/leaf?id=0B7vQ46Z5w-oyMjFmNjlkY2YtODZhYy00MjFmLTgxMGItNTg2OWY2NGZhNjkx&hl=en

Thanks for your help!

VoG · Apr 20, 2011

Try like this

Code:

Sub InsDate()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 4 Step -1
    If Range("E" & i).Value <> Range("E" & i - 1).Value + 1 Then
        Rows(i).Resize(Range("E" & i).Value - Range("E" & i - 1).Value - 1).Insert
    End If
Next i
LR = Range("E" & Rows.Count).End(xlUp).Row
With Range("E3:E" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"
    .Value = .Value
End With
End Sub

Rick Rothstein · Apr 20, 2011

While I would probably go with Peter's (VoG's) solution, I thought you might find it interesting to see an alternative method to do what you want...

Code:

Sub FillDates()
  Dim LastRow As Long, PreviousDataRow As Long, R As Range
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  On Error Resume Next
  For Each R In Range(Rows(StartRow + 1), Rows(LastRow)).Rows
    PreviousDataRow = R.Row - 1
    R.Resize(Cells(R.Row, "E").Value - Cells(R.Row - 1, "E").Value - 1).Insert
    If Err.Number Then Exit For
    Range(Cells(PreviousDataRow, "E"), Cells(R.Row - 1, "E")).DataSeries Type:=xlChronological
  Next
  On Error GoTo 0
End Sub

bcragg · Apr 20, 2011

Thanks Peter & Rick... How do I get activate that "code" into my spreadsheet?

I get the sense that I'm in over my head a bit.

VoG · Apr 20, 2011

Press ALT + F11 to open the Visual Basic Editor. Select Module from the Insert menu. Copy the code and paste into the white space on the right. Press ALT + Q to close the code window.

Press ALT + F8, click on the name of the macro that you wish to run then click the Run button.

Rick Rothstein · Apr 20, 2011

bcragg said:
How do I get activate that "code" into my spreadsheet?

From any worksheet, press ALT+F11 to go into the VB editor. Once there, click Insert/Module from its menu bar and copy paste whichever code you decide to use into the code window that opened up. Now, go to the worksheet with the data on it that you want to insert rows between, then press ALT+F8 to bring up the macro dialog box. Find the name of the code you inserted earlier (found on the first line of the code... either InsDate or FillDates) and select it, then click the Run button.

VoG · Apr 20, 2011

There's an echo in here

bcragg · Apr 20, 2011

I tried that, but nothing actually happens when I click run.
I went into 'Macro Security' and selected Enable All, and still nothing happens

VoG · Apr 20, 2011

Before my code

Excel Workbook

A

B

C

D

E

3

12/04/2011

4

20/04/2011

5

21/04/2011

6

30/04/2011

Sheet12

And after

Excel Workbook

A

B

C

D

E

3

12/04/2011

4

13/04/2011

5

14/04/2011

6

15/04/2011

7

16/04/2011

8

17/04/2011

9

18/04/2011

10

19/04/2011

11

20/04/2011

12

21/04/2011

13

22/04/2011

14

23/04/2011

15

24/04/2011

16

25/04/2011

17

26/04/2011

18

27/04/2011

19

28/04/2011

20

29/04/2011

21

30/04/2011

Sheet12

Maybe your dates are not in column E?

bcragg · Apr 20, 2011

Peter,
You're right, my problem was that I had a hidden column E.

However, when I run 'InsDate' it prompts me to Debug this line for some reason.
Rows(i).Resize(Range("E" & i).Value - Range("E" & i - 1).Value - 1).Insert

When I run Rick's code it works, but only for the first 2 rows of original data, it doesn't work for the entire contiguous sheet.

I really appreciate you guys helping me out, and so quickly!

How to automatically insert multiple rows & insert dates into new rows

bcragg

New Member

Excel Facts

VoG

Legend

Rick Rothstein

MrExcel MVP

bcragg

New Member

VoG

Legend

Rick Rothstein

MrExcel MVP

VoG

Legend

bcragg

New Member

VoG

Legend

bcragg

New Member

Similar threads

Forum statistics

Share this page

How to automatically insert multiple rows & insert dates into new rows

New Member

Excel Facts

Legend

MrExcel MVP

New Member

Legend

MrExcel MVP

Legend

New Member

Legend

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock