Reorder columns using a macro

stephjayne165

New Member
Joined
Jan 20, 2012
Messages
23
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!
 
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.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Culums in the Reference WB (master)
[/TD]
[TD]Columns in WB to reorder :warning: (slave)
[/TD]
[/TR]
[TR]
[TD]APS
[/TD]
[TD]APS
[/TD]
[/TR]
[TR]
[TD]ADA
[/TD]
[TD]ADA_SI
[/TD]
[/TR]
[TR]
[TD]ROIshort
[/TD]
[TD]ZONE
[/TD]
[/TR]
[TR]
[TD]DS New
[/TD]
[TD]Component
[/TD]
[/TR]
[TR]
[TD]Component
[/TD]
[TD]Part_Name
[/TD]
[/TR]
[TR]
[TD]NAME_
[/TD]
[TD]Code
[/TD]
[/TR]
[TR]
[TD]Station
[/TD]
[TD]ROI_Short
[/TD]
[/TR]
[TR]
[TD]Zone
[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Culums in the Reference WB (master)
[/TD]
[TD]Columns in WB to reorder :warning: (slave)
[/TD]
[TD]EXPECTED ORDERED FILE :biggrin:
[/TD]
[/TR]
[TR]
[TD]APS
[/TD]
[TD]APS
[/TD]
[TD]APS
[/TD]
[/TR]
[TR]
[TD]ADA
[/TD]
[TD]ADA_SI
[/TD]
[TD]ADA_SI
[/TD]
[/TR]
[TR]
[TD]ROI_Kurz
[/TD]
[TD]ZONE
[/TD]
[TD]ROI_Short
[/TD]
[/TR]
[TR]
[TD]DS New
[/TD]
[TD]Component
[/TD]
[TD]BLANK COLUMN
[/TD]
[/TR]
[TR]
[TD]Component
[/TD]
[TD]Part_Name
[/TD]
[TD]Component
[/TD]
[/TR]
[TR]
[TD]NAME_
[/TD]
[TD]Code
[/TD]
[TD]Part_Name
[/TD]
[/TR]
[TR]
[TD]Station
[/TD]
[TD]ROI_Short
[/TD]
[TD]BLANK COLUMN
[/TD]
[/TR]
[TR]
[TD]Zone
[/TD]
[TD][/TD]
[TD]ZONE
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Code
[/TD]
[/TR]
</tbody>[/TABLE]

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!
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,829
Members
452,673
Latest member
LaMiaAvy

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