WISDOMBYDATA
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me

Reorder columns based on header names in MS Excel

12/31/2018

0 Comments

 

In my previous blog post I demonstrated how to transpose data from one workbook to another based on matching header names. Today, I want to illustrate how to reorder columns (within the same workbook) based on header names.  I will use the same date set as last week.
Picture

​The goal is to reorder columns alphabetically based on the header names.

STEP 1] Copy all header names and sort them in the desired order (i.e. from A to Z). 
Picture
STEP 2] Use the site below to generate a comma-separated string of the header names.

​https://delim.co/#

Picture

​STEP 3]
Copy and paste the string created in step 2 in the 6th line of the code below (between the parenthesis ().

Sub Reorder_Columns()
    Dim ColumnOrder As Variant, DR As Integer
    Dim Found As Range, counter As Integer
   
    'Place the column headers in the end result order you want.
    ColumnOrder=Array
("Date","Audio Equipment","Beauty Supplies","Cameras","Footwear","Furniture","Jewelry","Luggage","Petcare","Smartphones","Sportswear","Swimsuits","Womenswear")

    counter = 1
    Application.ScreenUpdating = False
       For DR = LBound(ColumnOrder) To UBound(ColumnOrder)
           Set Found = Rows("1:1").Find(ColumnOrder(DR), 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 DR
       Application.ScreenUpdating = True
  End Sub


STEP 4] Copy and paste the code above into the VBA editor. Then, run the macro. 

Picture

​Here is the desired outcome with columns reordered in alphabetical sequence.
Picture
0 Comments



Leave a Reply.

    Categories

    All
    BI
    EXCEL
    MISC
    SQL

    Archives

    June 2020
    May 2020
    April 2020
    March 2020
    February 2020
    December 2019
    November 2019
    October 2019
    September 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    January 2019
    December 2018
    November 2018
    October 2018
    September 2018
    August 2018
    July 2018
    June 2018
    May 2018
    April 2018
    March 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    May 2015
    April 2015
    March 2015
    February 2015
    January 2015
    December 2014
    November 2014
    October 2014
    September 2014
    August 2014
    April 2014
    March 2014
    February 2014
    January 2014
    December 2013
    November 2013

Powered by Create your own unique website with customizable templates.
  • BLOG
    • Blog Guide
    • Blog History
  • EXCEL
    • Functions & Formulas
    • VBA & Macros
    • VLOOKUP
    • Pivot Tables
    • Conditional Formatting
    • Tricks & Shortcuts
  • BI
    • SAP BOBJ/BW
    • Tableau
  • SQL
  • ABOUT
    • About WBD
    • About Me