Reorder columns using a macro

S

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!
 
Sort by date Sort by votes
I took the great work by Alphafrog and added some code that allows you to specify the final order of the columns by using column numbers rather than the text headers. I tested it with a sheet with 41 columns and it seems to work.

Rich (BB code): 
Sub Columns_Reorder()
'---------------------------------------------------------------------------------------------------------------------------------------
' Procedure : Columns_Reorder
' Author      : Agglomerator, An aspiring Excel VBA coder
' Date          : 2013/01/23
' Purpose     : Reorder columns on a sheet using the column numbers as the driver
' Credits      : This code is largely copied from the "Reorder_Columns" routine provided by Excel MVP AlphaFrog, with some tweaks to use column numbers
'                     rather than the actual text of the column headers.  AlphaFrog did all the hard work.
'                     Original code located here - http://www.mrexcel.com/forum/excel-questions/606890-reorder-columns-using-macro.html
' Tip:           : To test this and see that is is working, comment out the last statement where the first row is deleted - you'll then see the column headers displayed in the
'                     order you specified in the varrColOrder array.
'---------------------------------------------------------------------------------------------------------------------------------------


    Dim varrColOrder As Variant
    Dim ndx As Integer
    Dim rFound As Range
    Dim iCounter As Integer
    Dim vColNames As Variant
    Dim X As Integer
'---------------------------------------------------------------------------------------------------------------------------------------
'  Place the existing column numbers in the order you want them to appear after the movement is done.  By using numbers here, you won't have to specify the
'  exact text of the column heading, making it easier.
'
'  This example has 41 columns - change as needed.
'  In this example, I want the original column # 20 to be the 1st column in the reordered sheet, original col 4 to be 2nd, original col 13 the 3rd, etc.
'---------------------------------------------------------------------------------------------------------------------------------------
   
    varrColOrder = Array(20, 13, 4, 3, 5, 7, 6, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 21, 22, 1, 2, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41)
    Sheets("raw_data").Activate                             ' active specific sheet
    
    Cells(1, 1).Select
    ActiveCell.EntireRow.Insert                                 ' insert blank row at the top  - fill with column headings in next step
    GetRowsCols                                                      ' subroutine to get last used row and column and store to iRowNums and iColNums variables
    ReDim vColNames(iColNums)                            ' dimension array to be the number of columns on the page
    
'---------------------------------------------------------------------------------------------------------------------------------------
'  First check to be sure we specified the right number of columns in our input array above (varrColOrder).  The number of elements must be equal to the
'    number of columns on the sheet.
'  It might work to not have the same number of columns in both places, but I did not test that.
'---------------------------------------------------------------------------------------------------------------------------------------
   
    If UBound(varrColOrder) <> iColNums Then
        MsgBox "Routine aborted - number of columns specified not equal to number in the spreadsheet. Check number of columns in input array specified in your VBA code."
        GoTo Abort
    End If
