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.
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 SubThe result of this script is as shown:
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
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”
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?
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.”
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.
Thank you! This was a great example. It was easy to understand and was just what I was looking for.
Working Great, thank you so much:)
Thanks a lot,Really Helfulll
Hello sir , this code helped me lot ,it reduced my 30 min of Daly work .
Thanks! Saved me a lot of time.
Thank you very much!
It really helps! Thank you:D
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.
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.
Really Nice code!!!
Works great, but I get a sytax error, I’m wanting to rearrange 100 – 120 columns, is there a limit?
Thanks!
Hi Adam,
Great code, thank you! Can you describe how to rename the columns after moving, please?
Best,
Dylan