Skip to content

Rearrange Excel columns via Visual Basic

A simple VBA script for rearranging the order of columns in an Excel spreadsheet.

In the following worksheet example, the columns are arranged in the order Header 1, Header 2, Header 3, Header 4, Header 5 and Header 6.

Screen shot of Excel.

An Excel spreadsheet for which the columns need to be rearranged.

The following Visual Basic for Applications (VBA) script will rearrange them in the order Header 6, Header 2, Header 1, Header 4, Header 5 and Header 3. Simply change the parameters in the code to suit the spreadsheet that you’re working on. Unlisted or missing columns will be ignored.

Sub Reorder_Columns()
Dim ColumnOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
	ColumnOrder = Array("Header 6", "Header 2", "Header 1", "Header 4", "Header 5", "Header 3")  
counter = 1

Application.ScreenUpdating = False
   
For ndx = LBound(ColumnOrder) To UBound(ColumnOrder)   
    Set Found = Rows("1:1").Find(ColumnOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not Found Is Nothing Then
        If Found.Column <> counter Then
            Found.EntireColumn.Cut
            Columns(counter).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
    counter = counter + 1
    End If
Next ndx
Application.ScreenUpdating = True
End Sub

The result of this script is as shown:

Screen shot of Excel

Columns rearranged in an Excel spreadsheet

Activating the script

Step 1

Open the Excel sheet that needs to be modified.

Step 2

Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.

Step 3

In the Visual Basic for Applications toolbar, go to Insert > Module

Screen capture of Visial Basic for Applications

Inserting a module in Visual Basic for Applications.


Insert the above code into the module.

Step 4

Press F5 to run the macro and execute the code.

   

Comments:

15 responses to “Rearrange Excel columns via Visual Basic”

  • Written by Michael Pluck on 10 May 2017:

    Hmmm. For some reason, when I try to run the code I am getting a compile error and my column names are in red text. The only thing that I changed in the above code is my column names. Does the name of my spreadsheet or workbook matter?

  • Written by Michael Pluck on 10 May 2017:

    Never mind. I changed my column names to header 1, etc. and it worked. Thank you very much for the code.

    If you are going to publish my comments, please replace my last name to the initial “P.”

    • Written by Adam Dimech on 31 July 2017:

      Hello Michael, please refer to the Editorial Policy. Comments will be published as they’re received. If you don’t wish for your surname to be published then don’t supply it.

  • Written by RobertSF on 23 June 2017:

    Thank you! This was a great example. It was easy to understand and was just what I was looking for.

  • Written by Nagesh on 9 March 2018:

    Working Great, thank you so much:)

  • Written by Praveen on 12 April 2018:

    Thanks a lot,Really Helfulll

  • Written by Kaushik shetty on 1 June 2018:

    Hello sir , this code helped me lot ,it reduced my 30 min of Daly work .

  • Written by NACHO on 14 June 2018:

    Thanks! Saved me a lot of time.

  • Written by Prafull on 17 July 2018:

    Thank you very much!

  • Written by KevinYen on 4 September 2018:

    It really helps! Thank you:D

  • Written by KevinYen on 6 September 2018:

    if some of those columns have a same name, ex: Header 1,Header 2, Header 2, Header 2, Header 3, Header 4, Header 5, Header 6 , “SearchDirection:=xlNext” may have a problem. I think “SearchDirection:=xlPrevious” is better.

  • Written by Carol on 11 September 2018:

    This worked for all the columns that were formatted as text. However, it ignore the columns that were formatted as Number (Accounting). Can you please add/include the Number format in the script.

  • Written by ABSExcel on 6 June 2019:

    Really Nice code!!!

  • Written by natalie on 8 July 2019:

    Works great, but I get a sytax error, I’m wanting to rearrange 100 – 120 columns, is there a limit?

    Thanks!

  • Written by Dylan on 4 April 2020:

    Hi Adam,

    Great code, thank you! Can you describe how to rename the columns after moving, please?

    Best,
    Dylan

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.