VBA Macro to help clean up report

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
I am trying to rearrange a report so it can be used in a differnt department. They only need certain information, and one column of info contains a five digit code that needs to be translated into an address.

I would like to create a macro that will delete all unwanted columns, Then insert a column next to the five digit address codes, and insert the address, the code stands for. It would also be ok for the address to replace the five digit code. Finally I would like to delete an entire row if one of the columns contains certain information that I will specify.

Summary
Delete columns:2,3,5,10,11,12,13,14,15,16,17,18,19,20

(Add column next to what will be 6 after the others are deleted {6 Contains the 5 Digit codes} Output Corrisponding address) or (Replace Five digit Code with specified address)

Delete Rows that contain specified data in Column 25

I hope this is easy enough to understand

Thanks for any help
Bryan
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
This is original
PIPELINE

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 129px"><COL style="WIDTH: 67px"><COL style="WIDTH: 100px"><COL style="WIDTH: 90px"><COL style="WIDTH: 97px"><COL style="WIDTH: 77px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 69px"><COL style="WIDTH: 107px"><COL style="WIDTH: 86px"><COL style="WIDTH: 72px"><COL style="WIDTH: 126px"><COL style="WIDTH: 111px"><COL style="WIDTH: 135px"><COL style="WIDTH: 68px"><COL style="WIDTH: 123px"><COL style="WIDTH: 152px"><COL style="WIDTH: 140px"><COL style="WIDTH: 150px"><COL style="WIDTH: 142px"><COL style="WIDTH: 131px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 146px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD><TD>Z</TD><TD>AA</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TFDM5F17AX015641</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8359</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SA</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42267</TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">U</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">3473</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">T</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004646476</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010/07/06</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Ready</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">BC</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">55</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004517632</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/20</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SF</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">56</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004515827</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/10/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDKN3DU0A5082535</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1225</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">SF</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004531220</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/11/23</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">PR</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">32</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004533408</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/03</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/21</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">4T1BF3EK5AU089590</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2532</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">FLTBB</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">F</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">LA001</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">04025</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">U</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">195</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">H</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">G9</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">V2010033663C</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2009/11/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8185814</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">1NXBU4EE3AZ321173</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">FR</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42103</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">A</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">-7</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">R</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">E</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">N</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">000004543319</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2009/12/29</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2010/01/01</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2010/01/15</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est Comp dt</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Locked</TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

I would like it to look like this once macro has been run.

PIPELINE

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 129px"><COL style="WIDTH: 67px"><COL style="WIDTH: 97px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 78px"><COL style="WIDTH: 126px"><COL style="WIDTH: 150px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TFDM5F17AX015641</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">8359</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2001/01/02</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">JTDKN3DU0A5082535</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1225</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Rev Comp dt</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">4T1BF3EK5AU089590</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">2532</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">F</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">LA001</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">04025</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Dallas, TX</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">M</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2009/11/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est VPC Arr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">1NXBU4EE3AZ321173</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">1838</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">Tyler Tx</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">2010/01/01</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00; TEXT-ALIGN: right">2010/06/22</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: Calibri; BACKGROUND-COLOR: #99cc00">Est Comp dt</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

It doesnt matter if the Location replaces the five digit code instead of creating a new column. Also I would like to delete entire rows if Column "K" contains a certain code "Shipped dt".

Thanks again!!
 
Last edited:
Upvote 0
The code to delete rows, that contain data I dont want, is working. Now all I need is to delete the unwanted columns, and find a way to replace Row "F" with the address for the corresponding code. Basically an alternative to Vlookup.
 
Upvote 0
pkaskaggs,


Sample data before the macro:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
135TFDM5F17AX01564183592010SAHO42267U3473MRT46464767/6/20101/2/20011/2/2001Est VPC ArrReady
143TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Rev Comp dtLocked
15JTDBT4K32A135813114412010SFHO4210342980A56RRGN451582710/22/20095/12/2010Rev Comp dtLocked
16JTDKN3DU0A508253512252010SFHO4210342980A32RKRGN453122011/23/20096/5/2010Rev Comp dtLocked
175TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Rev Comp dtLocked
184T1BF3EK5AU08959025322010GTGTFLTBBFLA0014025U195HMG9NV2010033663C12/2/200912/24/200911/30/200912/15/2009818581412/24/2009Est VPC Arr
191NXBU4EE3AZ32117318382010FRHO4210342980A-7RKREN454331912/22/200912/29/20091/1/20101/15/20106/22/2010Est Comp dtLocked
203TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Shipped dtLocked
215TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Shipped dtLocked
22
PIPELINE





