Hello all! I'm looking for some VBA code to help me put my columns in a specific order. Here is the catch I need to do it by the column's name (first row values), not by the letter. I have code that will work but I've ran into some difficulty. I'll try my best to explain, from the code below:
"CAT", "DOG ", "MOUSE", "COMPUTER MONITOR"
are my column names. I want them in that order, however the columns were pasted in from another program making the " MONITOR" with the space included in "COMPUTER MONITOR" unreadable in the code.
Is there a way I can ignore " MONITOR" in the array and still reorder the column with the name "COMPUTER MONITOR". Also the same with DOG, can I ignore the spaces in front of it and still derive the "DOG " with the spaces? Sorry if this isn't making any sense. I think what I am looking for is a wildcard.
Sub RearrangeColumns() Dim orderedHeadings() Dim i As Long Dim columnMatch As Long orderedHeadings = Array("Cat", "Dog ", "Mouse", "Computer Monitor") With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With For i = UBound(orderedHeadings) To LBound(orderedHeadings) Step -1 columnMatch = 0 On Error Resume Next columnMatch = Application.Match(orderedHeadings(i), Range("1:1"), 0) On Error GoTo 0 If columnMatch > 0 Then Columns(columnMatch).Cut Columns(1).Insert Else Debug.Print "Missing column: " & orderedHeadings(i) End If Next i With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub
All help is appreciated!