Macro for Quick Formatting

[muell595]

Good afternoon, everyone.

Could I get some help on writing a macro? Starting in R1C1, I am given the following from an external database following:

[TABLE="width: 580"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]List1
[/TD]
[TD]List2
[/TD]
[TD]List3
[/TD]
[TD]List4
[/TD]
[TD]List5
[/TD]
[TD]List6
[/TD]
[TD]List7
[/TD]
[/TR]
[TR]
[TD]Useless Text
[/TD]
[TD]9483
[/TD]
[TD]37.4
[/TD]
[TD]4.3
[/TD]
[TD]19
[/TD]
[TD]23.7
[/TD]
[TD]12.5
[/TD]
[TD]3.7
[/TD]
[/TR]
[TR]
[TD]Last, First
[/TD]
[TD]62
[/TD]
[TD]0.9
[/TD]
[TD]4.5
[/TD]
[TD]0[/TD]
[TD]85.5
[/TD]
[TD]6.8
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]7/1/2013
[/TD]
[TD]2
[/TD]
[TD]1.1
[/TD]
[TD]5.5
[/TD]
[TD]0[/TD]
[TD]93
[/TD]
[TD]0[/TD]
[TD]0.3
[/TD]
[/TR]
[TR]
[TD]07:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]15.1
[/TD]
[TD]0
[/TD]
[TD]84.8
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7/2/2013
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]1.8
[/TD]
[TD][/TD]
[TD]77.5
[/TD]
[TD]14.3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]26.5
[/TD]
[TD]73.3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]99.8
[/TD]
[TD]0.2
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7.1
[/TD]
[TD]0
[/TD]
[TD]91.8
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Last2,First2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2.3
[/TD]
[TD="align: right"]86
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]07:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]99.9
[/TD]
[TD]0.1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]07:45
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]98.1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]08:45
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:00
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]94.3
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:15
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]2.6
[/TD]
[TD]0
[/TD]
[TD]97.4
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]09:30
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


I would like it to look more like the following below. Considering the actual list is hundreds of times longer than this, a macro would be nice. The colored texts in the tables above and below are actually filled with that color with black text. Essentially I would like to have the name added in a separate column left of the time, and get rid of all the crap cells to mimic this table:

[TABLE="width: 561"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Time
[/TD]
[TD]List1
[/TD]
[TD]List 2
[/TD]
[TD]List3
[/TD]
[TD]List 4
[/TD]
[TD]List 5
[/TD]
[TD]List 6
[/TD]
[TD]List 7
[/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]07:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]07:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]07:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]07:45
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]08:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]08:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]08:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]08:45
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]09:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15.1
[/TD]
[TD][/TD]
[TD]84.8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/1/2013
[/TD]
[TD]09:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]07:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26.5
[/TD]
[TD]73.3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]07:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]99.8
[/TD]
[TD]0.2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]07:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]07:45
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]08:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]08:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]08:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]08:45
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]09:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]7.1
[/TD]
[TD][/TD]
[TD]91.8
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last, First 7/2/2013
[/TD]
[TD]09:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]07:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]07:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]99.9
[/TD]
[TD]0.1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]07:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]07:45
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]98.1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]08:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]08:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]08:30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]08:45
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]09:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]94.3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2,First2 7/1/2013
[/TD]
[TD]09:15
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2.6
[/TD]
[TD][/TD]
[TD]97.4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I don't even care about the fill or anything of the like either. Is there someone out there willing to be a hero today and help me? It would be greatly appreciated!

 

[sijpie]

How do I know that the first entries for Last2,First2 are starting on 7/1/2013? In the macro below I have assumed a new section (unless immediately followed by date) will start at the very first date in the list.

I also do not know if the dates and times are imported as text or as numbers, so i have taken both options into account.

Because we work totally in memory, only one read from the sheet, and one write to the sheet, the macro is very fast

