Easier Array to arrange columns VBA/Macro

[SendHalp3]

Hi all!

I have a code to sort columns of data based on the value in row1 of the column. Its a fairly large code and I was wondering if theres an easier version? Basically I want it to sort 1-200 in ascending value, with X values moved to the end. This macro is run using a shape/button.

Thank you!

Here is the code I currently have:


Sub Reorder_Columns()
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
arrColOrder = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", _
, "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", _
, "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", _
, "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", _
, "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", _
, "101", "102", "103", "104", "105", "106", "107", "108", "109", "110", "111", "112", "113", "114", "115", "116", "117", "118", "119", "120", _
, "121", "122", "123", "124", "125", "126", "127", "128", "129", "130", "131", "132", "133", "134", "135", "136", "137", "138", "139", "140", _
, "141", "142", "143", "144", "145", "146", "147", "148", "149", "150", "151", "152", "153", "154", "155", "156", "157", "158", "159", "160", _
, "161", "162", "163", "164", "165", "166", "167", "168", "169", "170", "171", "172", "173", "174", "175", "176", "177", "178", "179", "180", _
, "181", "182", "183", "184", "185", "186", "187", "188", "189", "190", "191", "192", "193", "194", "195", "196", "197", "198", "199", "200", _
, "x", "X")
counter = 1
Application.ScreenUpdating = False
For ndx = LBound(arrColOrder) To UBound(arrColOrder)
Set Found = Rows("1:1").Find(arrColOrder(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

 

[SendHalp3]

**This macro runs super slow and takes a couple minutes each time I use it so I wanted to see if there was a way to make it quicker without deleting column #s.... Thank you again!

 

[Akuini]

Actually you can do "sort left to right".
Select the range then on the Sort menu choose Custom sort > Option > sort left to right > choose Row 1.
To get the macro you can try using macro recorder & then adjust the macro as needed.

 

[SendHalp3]

And that would move the whole column associated, not just the numbers in that particular row?

 

[Akuini]

Yes, providing you have selected all row.