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

[Albert]

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?

 

[Juan Pablo González]

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

 

[Albert]

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

 

[Anne Troy]

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?

 

[Albert]

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.

 

[Guest]

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