'---------------------------------------------------------------------------------------------------------------------------------------
'  Now insert a temporary row on the top that is the searchable column header - it looks like this - "Column1", "Column2", etc.
'  This allows one to specify the column number order for your desired end state, rather than the specific text of the column header - much shorter.
'---------------------------------------------------------------------------------------------------------------------------------------
    
    For X = 1 To iColNums
          Cells(1, X).Value = "Column" & X                          ' add column header labels to 1st row, can search on these ..."Column1", "Column2"
    Next X
    
    For X = LBound(varrColOrder) To UBound(varrColOrder)
          vColNames(X) = "Column" & varrColOrder(X)          ' load up array with searchable column names "Column1" to "ColumnX" where X is last column number
    Next X
    
    iCounter = 1
    Application.ScreenUpdating = False
    For ndx = LBound(vColNames) To UBound(vColNames)            ' loop thru the array, searching for each column header in Row 1
        
       Set rFound = Rows("1:1").Find(vColNames(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not rFound Is Nothing Then                                        ' if the header (i.e. "Column1" is found, then arrange it.
            If rFound.Column <> iCounter Then
                rFound.EntireColumn.Cut
                Columns(iCounter).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
                iCounter = iCounter + 1
            End If
    Next ndx
    
    Application.ScreenUpdating = True
    
'---------------------------------------------------------------------------------------------------------------------------------------
'  Delete temporary row 1, and we are done
'---------------------------------------------------------------------------------------------------------------------------------------
   Rows(1).Delete
   
Abort:


End Sub
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
agglomerator said:
I took the great work by Alphafrog and added some code that allows you to specify the final order of the columns by using column numbers rather than the text headers. I tested it with a sheet with 41 columns and it seems to work.
Click to expand...

Nice code.

Another method could be to insert a row at the top, populate that row with the numbers in the order you want the columns ordered e.g. column 20 gets 1, column 13 gets a 2...etc, then use Excel's Sort feature to reordder the columns based on row 1.

Code: 
[COLOR=darkblue]Sub[/COLOR] Reorder_Columns_via_Numbers()
    
    [COLOR=darkblue]Dim[/COLOR] arrColOrder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Place the column Number in the end result order you want.[/COLOR]
    arrColOrder = Array(20, 13, 4, 3, 5, 7, 6, 8, 9, 10, _
                        11, 12, 14, 15, 16, 17, 18, 19, 21, 22, _
                        1, 2, 23, 24, 25, 26, 27, 28, 29, 30, _
                        31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41)
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    Rows(1).Insert
    Rows(1).NumberFormat = "@"
    For i = 0 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrColOrder)
        Cells(1, arrColOrder(i)).Value = Format(i + 1, "'0000")
    [COLOR=darkblue]Next[/COLOR] i
    
    Columns("A:A").Resize(, [COLOR=darkblue]UBound[/COLOR](arrColOrder)).Sort Key1:=Range("A1"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    
    Rows(1).Delete
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Upvote 0
Can i get some more explaiantion on how to implement this?
New to macros all together.
I have built a very large excel sheet 35 MB that consists of about 80 grapahs over 3 sheets. All the data that feeds the graphs comes from 3 different sheets within the workbook.

All was fine and dandy untill the equipment which outputs the data was updated and now the format all needs to be reordered. So the data i have to work with is now in differnt columns than my graphing work book. I would like to create a macro that takes the new formated data and re-orders the columns into my old order. or better yet, update all the graphs to the new column order.

Thank you.




AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] Reorder_Columns()
    
    [COLOR=darkblue]Dim[/COLOR] arrColOrder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], ndx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Found [COLOR=darkblue]As[/COLOR] Range, counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    [COLOR=green]'Place the column headers in the end result order you want.[/COLOR]
    arrColOrder = Array("COLUMN2", "COLUMN4", "COLUMN6", "COLUMN10", "COLUMN1", _
                        "COLUMN9", "COLUMN3", "COLUMN8", "COLUMN7", "COLUMN5")
    
    counter = 1
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] ndx = [COLOR=darkblue]LBound[/COLOR](arrColOrder) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrColOrder)
    
        [COLOR=darkblue]Set[/COLOR] Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Found.Column <> counter [COLOR=darkblue]Then[/COLOR]
                Found.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            counter = counter + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] ndx
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...
 
Upvote 0
GabeMarciano said:
Can i get some more explaiantion on how to implement this?
New to macros all together.
I have built a very large excel sheet 35 MB that consists of about 80 grapahs over 3 sheets. All the data that feeds the graphs comes from 3 different sheets within the workbook.

All was fine and dandy untill the equipment which outputs the data was updated and now the format all needs to be reordered. So the data i have to work with is now in differnt columns than my graphing work book. I would like to create a macro that takes the new formated data and re-orders the columns into my old order. or better yet, update all the graphs to the new column order.

Thank you.
Click to expand...

If, in your case, your new data format is permanent, you probably should just bite the bullet and edit your 80 charts to accommodate the new format (how to do that goes beyond the scope of this thread). In the long term, that may be easier than having to reorder the columns for each new data feed.

If you need to learn how to paste and run a macro, do a simple web search. There are countless videos and guides on how that's done.

The only part of the macro to customize is this line of code. Place your column headers in the end result order you want them sorted...
Code: 
    [COLOR=green]'Place the column headers in the end result order you want.[/COLOR]
    arrColOrder = Array("COLUMN2", "COLUMN4", "COLUMN6", "COLUMN10", "COLUMN1", _
                        "COLUMN9", "COLUMN3", "COLUMN8", "COLUMN7", "COLUMN5")
 
Upvote 0
An after thought; If you use this alternate macro to sort your columns, you may (not guaranteed) only have run this one time. This code may have a beneficial side effect of permanently changing your charts to accept the new data format.

This code uses the sort method on the columns (the other code uses a cut\paste method). If the columns are re-sorted, Excel may automatically update your chart references. So on the next data feed, you may not have to reorder the columns as the chart references have been previously changed. Again, this may not work. It depends on how the charts and workbook are setup.

Test this on a copy of your workbook.

Edit the column numbers in arrColOrder in the order you want them in the end result

