Insert Rows after each Date (office xp)

Topic

New Reply

Thanks for helping. i have daily Date in column A since 1985 until 2005. at each date, i wanted to insert an entire empty row. then, at each empty row, copy the previous date into the empty row. attached is the sample file illustrating the task and its result. please browse through the 3 worksheets.

Can anyone please help me to form a vba here?

Thanks a lot.

Reply | Quote

Replies

Here is a macro:

Sub InsertRowsAndDates()
Dim lngMaxRow As Long
Dim lngRow As Long

‘ Last populated row
lngMaxRow = Range(“A65536”).End(xlUp).Row

‘ Loop backwards
For lngRow = lngMaxRow To 2 Step -1
‘ Insert a row
Rows(lngRow + 1).Insert
‘ Copy the date
Cells(lngRow + 1, 1) = Cells(lngRow, 1)
Next lngRow
End Sub

Reply | Quote

Hans, thanks a lot for your help.

Coleman, thank you very much for your reply. this code helps me to shift other datas in following columns.

I appreciate your help.

Reply | Quote

My method would also shift the data in the other columns if those columns are included in the sort.

Reply | Quote

Hans has given you a VBA solution (you did ask in the VBA forum). However, this is very easy to do without VBA:

1- Select and copy the column of dates (not including the header)

2- Paste the copied dates immediately below the current dates.

3- Sort on the column containing the dates.

Reply | Quote

Coleman, you were right. For my task, I can also do it this way. Thanks!

Reply | Quote