Seeking help to format data with a macro

[abberyfarm]

Hi there,

I was hoping somebody might be able to help me check and format some data with some code.

I have 100,000+ rows that look like this

Excel Workbook
	A	B	C	D	E	F	G
1	start_date	start_time	end_date	end_time	Type	SOC Before	SOC After
2	08/01/2012	18:36:20	08/01/2012	18:40:00	T	*	*
3	08/01/2012	18:45:12	08/01/2012	18:50:44	T	*	*
4	08/01/2012	18:52:12	08/01/2012	19:50:46	C	34	46
5	08/01/2012	19:54:27	08/01/2012	21:44:40	C	46	81
6	08/01/2012	21:48:41	08/01/2012	22:02:36	C	81	87
7	08/01/2012	22:06:46	08/01/2012	22:11:49	C	87	87
8	08/01/2012	22:15:50	08/01/2012	23:02:00	C	87	100
9	09/01/2012	08:16:51	09/01/2012	08:44:08	T	*	*
10	09/01/2012	08:45:11	09/01/2012	08:49:55	T	*	*
Sheet1

I'm trying to merge all the "C" types in Column E into one row like this

Excel Workbook
	A	B	C	D	E	F	G
14	start_date	start_time	end_date	end_time	Type	SOC Before	SOC After
15	08/01/2012	18:36:20	08/01/2012	18:40:00	T	*	*
16	08/01/2012	18:45:12	08/01/2012	18:50:44	T	*	*
17	08/01/2012	18:52:12	08/01/2012	23:02:00	C	34	100
18	09/01/2012	08:16:51	09/01/2012	08:44:08	T	*	*
19	09/01/2012	08:45:11	09/01/2012	08:49:55	T	*	*
Sheet1

So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.

I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.

Appreciate any help with this

Thank you in advance

John

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jun07
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Offset(, 4) = "C" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    nRng(1).Offset(, 3) = nRng(nRng.Count).Offset(, 3)
    nRng(1).Offset(, 6) = nRng(nRng.Count).Offset(, 6)
    nRng.Offset(1).Resize(nRng.Count - 1).EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[taurean]

Does the '*' [e.g. in cell F2] mean real empty space [doesn't contain a formula which gives blank]?