After the macro:


Excel Workbook
ABCDEFGHIJKLMN
135TFDM5F17AX015641SA422671/2/2001Est VPC ArrReady
143TMJU4GN1AM095989BC42103429805/13/2010Rev Comp dtLocked
15JTDBT4K32A1358131SF42103429805/12/2010Rev Comp dtLocked
16JTDKN3DU0A5082535SF42103429806/5/2010Rev Comp dtLocked
175TDKK4CC2AS343200PR42103429806/5/2010Rev Comp dtLocked
184T1BF3EK5AU089590GTFLTBBFLA001402511/30/200912/15/2009818581412/24/2009Est VPC Arr
191NXBU4EE3AZ321173FR42103429801/1/20101/15/20106/22/2010Est Comp dtLocked
20
PIPELINE





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("PIPELINE")
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("J:T").EntireColumn.Delete
  .Columns("E").EntireColumn.Delete
  .Columns("B:C").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
  If .Range("L12") = "" Then .Range("L12") = "Test"
  With .Range("L12:L" & LR)
    .AutoFilter Field:=1, Criteria1:="=Shipped dt", Operator:=xlAnd
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Resize(LR).EntireRow.Delete
    .AutoFilter
  End With
  If .Range("L12") = "Test" Then .Range("L12") = ""
End With
Application.ScreenUpdating = True
End Sub


Then run the "ReordData" macro.


If you gave me the formula for cell G13, I could insert the formula for you.
 
Upvote 0
pkaskaggs,

Let me adjust the macro to delete/insert columns per your latest screenshots (I deleted columns from the right to left).
 
Upvote 0
pkaskaggs,




If you gave me the formula for cell G13, I could insert the formula for you.

This is a great start thanks!!! Can you incorperate this into your formula?

Sub test()
With ActiveSheet
.AutoFilterMode = False
With Range("y1", Range("y" & Rows.Count).End(xlUp))
.AutoFilter 1, "*Shipped dt*"
.AutoFilter 1, "*Inv dt*"
.AutoFilter 1, "*Est Comp dt*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

It is eaiser for me to add and remove the information myself as we decide what to keep and delete.

As far as column "G" is concerned, I dont have a formula, I was hoping for a generic VBA code that I could change the numbers in "F" and the ouput in "G". Then duplicate for as many address codes as we have. I think there are 1500 unique codes that stand for address.

The report only exports the codes, I want the macro to display the address in the adjecent cell. I manually typed in the City State for the example.
 
Last edited:
Upvote 0
Sample data before the macro:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
135TFDM5F17AX01564183592010SAHO42267U3473MRT46464767/6/20101/2/20011/2/2001Est VPC ArrReady
143TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Rev Comp dtLocked
15JTDBT4K32A135813114412010SFHO4210342980A56RRGN451582710/22/20095/12/2010Rev Comp dtLocked
16JTDKN3DU0A508253512252010SFHO4210342980A32RKRGN453122011/23/20096/5/2010Rev Comp dtLocked
175TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Rev Comp dtLocked
184T1BF3EK5AU08959025322010GTGTFLTBBFLA0014025U195HMG9NV2010033663C12/2/200912/24/200911/30/200912/15/2009818581412/24/2009Est VPC Arr
191NXBU4EE3AZ32117318382010FRHO4210342980A-7RKREN454331912/22/200912/29/20091/1/20101/15/20106/22/2010Est Comp dtLocked
203TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Shipped dtLocked
215TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Shipped dtLocked
22
PIPELINE





After the maceo:


