Reorder columns using a macro

[stephjayne165]

I have an Excel 2007 spreadsheet where I need to reorder the columns. The column headings are as follows:

<TABLE style="WIDTH: 530pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=700><COLGROUP><COL style="WIDTH: 53pt" span=3 width=70><COL style="WIDTH: 53pt" width=70><COL style="WIDTH: 53pt" span=6 width=70><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=17 width=70>COLUMN1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=70>COLUMN10</TD></TR></TBODY></TABLE>

They need to be rearranged in the following order:

<TABLE style="WIDTH: 530pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=700><COLGROUP><COL style="WIDTH: 53pt" span=10 width=70><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 width=70>COLUMN2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=70>COLUMN5</TD></TR></TBODY></TABLE>

Please bear in mind I've never actually used VBA to script a macro (purely just recording), so keep explainations nice and simple for me!

Also, is there a way for me to tag this onto the end of a macro I've recorded which deletes unwanted data?

Your help is appreciated!

 

[AlphaFrog]

Hello AlphaFrog, I used this code and it works great but I would like to know how can I add a column in between the ones I need to reorder.

Thank you

Don't know what version of the code you're using; reordering by column headers or column numbers.

If you are adding a column before you reorder the others and you order the columns using column headers, give the new column a header (even if it's temporary) and include it in the arrColOrder array. You can delete the temporary header after the columns are reordered if you like.

If you are adding a column before you reorder the others and if you order the columns using column numbers, include it in the arrColOrder number array.

If you add the new column after you reorder the columns, I don't see the problem.

 

[maj1c]

Hello all, specially to Alphafrog, could you help me or advice me with this?

I am facing a similar ordering Task, but with a higher degree of difficulty becuase it is about ordering and inserting new columns, based in the order of another Woorkbook's Headers and preferably not by writing the order in the code.

The next table is a shorter Version of the files I am working with (in total are 30-40 columns):

Culums in the Reference WB (master)	Columns in WB to reorder (slave)
APS	APS
ADA	ADA_SI
ROIshort	ZONE
DS New	Component
Component	Part_Name
NAME_	Code
Station	ROI_Short
Zone

<tbody>
</tbody>

The first Problem is that the headers dont even match. I do not if I should stop right here and say that I cannot be done until the files contain the same headers.

The second problem is that some columns do not exist in the other WB (marked in red). When there is a column in the Reference file that do not exist in the "slave", I have to insert a BLANK column in its place. When there is a column in the "slave" that do not exist in the "master", it should only be moved at the end of the table.

Culums in the Reference WB (master)	Columns in WB to reorder (slave)	EXPECTED ORDERED FILE
APS	APS	APS
ADA	ADA_SI	ADA_SI
ROI_Kurz	ZONE	ROI_Short
DS New	Component	BLANK COLUMN
Component	Part_Name	Component
NAME_	Code	Part_Name
Station	ROI_Short	BLANK COLUMN
Zone		ZONE
		Code

<tbody>
</tbody>

Since I am a true beginner, I have had some hard time even thinking how to start. I think it is a Little overwhelming and I would really appreciate help or guidance.

Thank you in advance!