After running the macro the one time, you have to refresh the data feed to see if the charts accepted the new data format.

Code: 
[COLOR=darkblue]Sub[/COLOR] Reorder_Columns_via_Numbers()
    
    [COLOR=darkblue]Dim[/COLOR] arrColOrder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]'Place the column Number in the end result order you want.[/COLOR]
[B]    arrColOrder = Array(20, 13, 4, 3, 5, 7, 6, 8, 9, 10, _
                        11, 12, 14, 15, 16, 17, 18, 19, 21, 22, _
                        1, 2, 23, 24, 25, 26, 27, 28, 29, 30, _
                        31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41)[/B]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    Rows(1).Insert
    Rows(1).NumberFormat = "@"
    For i = 0 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrColOrder)
        Cells(1, arrColOrder(i)).Value = Format(i + 1, "'0000")
    [COLOR=darkblue]Next[/COLOR] i
    
    Columns("A:A").Resize(, [COLOR=darkblue]UBound[/COLOR](arrColOrder)).Sort Key1:=Range("A1"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
    
    Rows(1).Delete
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
 
Last edited:
Upvote 0
Thanks for the replys. I tried using the sort function by settings a new row on top, and populating it with the new order. After I set it up however, it did not reorder the Target data in the graphs. I have even tried removing the $ sign from the referenced cells, still no luck, so I'm guessing sorting will not work. Cut and paste will work, I tested it. So it may still be worth investigating the cut and paste reorder macro. I'm running out of time however. I may just have to bite the bullet and manually cut and paste each col. Using my ordered labels as a guide. there are 5 sheets that I am pulling data from and each will have 237 columns of data. I want to know how to use a Marco to make it easy and fast. I long for Marco building knowledge... You guys who can make them amaze me.
 
Upvote 0
You're welcome.

GabeMarciano said:
I tried using the sort function by settings a new row on top, and populating it with the new order. After I set it up however, it did not reorder the Target data in the graphs. I have even tried removing the $ sign from the referenced cells, still no luck, so I'm guessing sorting will not work.
Click to expand...

I didn't understand this at all. The Reorder_Columns_via_Number macro doesn't use column headers and what $ signs are you referring to?
 
Last edited:
Upvote 0
AlphaFrog said:
Code: 
[COLOR=darkblue]Sub[/COLOR] Reorder_Columns()
    
    [COLOR=darkblue]Dim[/COLOR] arrColOrder [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], ndx [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Found [COLOR=darkblue]As[/COLOR] Range, counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    [COLOR=green]'Place the column headers in the end result order you want.[/COLOR]
    arrColOrder = Array("COLUMN2", "COLUMN4", "COLUMN6", "COLUMN10", "COLUMN1", _
                        "COLUMN9", "COLUMN3", "COLUMN8", "COLUMN7", "COLUMN5")
    
    counter = 1
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] ndx = [COLOR=darkblue]LBound[/COLOR](arrColOrder) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrColOrder)
    
        [COLOR=darkblue]Set[/COLOR] Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Found [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Found.Column <> counter [COLOR=darkblue]Then[/COLOR]
                Found.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            counter = counter + 1
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] ndx
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...


just used this code for a similar problem I was having and worked like a charm, thanks alot!
 
Upvote 0
AlphaFrog said:
Code: 
[color=darkblue]Sub[/color] Reorder_Columns()
    
    [color=darkblue]Dim[/color] arrColOrder [color=darkblue]As[/color] [color=darkblue]Variant[/color], ndx [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    [color=darkblue]Dim[/color] Found [color=darkblue]As[/color] Range, counter [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    
    [color=green]'Place the column headers in the end result order you want.[/color]
    arrColOrder = Array("COLUMN2", "COLUMN4", "COLUMN6", "COLUMN10", "COLUMN1", _
                        "COLUMN9", "COLUMN3", "COLUMN8", "COLUMN7", "COLUMN5")
    
    counter = 1
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] ndx = [color=darkblue]LBound[/color](arrColOrder) [color=darkblue]To[/color] [color=darkblue]UBound[/color](arrColOrder)
    
        [color=darkblue]Set[/color] Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Found [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Found.Column <> counter [color=darkblue]Then[/color]
                Found.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = [color=darkblue]False[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            counter = counter + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
    [color=darkblue]Next[/color] ndx
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
Click to expand...

This works awesome
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
854
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
608
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
358
esben
E

Forum statistics

Threads
1,215,636
Messages
6,125,959
Members
449,276
Latest member
surendra75

Share this page

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