Excel Workbook
ABCDEFGHIJKL
135TFDM5F17AX0156418359HO1/2/2001Est VPC Arr
143TMJU4GN1AM0959897188HO42980K5/13/2010Rev Comp dt
15JTDBT4K32A13581311441HO429805/12/2010Rev Comp dt
16JTDKN3DU0A50825351225HO42980K6/5/2010Rev Comp dt
175TDKK4CC2AS3432005328HO42980K6/5/2010Rev Comp dt
184T1BF3EK5AU0895902532GTFLA0014025M11/30/200912/24/2009Est VPC Arr
191NXBU4EE3AZ3211731838HO42980K1/1/20106/22/2010Est Comp dt
20
PIPELINE





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Dim LR As Long, a As Long
Application.ScreenUpdating = False
With Worksheets("PIPELINE")
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Columns("Z").EntireColumn.Delete
  .Columns("V:W").EntireColumn.Delete
  .Columns("O:T").EntireColumn.Delete
  .Columns("J:M").EntireColumn.Delete
  .Columns("F").EntireColumn.Delete
  .Columns("C:D").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
  If .Range("K12") = "" Then .Range("K12") = "Test"
  With .Range("K12:K" & LR)
    .AutoFilter Field:=1, Criteria1:="=Shipped dt", Operator:=xlAnd
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Resize(LR).EntireRow.Delete
    .AutoFilter
  End With
  If .Range("K12") = "Test" Then .Range("K12") = ""
End With
Application.ScreenUpdating = True
End Sub


Then run the "ReordData" macro.
 
Upvote 0
Sample data before the macro:


Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWXYZ
135TFDM5F17AX01564183592010SAHO42267U3473MRT46464767/6/20101/2/20011/2/2001Est VPC ArrReady
143TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Rev Comp dtLocked
15JTDBT4K32A135813114412010SFHO4210342980A56RRGN451582710/22/20095/12/2010Rev Comp dtLocked
16JTDKN3DU0A508253512252010SFHO4210342980A32RKRGN453122011/23/20096/5/2010Rev Comp dtLocked
175TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Rev Comp dtLocked
184T1BF3EK5AU08959025322010GTGTFLTBBFLA0014025U195HMG9NV2010033663C12/2/200912/24/200911/30/200912/15/2009818581412/24/2009Est VPC Arr
191NXBU4EE3AZ32117318382010FRHO4210342980A-7RKREN454331912/22/200912/29/20091/1/20101/15/20106/22/2010Est Comp dtLocked
203TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Shipped dtLocked
215TDKK4CC2AS34320053282010PRHO4210342980A32RKRGN453340812/3/200912/21/20096/5/2010Shipped dtLocked
225TFDM5F17AX01564183592010SAHO42267U3473MRT46464767/6/20101/2/20011/2/2001Inv dtReady
233TMJU4GN1AM09598971882010BCHO4210342980A55RKRGN451763210/12/200910/20/20095/13/2010Est Comp dtLocked
24
PIPELINE





After the macro:


Excel Workbook
ABCDEFGHIJKL
135TFDM5F17AX0156418359HO1/2/2001Est VPC Arr
143TMJU4GN1AM0959897188HO42980K5/13/2010Rev Comp dt
15JTDBT4K32A13581311441HO429805/12/2010Rev Comp dt
16JTDKN3DU0A50825351225HO42980K6/5/2010Rev Comp dt
175TDKK4CC2AS3432005328HO42980K6/5/2010Rev Comp dt
184T1BF3EK5AU0895902532GTFLA0014025M11/30/200912/24/2009Est VPC Arr
19
PIPELINE





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code:
Option Explicit
Sub ReordData()
' hiker95, 07/14/2010, http://www.mrexcel.com/forum/showthread.php?t=480930
Application.ScreenUpdating = False
With ActiveSheet
  .AutoFilterMode = False
  With .Range("Y1", .Range("Y" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="=*Shipped dt*", Operator:=xlOr, Criteria2:="=*Inv dt*"
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    .AutoFilter Field:=1, Criteria1:="=*Est Comp dt*", Operator:=xlAnd
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
  End With
  .AutoFilterMode = False
  .Columns("Z").EntireColumn.Delete
  .Columns("V:W").EntireColumn.Delete
  .Columns("O:T").EntireColumn.Delete
  .Columns("J:M").EntireColumn.Delete
  .Columns("F").EntireColumn.Delete
  .Columns("C:D").EntireColumn.Delete
  .Columns("G").EntireColumn.Insert
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
This formula works great, however if I paste the data into a blank workbook and try to run the macro I get a Run-time error '9':
Subscript out of range.

I am trying to automate it so it can be run easily multiple times a day. The report is exported from mainframe to excel. I was planning on having a blank template that coworkers could paste the information into everytime they exported then run the macro. Will this work?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top