Code To Remove Only 2 Rows

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
I have a sheet as below. Whenever there are 2 rows only that match in K then I want them removed to another sheet. This must be when there is one heater blower and one heater in column AF. So in this example below rows 2-4 and 5-7 will remain as there are 3 rows but the rest would be removed to another sheet as there are only 2 rows and there is one description for each.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5 " /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5 ;text-align: center;color: #161120"><th></th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FF0000;;">M14301280143001</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="background-color: #FF0000;;">Heater Blower</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FF0000;;">M14301280143001</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="background-color: #FF0000;;">Heater</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FF0000;;">M14301280143001</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td><td style="background-color: #FF0000;;">Heater</td><td style="text-align: right;background-color: #FF0000;;"></td><td style="text-align: right;background-color: #FF0000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #00FF00;;">M14301280143003</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="background-color: #00FF00;;">Heater Blower</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="background-color: #00FF00;;">M14301280143003</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="background-color: #00FF00;;">Heater</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="background-color: #00FF00;;">M14301280143003</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td><td style="background-color: #00FF00;;">Heater</td><td style="text-align: right;background-color: #00FF00;;"></td><td style="text-align: right;background-color: #00FF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #FFFF99;;">M14301280145007</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="background-color: #FFFF99;;">Heater Blower</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="background-color: #FFFF99;;">M14301280145007</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="background-color: #FFFF99;;">Heater</td><td style="text-align: right;background-color: #FFFF99;;"></td><td style="text-align: right;background-color: #FFFF99;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #99CCFF;;">M14301280145011</td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="background-color: #99CCFF;;">Heater Blower</td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="background-color: #99CCFF;;">M14301280145011</td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="background-color: #99CCFF;;">Heater</td><td style="text-align: right;background-color: #99CCFF;;"></td><td style="text-align: right;background-color: #99CCFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="background-color: #FFFFFF;;">M14301280145008</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="background-color: #FFFFFF;;">Heater Blower</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="background-color: #FFFFFF;;">M14301280145008</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="background-color: #FFFFFF;;">Heater</td><td style="text-align: right;background-color: #FFFFFF;;"></td><td style="text-align: right;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="background-color: #FF99CC;;">M14301280145002</td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="background-color: #FF99CC;;">Heater Blower</td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="background-color: #FF99CC;;">M14301280145002</td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="background-color: #FF99CC;;">Heater</td><td style="text-align: right;background-color: #FF99CC;;"></td><td style="text-align: right;background-color: #FF99CC;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="background-color: #C0C0C0;;">M14301280145005</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="background-color: #C0C0C0;;">Heater Blower</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="background-color: #C0C0C0;;">M14301280145005</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="background-color: #C0C0C0;;">Heater</td><td style="text-align: right;background-color: #C0C0C0;;"></td><td style="text-align: right;background-color: #C0C0C0;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5 ;color: #161120">Sheet1</p><br /><br />
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
I hope I explained this ok, please post should you need clarification.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,852
Office Version
365
Platform
Windows
Here is one way to do it:

Enter this formula in cell AI2 and copy down for all rows:
Code:
=AND(COUNTIF(K:K,K2)=2,COUNTIFS(K:K,K2,AF:AF,"Heater Blower")=1,COUNTIFS(K:K,K2,AF:AF,"Heater")=1)
This should identify every row that needs to be moved by returning "TRUE".
You can then use Advanced Filters to copy them to another sheet, and then delete the TRUE entries from the original sheet.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,852
Office Version
365
Platform
Windows
Here is the VBA code that I came up with to do this:
Code:
Sub MyMoveMacro()

    Dim lr As Long
    Dim sh1 As Worksheet, sh2 As Worksheet

    Application.ScreenUpdating = False

'   Set worksheet data resides on
    Set sh1 = Sheets("Sheet1")
'   Set worksheet to copy data to
    Set sh2 = Sheets("Sheet2")

'   Find last row with data in column K
    lr = sh1.Cells(Rows.Count, "K").End(xlUp).Row

'   Populate formula in column AI
    sh1.Range("AI1") = "Move"
    sh1.Range("AI2:AI" & lr).FormulaR1C1 = _
        "=AND(COUNTIF(C[-24],RC[-24])=2,COUNTIFS(C[-24],RC[-24],C[-3],""Heater Blower"")=1,COUNTIFS(C[-24],RC[-24],C[-3],""Heater"")=1)"

'   Filter TRUE entries to new sheet
    sh2.Activate
    sh2.Range("AK1") = "Move"
    sh2.Range("AK2") = "TRUE"
    sh1.Range("K1:AI" & lr).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("AK1:AK2"), CopyToRange:=Range("K1"), Unique:=False
    sh2.Columns("AI:AK").ClearContents
    sh2.Cells.EntireColumn.AutoFit
    
'   Delete TRUE entries from original sheet
    sh1.Activate
    sh1.Range("$K$1:$AI$" & lr).AutoFilter Field:=25, Criteria1:="TRUE"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    sh1.AutoFilterMode = False
    sh1.Columns("AI:AI").ClearContents

    Application.ScreenUpdating = True

End Sub
Note that you will need to update the value of the "sh1" and "sh2" variables to reflect the names of the sheet that you are working with.
I am also assuming that your data is in columns K:AH. If we need to extend it out to include other columns, the code may need to be altered.
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
Sorry the data starts in A to AV. I thought I would just include the important data to make it clearer.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,852
Office Version
365
Platform
Windows
Yes, it is important to mention that, since you want to move those cells too.

Try this variation:
Code:
Sub MyMoveMacro()

    Dim lr As Long
    Dim sh1 As Worksheet, sh2 As Worksheet

    Application.ScreenUpdating = False

'   Set worksheet data resides on
    Set sh1 = Sheets("Sheet1")
'   Set worksheet to copy data to
    Set sh2 = Sheets("Sheet2")

'   Find last row with data in column K
    lr = sh1.Cells(Rows.Count, "K").End(xlUp).Row

'   Populate formula in column AW
    sh1.Range("AW1") = "Move"
    sh1.Range("AW2:AW" & lr).FormulaR1C1 = _
        "=AND(COUNTIF(C[-38],RC[-38])=2,COUNTIFS(C[-38],RC[-38],C[-17],""Heater Blower"")=1,COUNTIFS(C[-38],RC[-38],C[-17],""Heater"")=1)"

'   Filter TRUE entries to new sheet
    sh2.Activate
    sh2.Range("AX1") = "Move"
    sh2.Range("AX2") = "TRUE"
    sh1.Range("A1:AW" & lr).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("AX1:AX2"), CopyToRange:=Range("A1"), Unique:=False
    sh2.Columns("AW:AX").ClearContents
    sh2.Cells.EntireColumn.AutoFit
    
'   Delete TRUE entries from original sheet
    sh1.Activate
    sh1.Range("$A$1:$AW$" & lr).AutoFilter Field:=49, Criteria1:="TRUE"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    sh1.AutoFilterMode = False
    sh1.Columns("AW:AW").ClearContents

    Application.ScreenUpdating = True

End Sub
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
Thanks Joe I run the code and it completed but nothing moved to sheet 2? It looks like it removed them but didn't paste them onto sheet 2.
 
Last edited:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
Don't worry I used the formula and that done the job. Thanks.
 

Forum statistics

Threads
1,086,185
Messages
5,388,291
Members
402,113
Latest member
RSCD

Some videos you may like

This Week's Hot Topics

Top