Copy Down (possibly fill) - VBA - until last populated row in sheet

[Allan Walker]

Hi,

I am trying to re-format a sheet.

I know how to delete empty rows by VBA, so I can clean this up after. I'd really appreciate some help...I think it involves xlup and fill, but can't for the life of me work the sub routine out.

Note: The number of rows in the sheet can vary, so the last date and name should only be copied down as far as the end of the last populated row.

I'm using Office 2003 on XPSP3

Before:

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	YASMIN AKHTAR (YA1)
2	Mon 01 Aug 2011	*	*	*	*	*	*	*
3	*	06:00-06:30 AM	*	30mins	*	MARY COOK(PR271)	*	*
4	*	06:45-07:30 AM	*	45mins	*	BETTY YATES(WD305)	*	*
5	*	07:30-08:00 AM	*	30mins	*	JOAN OWEN(PR304)	*	*
6	*	08:15-09:15 AM	*	1 hour	*	AVTAR SINGH(WD401)	*	*
7	*	09:15-10:15 AM	*	1 hour	*	HILDA *YOUNG(WD405)	*	*
8	*	11:45-12:15 Lunchtime	*	30mins	*	PERCY COLEMAN(WD410)	*	*
9	*	12:15-12:45 Lunchtime	*	30mins	*	AUGUSTA BELL(WD273)	*	*
10	*	12:45-13:15 Lunchtime	*	30mins	*	LILLIAN WOODLEY(WD301)	*	*
11	*	13:15-13:45 Lunchtime	*	30mins	*	CHARLES BARRINGTON BATEMAN(WH2... * * * * * * * * * *	*	*
12	*	13:45-14:15 Lunchtime	*	30mins	*	BETTY MASKELL(WH229)	*	*
13	*	14:15-14:30 Lunchtime	*	15mins	*	VIOLET *LACEY(WH177)	*	*
14	*	14:30-14:45 Lunchtime	*	15mins	*	ELIZABETH POWELL(WD203)	*	*
15	*	14:45-15:00 Lunchtime	*	15mins	*	VIOLET BLOFELD(WH287091)	*	*
16	*	15:00-15:15 Lunchtime	*	15mins	*	GRACE CORDERY(WH287175)	*	*
17	*	15:15-16:00 AM	*	45mins	*	GWENDOLINE JOHNSON(WH287153) * * * * * * * * * * * * *	*	*
18	*	18:00-18:15 PM	*	15mins	*	MARJORIE CALDWELL(PR281)	*	*
19	*	18:15-18:30 PM	*	15mins	*	GWENDOLINE *WARE (WD334)	*	*
20	*	18:30-19:00 PM	*	30mins	*	TAMARA JANSSENS(WK121)	*	*
21	*	19:00-19:15 PM	*	15mins	*	MOHINDER AHLUWALIA(WK321)	*	*
22	*	19:15-19:45 PM	*	30mins	*	VERA AXTELL(WD414)	*	*
23	*	19:45-20:15 PM	*	30mins	*	JOAN MACE(WK406)	*	*
24	*	20:15-20:45 PM	*	30mins	*	AVTAR SINGH(WD401)	*	*
25	*	*	*	*	*		*	* * * * * * * * * * * * *
26	*	*	*	*	*	*	*	YASMIN AKHTAR (YA1)
27	Tue 02 Aug 2011	*	*	*	*	*	*	*
28	*	06:00-06:15 AM	*	15mins	*	AMALIA ATKINSON(WH287184)	*	*
29	*	06:45-07:30 AM	*	45mins	*	BETTY YATES(WD305)	*	*
30	*	07:30-08:00 AM	*	30mins	*	MARY COOK(PR271)	*	*
31	*	08:15-09:15 AM	*	1 hour	*	AVTAR SINGH(WD401)	*	*
32	*	09:15-10:15 AM	*	1 hour	*	HILDA *YOUNG(WD405)	*	*
33	*	11:15-11:30 Lunchtime	*	15mins	*	VIOLET BLOFELD(WH287091)	*	*
34	*	11:30-11:45 Lunchtime	*	15mins	*	ELIZABETH POWELL(WD203)	*	*
35	*	12:00-12:30 Lunchtime	*	30mins	*	PERCY COLEMAN(WD410)	*	*
36	*	12:30-13:00 Lunchtime	*	30mins	*	LILLIAN WOODLEY(WD301)	*	*
37	*	13:15-13:45 Lunchtime	*	30mins	*	CHARLES BARRINGTON BATEMAN(WH2... * * * * * * * * * *	*	*
38	*	13:45-14:15 Lunchtime	*	30mins	*	BETTY MASKELL(WH229)	*	*
39	*	14:15-14:30 Lunchtime	*	15mins	*	VIOLET *LACEY(WH177)	*	*
Sheet 1

After (the result I am after)

Excel Workbook
	A	B	C	D	E	F	G	H
3	Mon 01 Aug 2011	06:00-06:30 AM	*	30mins	*	MARY COOK(PR271)	*	YASMIN AKHTAR (YA1)
4	Mon 01 Aug 2011	06:45-07:30 AM	*	45mins	*	BETTY YATES(WD305)	*	YASMIN AKHTAR (YA1)
5	Mon 01 Aug 2011	07:30-08:00 AM	*	30mins	*	JOAN OWEN(PR304)	*	YASMIN AKHTAR (YA1)
6	Mon 01 Aug 2011	08:15-09:15 AM	*	1 hour	*	AVTAR SINGH(WD401)	*	YASMIN AKHTAR (YA1)
7	Mon 01 Aug 2011	09:15-10:15 AM	*	1 hour	*	HILDA *YOUNG(WD405)	*	YASMIN AKHTAR (YA1)
8	Mon 01 Aug 2011	11:45-12:15 Lunchtime	*	30mins	*	PERCY COLEMAN(WD410)	*	YASMIN AKHTAR (YA1)
9	Mon 01 Aug 2011	12:15-12:45 Lunchtime	*	30mins	*	AUGUSTA BELL(WD273)	*	YASMIN AKHTAR (YA1)
10	Mon 01 Aug 2011	12:45-13:15 Lunchtime	*	30mins	*	LILLIAN WOODLEY(WD301)	*	YASMIN AKHTAR (YA1)
11	Mon 01 Aug 2011	13:15-13:45 Lunchtime	*	30mins	*	CHARLES BARRINGTON BATEMAN(WH2... * * * * * * * * * *	*	YASMIN AKHTAR (YA1)
12	Mon 01 Aug 2011	13:45-14:15 Lunchtime	*	30mins	*	BETTY MASKELL(WH229)	*	YASMIN AKHTAR (YA1)
13	Mon 01 Aug 2011	14:15-14:30 Lunchtime	*	15mins	*	VIOLET *LACEY(WH177)	*	YASMIN AKHTAR (YA1)
14	Mon 01 Aug 2011	14:30-14:45 Lunchtime	*	15mins	*	ELIZABETH POWELL(WD203)	*	YASMIN AKHTAR (YA1)
15	Mon 01 Aug 2011	14:45-15:00 Lunchtime	*	15mins	*	VIOLET BLOFELD(WH287091)	*	YASMIN AKHTAR (YA1)
16	Mon 01 Aug 2011	15:00-15:15 Lunchtime	*	15mins	*	GRACE CORDERY(WH287175)	*	YASMIN AKHTAR (YA1)
17	Mon 01 Aug 2011	15:15-16:00 AM	*	45mins	*	GWENDOLINE JOHNSON(WH287153) * * * * * * * * * * * * *	*	YASMIN AKHTAR (YA1)
18	Mon 01 Aug 2011	18:00-18:15 PM	*	15mins	*	MARJORIE CALDWELL(PR281)	*	YASMIN AKHTAR (YA1)
19	Mon 01 Aug 2011	18:15-18:30 PM	*	15mins	*	GWENDOLINE *WARE (WD334)	*	YASMIN AKHTAR (YA1)
20	Mon 01 Aug 2011	18:30-19:00 PM	*	30mins	*	TAMARA JANSSENS(WK121)	*	YASMIN AKHTAR (YA1)
21	Mon 01 Aug 2011	19:00-19:15 PM	*	15mins	*	MOHINDER AHLUWALIA(WK321)	*	YASMIN AKHTAR (YA1)
22	Mon 01 Aug 2011	19:15-19:45 PM	*	30mins	*	VERA AXTELL(WD414)	*	YASMIN AKHTAR (YA1)
23	Mon 01 Aug 2011	19:45-20:15 PM	*	30mins	*	JOAN MACE(WK406)	*	YASMIN AKHTAR (YA1)
24	Mon 01 Aug 2011	20:15-20:45 PM	*	30mins	*	AVTAR SINGH(WD401)	*	YASMIN AKHTAR (YA1)
25	*	*	*	*	*		*	* * * * * * * * * * * * *
26	*	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*	*
28	Tue 02 Aug 2011	06:00-06:15 AM	*	15mins	*	AMALIA ATKINSON(WH287184)	*	YASMIN AKHTAR (YA1)
29	Tue 02 Aug 2011	06:45-07:30 AM	*	45mins	*	BETTY YATES(WD305)	*	YASMIN AKHTAR (YA1)
30	Tue 02 Aug 2011	07:30-08:00 AM	*	30mins	*	MARY COOK(PR271)	*	YASMIN AKHTAR (YA1)
31	Tue 02 Aug 2011	08:15-09:15 AM	*	1 hour	*	AVTAR SINGH(WD401)	*	YASMIN AKHTAR (YA1)
32	Tue 02 Aug 2011	09:15-10:15 AM	*	1 hour	*	HILDA *YOUNG(WD405)	*	YASMIN AKHTAR (YA1)
33	Tue 02 Aug 2011	11:15-11:30 Lunchtime	*	15mins	*	VIOLET BLOFELD(WH287091)	*	YASMIN AKHTAR (YA1)
34	Tue 02 Aug 2011	11:30-11:45 Lunchtime	*	15mins	*	ELIZABETH POWELL(WD203)	*	YASMIN AKHTAR (YA1)
35	Tue 02 Aug 2011	12:00-12:30 Lunchtime	*	30mins	*	PERCY COLEMAN(WD410)	*	YASMIN AKHTAR (YA1)
36	Tue 02 Aug 2011	12:30-13:00 Lunchtime	*	30mins	*	LILLIAN WOODLEY(WD301)	*	YASMIN AKHTAR (YA1)
37	Tue 02 Aug 2011	13:15-13:45 Lunchtime	*	30mins	*	CHARLES BARRINGTON BATEMAN(WH2... * * * * * * * * * *	*	YASMIN AKHTAR (YA1)
38	Tue 02 Aug 2011	13:45-14:15 Lunchtime	*	30mins	*	BETTY MASKELL(WH229)	*	YASMIN AKHTAR (YA1)
39	Tue 02 Aug 2011	14:15-14:30 Lunchtime	*	15mins	*	VIOLET *LACEY(WH177)	*	YASMIN AKHTAR (YA1)
Sheet 1

Many thanks in advance,

Allan

 

[Andrew Poulsom]

Select column A and choose Edit|Go To|Special, check Blanks and click OK. Go to the Formula Bar, type =, point to the cell above the active cell and press Ctrl+Enter. You can copy/paste special values afterwards if you want.

 

[Allan Walker]

Hi Andrew,

Firstly, many thanks for your quick response!

I am getting some very odd results! When I followed your instructions, I was getting a fill series rather than copy (in column A). When I repeated the process for column H - every blank cell in column H was being populated with an equals sign!

Would you like me to post the results?

Will I be able to record a macro using this procedure?

Best Regards,

Allan

 

[Andrew Poulsom]

I see you have a blank cell in A1. So rather than selecting the entire column select from A2 to the last used call in column A. Then follow the steps I outlined. A3 should be the active cell and its formula should be =A2. The formula in A4 should be =A3 and so on.

 

[Allan Walker]

Hi Andrew,

I am able to cut and paste A2 to A1, and then applied your process, it works brilliantly, and I was able to record a subroutine. Many thanks, your help is really appreciated.