0

I got this VBA macro from here it reorders columns from an array of column headers even if there are hundreds of columns in a moment (at least in excel)

from this part:

x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False

If Before:=false then the columns are reordered to the end of the range, and if true then reordered to the beginning of the range

If RemoveRest:=true then only the columns listed in the array of headers to be reordered myOrder = Array("From", "To", "Name From", "Name To") will be outputed

I want to use it as a Google script (I am using Googles V8)

Sub ReOrderColumns()
    Dim a, x, rng As Range, myOrder, ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Helper_1Filted")
    Set rng = ws.Cells(1).CurrentRegion
    a = rng.Value
    myOrder = ("Finished", "Progress", "StartDate", "Status", "EndDate")
    
    x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False)  '<-- change bold part as per your need.
    
    Application.ScreenUpdating = False
    With rng
        x = Application.Index(.Value, Evaluate("row(1:" & rng.Rows.Count & ")"), x)
        .ClearContents
        .Resize(, UBound(x, 2)).Value = x
        .Columns.AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

Function GetOrder(rng As Range, myOrder, RemoveRest As Boolean, Optional Before As Boolean = True)
    Dim a, x, i As Long, ii As Long, ub As Long, myList, n As Long, t As Long
    ReDim myList(1 To 2, 1 To rng.Columns.count)
    myList = rng.Rows(1)
    With Application
        x = filter(.IfError(.Match(myOrder, .Index(myList, 1, 0), 0), False), False, 0)
        If RemoveRest Then
            GetOrder = x
        Else
            ReDim a(1 To UBound(myList, 2))
            ub = UBound(x): ReDim x(1 To rng.Columns.count)
            For i = 1 To UBound(myList, 2)
                x = .Match(myList(1, i), myOrder, 0)
                If IsNumeric(x) Then

                   If Before = False Then
                      a(x + IIf(Before, 0, UBound(myList, 2) - ub - 1)) = i
                   Else
                     a(x + IIf(Before, 0, ub)) = i
                   End If

                Else
                    n = n + 1
                    a(n + IIf(Before, ub + 1, 0)) = i
                End If
            Next
             GetOrder = a
        End If
    End With
End Function

This is my attempt to translate the first part

function ReOrderColumns() {
  const ss  = SpreadsheetApp.getActiveSpreadsheet();
  const sht = ss.getSheetByName("Elements");
  const rng = sht.getDataRange();
  var a     = rng.getDisplayValues();

  //Array of column headers to reordoer
  const myOrder = ["Finished", "Progress", "StartDate", "Status", "EndDate"]
  
  var x = GetOrder(rng, myOrder, RemoveRest:=False, Before:=False)  //<-- change bold part as per your need.
  x = Application.Index(rng.Value, Evaluate("row(1:" & rng.Rows.Count & ")"), x)  //<-- Do not now what to do with Evaluate

  sht.Clear()
  rng.setValues(x)
}

Thanks in advance for any assistance with this

Here is a Google Sheet with data if helpful https://docs.google.com/spreadsheets/d/1xmIs6Aur-1ruI3iPZeJvvQXhhA4j8WowP44-tr-dh90/edit?usp=sharing

Einarr
  • 453
  • 2
  • 11
  • 1
    You need to tell us where exactly your issue is. What went wrong? Where did you gets stuck? And you need to ask a question. If `Evaluate` is your issue, it [does not exist in Google Script](https://stackoverflow.com/a/26854331). Note that `Evaluate("row(1:" & rng.Rows.Count & ")")` can be written in VBA as `Rows("1:" & rng.Rows.Count).Value` maybe that helps. I don't see why `Evaluate` was used at all here. – Pᴇʜ Jun 08 '21 at 14:19

0 Answers0