Macro to clear content &

[Marlon]

Here below you see a copy out of a workschedule from my team.

I need a macro (will create buttons for this) which deletes and merge some cells. In screenshot 2 you see how I would like to have the results.

Also in screenshot 2 you see that in the shedule for Felix nothing changed (away from the part that 3L is gone) because he has holidays.

The reason i need this is that we have a full working macro, however our shedule tool changed and we are not able to copy and paste the lists anomore because of the multiple cells.


<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><TBODY><TR style="HEIGHT: 23.25pt" height=31><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 36pt; BACKGROUND-COLOR: transparent" vAlign=top align=left width=72 height=48 rowSpan=2><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><TABLE cellSpacing=0 cellPadding=0><TBODY><TR><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 36pt; BACKGROUND-COLOR: #fff58c" width=72 height=48 rowSpan=2 __descending="true">Employee</TD></TR></TBODY></TABLE>

</TD><TD class=xl28 id=EmpNameCell style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fff58c" width=77>Monday</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 id=headingDate0 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #fff58c" align=right width=77 height=17 x:num="38608">9/14/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="0"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Achleitner, Felix</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=77>3L</TD></TR><TR id=dataRow-979683331503 style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.5625">1:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.91666666666666663">10:00 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17>3LVAC</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="1"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Bauer, Martina</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=77>3L</TD></TR><TR id=dataRow-979683331282 style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.33333333333333331">8:00 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.6875">4:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="2"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Dorandt, Jana</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=77>3L</TD></TR><TR id=dataRow-979683330764 style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.5625">1:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.91666666666666663">10:00 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="3"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Ellen, Sandy</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=77>3L</TD></TR><TR id=dataRow-979683330665 style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.33333333333333331">8:00 AM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.60416666666666663">2:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17></TD></TR></TBODY></TABLE>

Screenshots 2

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=149 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2464" width=77><TBODY><TR style="HEIGHT: 12.75pt" height=17 pos="0"><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" vAlign=top align=left width=72 height=34 rowSpan=2><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=_x0000_s4112 style="MARGIN-TOP: 0px; Z-INDEX: 6; MARGIN-LEFT: 0px; WIDTH: 9.75pt; POSITION: absolute; HEIGHT: 9.75pt" alt="" type="#_x0000_t75"><v:imagedata o:title="sort_up" src="file:///C:\DOCUME~1\MVANHO~1\LOCALS~1\Temp\msohtml1\01\clip_image001.gif"></v:imagedata><?xml:namespace prefix = x ns = "urn:schemas-microsoft-comffice:excel" /><x:ClientData ObjectType="Pict"><x:SizeWithCells></x:SizeWithCells><x:CF>Bitmap</x:CF> <x:AutoPict></x:AutoPict></x:ClientData></v:shape><TABLE cellSpacing=0 cellPadding=0><TBODY><TR id=dataRow-979683331503><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #fff58c" width=72 height=34 rowSpan=2>Employee Name</TD></TR></TBODY></TABLE></TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fff58c" width=77>Monday</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 id=headingDate0 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #fff58c" align=right width=77 height=17 x:num="38608">9/14/2009</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="0"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Achleitner, Felix</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=77></TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="1"><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.5625">1:30 PM</TD></TR><TR id=dataRow-979683331282 style="HEIGHT: 12.75pt" height=17><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="0.91666666666666663">10:00 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17>3LVAC</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17 pos="1"><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Bauer, Martina</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=77 rowSpan=4>8:00 AM 4:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="2"></TR><TR id=dataRow-979683330764 style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17 pos="2"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Dorandt, Jana</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=77 rowSpan=4>1:30 PM 10:00 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="3"></TR><TR id=dataRow-979683330665 style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17 pos="3"><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 51pt; BACKGROUND-COLOR: white" width=72 height=68 rowSpan=4>Ellen, Sandy</TD><TD class=xl37 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=77 rowSpan=4>8:00 AM 4:30 PM</TD></TR><TR style="HEIGHT: 12.75pt" height=17 pos="4"></TR><TR id=dataRow-979683305886 style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

 

[Michaels]

Hello and Welcome OnBoard!

If you know to do it manually and have to do it again and again, why not do this by recording a macro. Start recording macro, delete and merge whatever you like. Whatever you are doing will be recorded behing the scenes. Finally, when you are done, make a textbox and assign it the macro you just recorded. Next time, you will only have to click the textbox and BOOM BOOM...magic...cells will be merged and deleted.

Hope this helps.