Joseph Michael Pesch
VP Programming

Excel Macro to Re-Order Columns Based on Comma Delimited List

by 13. January 2010 18:24

This script will take the comma delimited values in the list variable, search for those values in the header row and reorder the columns in the corresponding order as the list.

Sub FormatFile()
    Dim iColTarget As Integer
    iColTarget = 0
    Dim iColSource As Integer
    Dim list As String
    list = "Date,Time,DNIS,ANI,Call Type,Duration,Hold Time,Handle Time,Rate,Cost,Disposition,Campaign,Agent,Comments,Number1,Number2,Number3,First_Name,Last_Name,Company,Street,City,State,Zip,Language,Impac Loan Number,Email Address,MailZip,MailAddress,MailCity,MailState,ContactComments"
    For Each listval In Split(list, ",")
      iColTarget = iColTarget + 1
      Rows(1).Select 'Select header row to search
      Cells.Find(What:=listval, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
      iColSource = ActiveCell.Column
      Debug.Print listval & ": is located in column: " & iColSource & "; moving to column: " & iColTarget
      If iColSource <> iColTarget Then
        Columns(iColSource).Select
        Selection.Cut
        Columns(iColTarget).Select
        Selection.Insert Shift:=xlToRight
      End If
    Next
End Sub

Tags: ,

[None]

Comments are closed