Enjoy.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> ResortList()<br>    <SPAN style="color:#00007F">Dim</SPAN> rOut <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> aIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, aOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lRin <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lRout <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sSect <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">'We are going to do all the rehashing of _<br>    first two columns and deleting of zeros _<br>    in memory, rather than looping on the sheet. _<br>    For this we work with two arrays, aIn and aOut</SPAN><br>    <br>    <SPAN style="color:#007F00">'fill aIn array with the input data table</SPAN><br>    aIn = Cells(1, 1).CurrentRegion<br>    <br>    <SPAN style="color:#007F00">'the array is now just like a sheet with _<br>     'rows' and 'columns'. Now we need to size _<br>     the output array to be same number of rows _<br>     and one column wider. In fact it will give _<br>     us few too many rows, but that doesn't matter</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> aOut(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aIn, 1), 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aIn, 2) + 1)<br>    <br>    <SPAN style="color:#007F00">'Set title row</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aIn, 2)<br>        aOut(1, i + 1) = aIn(1, i)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    aOut(1, 1) = aIn(1, 1)<br>    aOut(1, 2) = "Time"<br>    lRout = 2<br><br>    <SPAN style="color:#007F00">'now process rest of aIn. Start on row 3</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lRin = 3 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aIn, 1)<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Case</SPAN> Left(aIn(lRin, 1), 4) = "Last"<br>                <SPAN style="color:#007F00">'signals 'new section'</SPAN><br>                sSect = aIn(lRin, 1)<br>                aOut(lRout, 1) = sSect & " - " & sDate<br>            <br>            <SPAN style="color:#00007F">Case</SPAN> Mid(aIn(lRin, 1), 3, 1) = "/", _<br>                IsDate(aIn(lRin, 1))<br>                <SPAN style="color:#007F00">'signals new date</SPAN><br>                <SPAN style="color:#007F00">'if first date, store for start _<br>                 new sections</SPAN><br>                <SPAN style="color:#007F00">'in above case i check for both numerical _<br>                 date as for text date format</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> sDate = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> IsDate(aIn(lRin, 1)) <SPAN style="color:#00007F">Then</SPAN><br>                        sDate = Format(aIn(lRin, 1), "MM/D/YYYY")<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                        sDate = aIn(lRin, 1)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#007F00">'Put section & date in first column</SPAN><br>                aOut(lRout, 1) = sSect & " - " & aIn(lRin, 1)<br>                <br>            <SPAN style="color:#00007F">Case</SPAN> Mid(aIn(lRin, 1), 3, 1) = ":", IsNumeric(aIn(lRin, 1))<br>                <SPAN style="color:#007F00">' signals a time. copy List columns</SPAN><br>                <SPAN style="color:#007F00">'case accounts for both text time as for _<br>                 numerical time (fraction)</SPAN><br>                <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aOut, 2)<br>                    <SPAN style="color:#00007F">If</SPAN> aIn(lRin, i - 1) <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                        aOut(lRout, i) = aIn(lRin, i - 1)<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> i<br>                <SPAN style="color:#00007F">If</SPAN> aOut(lRout, 1) = vbNullString <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#007F00">'copy 'cell' above</SPAN><br>                    aOut(lRout, 1) = aOut(lRout - 1, 1)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                lRout = lRout + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lRin<br><br>    <SPAN style="color:#007F00">'output array to new sheet</SPAN><br>    Sheets.Add after:=ActiveSheet<br>   <SPAN style="color:#007F00">' ActiveSheet.Name = "List"</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rOut = Cells(1, 1) <SPAN style="color:#007F00">'set to A1</SPAN><br>    <SPAN style="color:#007F00">'write data from aOut to range sized to same size</SPAN><br>    rOut.Resize(UBound(aOut, 1), UBound(aOut, 2)).Value = aOut<br>    <br>    Columns(1).EntireColumn.AutoFit<br>    Columns(2).EntireColumn.NumberFormat = "HH:MM"<br>    <br>    <SPAN style="color:#007F00">'clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rOut = <SPAN style="color:#00007F">Nothing</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[muell595]

I have a feeling this is going to work extremely well, but I am now getting an error with the number of arguments for line:
sDate = Format(aIn(lRin, 1), "MM/D/YYYY")

Is that becasue it's the wrong format? Honestly, it'll take me some time to wrap my head around this beautiful piece of work you wrote, so at this moment I'm sitting here with my head sideways, drooling. Do you know the quick answer for this one?

The dates and times are all imported as text/"General." I have a feeling that's they key here, but I don't really know for sure.

 

[sijpie]

Mmhh, although the format should be "MM/DD/YYYY" in your case this should not a difference


I suggest you do a bit of debugging: open the vba editor, go to the macro and click in the border left of this line. A red dot should appear and the line should turn red. You have set a breakpoint, the code will stop here every time it runs. So run the macro. The editor will show with the red line marked yellow. Hold your mouse over the aIn(lRin,1). It should show you the date. Press F8 key to execute the next step. If it executes without an error, then press F5 to continue run the code. It will stop at the same line again. Do this until it gives the error. What is the value of aIn(lRin,1) ?


I do not have access to Excel at the moment, and so can't check, but possibly the format function can't operate if the date is text and not a value. I will then have to rethink how to modify this piece of code.


but let me know the results of the debugging.