Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert Rows after each Date (office xp)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert Rows after each Date (office xp)

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows after each Date (office xp)

    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.
    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows after each Date (office xp)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows after each Date (office xp)

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

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Rows after each Date (office xp)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •