removing a blank row and inserting it else where in a series
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: removing a blank row and inserting it else where in a series

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a list of days of the week in a column,with dates in the next column for a whole year.Between sun and mon is a blank row, and I want to move this to go between tues and wed. To many clicks etc for each individual week. Whats the macro I should use to automate this so it does the whole year?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just to make sure, can you put some 10 rows of your data and the expected results ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 19:55, Juan Pablo G. wrote:
    Just to make sure, can you put some 10 rows of your data and the expected results ?
    March Mon 4-Mar 2pm 6pm
    Tues 5-Mar 4pm 10pm
    Wed 6-Mar 11am 11pm
    Thur 7-Mar 12am 9pm
    Fri 8-Mar 3pm 8pm
    Sat 9-Mar 11am 10pm
    Sun 10-Mar 2pm 7pm

    Mon 11-Mar 10am 7pm
    Tues 12-Mar 1pm 5pm
    Wed 13-Mar 2pm 10pm
    Thur 14-Mar 4pm 6pm
    Fri 15-Mar 3pm 9pm
    Sat 16-Mar 4pm 11pm
    Sun 17-Mar 1pm 9pm

    turn this into:
    March Mon 4-Mar 2pm 6pm
    Tues 5-Mar 4pm 10pm

    Wed 6-Mar 11am 11pm
    Thur 7-Mar 12am 9pm
    Fri 8-Mar 3pm 8pm
    Sat 9-Mar 11am 10pm
    Sun 10-Mar 2pm 7pm
    Mon 11-Mar 10am 7pm
    Tues 12-Mar 1pm 5pm

    Wed 13-Mar 2pm 10pm
    Thur 14-Mar 4pm 6pm

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do this instead:

    Delete the contents of both columns, day and date.

    In the first cell of dates, type the first date you're using. Format the cell: Format-Cells-Number tab. Choose Date. Choose the one that gives you something like Friday, January 22, 2002 or whatever you like; there are ones that already show the days for you.

    Give yourself about 13 cells to start with. Select the last eight cells--a Wednesday through Tuesday and an additional blank cell beyond that. Now click and drag the fill handle (that little square black box at the bottom right-hand corner of your selection) and drag down as far as you want to go. Do NOT allow the cells to become deselected. Hit Copy. Hit Edit-Paste special-Values.

    Do you seriously need code now?


    ~Anne Troy

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    sorry I can't do that as there is data in 4 other columns to the right of the days and date columns which has to remain with the correct date.
    I have done this before using a macro with 'find, replace' but cannot remember how to do it again, and I dont have the original macro either.

  6. #6
    Guest

    Default

      
    On 2002-02-22 20:09, Albert wrote:
    sorry I can't do that as there is data in 4 other columns to the right of the days and date columns which has to remain with the correct date.
    I have done this before using a macro with 'find, replace' but cannot remember how to do it again, and I dont have the original macro either.

    Hi Albert

    I/m no Macro expert
    but I think the following code will do what you want.

    first you should select all the data on your sheet and sort by date order to remove the existing blank rows.

    then run this macro
    Sub insertrow()

    Application.Goto Reference:="R65536C1"
    Selection.End(xlUp).Select
    lastrow = Selection.Row
    Range("A2").Select
    For t = 2 To lastrow
    If (Left(ActiveCell.Value, 3) = "Tue") Then
    ActiveCell.Offset(1, 0).Range("A1").Select
    z = ActiveCell.Row
    Rows(z & ":" & z).Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(1, 0).Range("A1").Select
    Else
    ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    Next t
    End Sub


    HTH


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com