I have a macro (I do not recall where I got this) that Rearranges the order of columns on a sheet which can have hundreds of columns by an array of header names
So if the array of headers is
Array("From", "To", "Name From", "Name To")
then my sheet would look like
front.png
But I often need to place the columns at the END of the sheet
So if the array of headers is
Array("From", "To", "Name From", "Name To")
then my sheet would look like
reverse.png
But I can not figure out how to alter the Sub and/or function to do this
Thank you in advance for your help with this
(I have a macro that does this with pasting and cutting but is extremely slow on sheets with lots of columns)
Sub MoveRangeBycolOrder()
Dim colOrder(), ws As Worksheet
Set ws = ThisWorkbook.Sheets("Helper_1Filted")
With ws
Dim rng As Range, lastRow&, lastCol&
lastRow = .Cells(.Rows.count, 1).End(xlUp).row
lastCol = .Cells(1, .Columns.count).End(xlToLeft).Column
Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
colOrder = Array("From", "To", "Name From", "Name To")
' ~~~~~~~~~~~~~~~
' Get data array
' ~~~~~~~~~~~~~~~
Dim v: v = rng
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Restructure column order in array in a one-liner
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
v = Application.Index(v, Evaluate("row(1:" & lastRow & ")"), getColNums(ws.Name, v, colOrder, False))
rng = vbNullString
.Range("A1").Resize(UBound(v), UBound(v, 2)) = v
End With
End Sub
' Note: if argument DeleteRest (default: False) is passed as True, each unlisted titles will be removed
Function getColNums(shtName, arr, colOrdr(), Optional ByVal DeleteRest As Boolean = False) As Variant()
Dim titles
titles = Application.Transpose(Application.Transpose(Application.Index(arr, 1, 0)))
Dim rest: rest = titles
Dim i&, ii&, pos ' array counters, element position
ReDim tmp(0 To UBound(colOrdr) + UBound(titles) + 2) ' temporary array to collect found positions
' a) find position in
For i = 0 To UBound(colOrdr) ' loop through titles in wanted order
pos = Application.Match(colOrdr(i), titles, 0) ' check positions
If Not IsError(pos) Then
tmp(ii) = pos: ii = ii + 1 ' remember found positions, increment counter
rest = filter(rest, colOrdr(i), False, vbTextCompare)
End If
Next i
' b) Default: ~~~> don't remove unlisted titles <~~~ ' << inserted code block as of 2020-05-15 >>
If Not DeleteRest Then
For i = 0 To UBound(rest)
pos = Application.Match(rest(i), titles, 0) ' check positions
If Not IsError(pos) Then
tmp(ii) = pos: ii = ii + 1
End If
Next i
End If
ReDim Preserve tmp(0 To ii - 1) ' remove empty elements
getColNums = tmp ' return array with current column numbers (1-based)
End Function
Bookmarks