insert 2 blank rows if after every week

L

Lisa Hetherington

I have a 365 row sheet for each day of the year....I would like Excel to add
2 blank rows below every cell that has "Sunday" in it.

dates are entered as 1/1/2007 but are displayed using a custom display of
ddddmmmmd

Using Excel 2003

Thanks!!

....Lisa
 
H

Harlan Grove

Lisa Hetherington said:
I have a 365 row sheet for each day of the year....I would like Excel to add
2 blank rows below every cell that has "Sunday" in it.

dates are entered as 1/1/2007 but are displayed using a custom display of
ddddmmmmd
Click to expand...

There's an easy way to do this without macros. If your dates in your
table of day-of-year records were in A3:A367, and rows 368 to 471 were
empty, enter 1/7/2007 8:00 in A368 and 1/7/2007 16:00 in A369. Select
A370:A471 with cell A370 the active cell, type =A368+7 and press
[Ctrl]+[Enter]. This adds entries for each Sunday in 2007 at 8:00 AM
and 4:00 PM, which are both greater than just the Sunday dates (which
are taken to be midnight) but less than the following Monday dates.
With A370:A471 still selected, press [Ctrl]+C then Edit > Paste Special
as values. Sort the table from row 3 to row 471 in ascending order,
which should sort the added Sunday 8:00 AM and 4:00 PM entries between
the preexisting Sunday and Monday entries. Then select A3:A471 and run
Edit > Replace, replacing *:* with nothing, which will clear the cells
containing the Sunday 8:00 AM and 4:00 PM entries.
 
M

MartinW

Hi Lisa,

If you are setting up a new sheet you can put 1/1/2007 in cell A1
and drag it down to A7 which will be sunday 7/1/2007.
Then select A1 to A9 grab the fill handle and drag it down as
far as needed.

If this is an existing sheet you could use this column as a helper column
and do a bit of juggling with autofiltering and cutting and pasting
to a helper sheet to sort the rest of your data.

HTH
Martin
 
R

Roger Govier

Hi Lisa

If you are setting up a new sheet, then fill in your dates down to the
first Sunday
I started in A3, and filled in dates down to A9 (07/01/2007)
In cell A10 enter
=IF(AND(A8="",A9=""),A7+1,IF(A9="","",IF(WEEKDAY(A9)=1,"",A9+1)))
and copy down as far as required.
When finished, copy the whole block of cells and Paste Special>Values.
 
L

Lori

In xl2002/3 try:

Edit > Find type "sunday" with options Lookin: values, click Find All
and highlight all instances with Ctrl+A then select Insert > Rows.
 
L

Lori

I just noticed rows are inserted below sunday, so you need to type
"monday" in the find box. Click Find All, Ctrl+A and Insert>Rows again
to add a second row.
 
L

Lisa Hetherington

I got quite a few suggestions. Thank you

This one is the one for me!

....Lisa
 
Top