Macro to insert Lines

Microsoft Excel 2003 (full)
December 13, 2009 at 23:59:04
Specs: Windows XP
I have a spreadsheet that I xtract on a daily basis. But someitmes there is no data for a given day so my spreadsheet will show only the dates that have data. Eg. 10/12/2009, 11/12/2009, 15/12/2009. I would like to insert rows for the missing dates so that the entire spreadsheet will be squencial. Can anyone help?

See More: Macro to insert Lines

Report •

December 14, 2009 at 07:42:51

Here is a macro that will insert rows for missing dates.

It assumes that dates are in an Excel recognized date format.

Enter the following in a standard module:

Option Explicit

Private Sub MakeRows()
Dim lngRow As Long
Dim intCol As Integer
Dim lngRowCount As Long
Dim intIns As Integer
Dim m As Integer
Dim n As Long

On Error GoTo ErrHnd

'get start row
lngRow = Selection.Row
intCol = Selection.Column
'get number of rows
lngRowCount = Selection.Rows.Count

For n = lngRow To lngRow + lngRowCount - 1
    'get difference in dates in next two rows
    intIns = Cells(n + 1, intCol).Value - Cells(n, intCol).Value
    If intIns > 1 Then
        'insert row(s) and include next date
        For m = 2 To intIns
            Cells(n + m - 1, intCol).EntireRow.Insert Shift:=xlShiftDown
            Cells(n + m - 1, intCol).Value = Cells(n, intCol).Value + m - 1
            lngRowCount = lngRowCount + 1
        Next m
    End If
Next n
Exit Sub

'error handler
End Sub
Note that Macros cannot be undone.
Always make a backup before using this macro.

As this macro has had very limited testing, please test it on copies of your worksheets, and as noted above always make a backup before using it on real data.

To use the macro, select the column of cells containing the dates. (I haven't included any check that the selection is only one column or that the column contains dates - these checks can be added if the basic macro works).
With the dates selected, run the macro.
The macro can be linked to a button on a toolbar, for regular use.


Report •

December 14, 2009 at 22:05:22

Thanks for your reply. For some reason when I copy the code for this macro into my VB editor under a module, weather its personal, or the wookbooks normal modules the marco dissapears. Am idoing something wrong?

Report •

December 15, 2009 at 04:13:51

From what you say, I don't know why you are losing the Macro. Try following the procedure I use:

To enter the code in 'Your.xls' workbook:

Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(Your.xls)
Right click on it and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.
Click File Save.
Alt+f11 takes you back to the main Excel window and your workbook.
Select the cells with the dates.
Click inside the Macro, after Sub MakeRows() and click the f8 key. The first line of the Macro is highlighted, then click f5 and the whole macro will run.
Alt+f11 back to the workbook, to see if it worked as expected.


Report •

Related Solutions

Ask Question