Excel VBA: detect date and insert row?

Assuming you have less than 500 such entries in Column A

Paste the following into a module and run it.

If you have more than 500 entries, change the first

line in the code accordingly

Sub Insert_Rows()

For Count = 500 To 1 Step -1

If Day(Cells(Count, "A")) = 1 Then

If Cells(Count, "A") = 0 Then GoTo Line1

Cells(Count, "A").Select

Selection.EntireRow.Insert

End If

Line1:

Next Count

End Sub

1

I'm not the best at VBA, but if you just need to do this once, you don't need VBA code.

Select all of the dates in your Date column and press Ctrl+F to bring up the find box.

In the "Find what:" box, search for "/1/" and click "Find all." The find box displays a list of all its findings.

Press Ctrl+A to select all of the findings.

Now close the find box.

You have each first of the month selected, so on the top menu, click Insert, Rows for Excel 2003, or for Excel 2007, select the Home ribbon, click the drop-down arrow under "Insert" and select "Insert Sheet Rows."

This should insert a row before the first of each month.

Here is one way to do it. Select a cell at the bottom of all the cells that you will be using on your sheet e.g. cell A100 Put the row number of that cell in that cell e.g. for A100 put the value 100 Name that cell... Select the cell Select Insert\Name\Define Name the cell rowCheck and ADD OK The macro below will check the named cell "rowCheck" and see if it's value is the same as its row number. This works anytime a new row is inserted above this cell. To install this macro... Right-click on the sheet tab and select View Code Paste the code below in the VB edit window --- Private Sub Worksheet_Change(ByVal Target As Range) If Range("rowCheck").Row <> Range("rowCheck").Value Then MsgBox "New row has been inserted." Range("rowCheck").Value = Range("rowCheck").Row End If End Sub ---