Whoever wrote that line must have laughed like a mad scientist when they were finished. It combines numerous functions and methods to perform an entire sub worth of code in a single line. I will attempt to explain each step.
Range("A1") : Starting with Cell A1 so the range can extend from this top corner to grab the rest of the sheet.
.Resize( : This is the property that will stretch Range("A1") from a single cell to encompass the targetted area. Resize has two arguments, Rows and Columns, that decide the size of the final range.
Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row : This .Find method searches in Cells which is all cells in the sheet. It is searching for * which means anything. It specifies xlPrevious which means it is searching backwards. But since it is searching all cells, it is searching from the bottom right to top left. It is a quick way to find the bottom row of the sheet.
.Row : This grabs the row # from the result of the .Find which will be the bottom row of the sheet.
UBound(newColumnOrder) + 1 : This counts the number of items inside the array newColumnOrder. Since arrays defined using Array() start at 0, you need to add one to the upper bound to get the count.
Resize( : Going back to this property now that we have understood the input arguements, we are resizing Range("A1") to extend from "A1" to a number of columns equal to the size of the array newColumnOrder and enough rows to encompass all non-blank cells of the sheet.
= Application.Index( : The index function returns a single value, but because we are assigning this value to a multi-cell range, VBA automatically interprets this as "Do this for every cell in the range". VBA is also smart when applying formula values into ranges, automatically adjusting the references to follow the current cell's address. This is how the function "iterates" through the array newColumnOrder without actually looping.
Cells : The first argument of Index. This is where Excel will grab the values from. Cells is all cells on the sheet. Notably, since Cells also starts from "A1" there is no offset between this and our target range. If you replace this with a different range, make sure to account for the offset with the other two arguments of Index.
Evaluate( : This second argument of the Index function is the row# of the value to be returned from the range defined in the first argument. The Evaluate method asks VBA to interpret the input expression similarly to how an Excel sheet evaluates input formulas/references. This is used to turn the input string which is an Excel formula into an array of values.
"ROW(1:" __ ")" : This is the ROW function from Excel which returns the row # from the input reference. Since the input is a range instead of a single reference, we get an array of values corresponding to the ROW # of each cell in the range. As VBA fills our target range with results from the Index function, it will automatically "iterate" through this array. This will input into Index a row# corresponding to each cells current row# since there is no offset between the filled target range and the Indexed range.
Cells.Find("*", , xlFormulas, , xlRows, xlPrevious).Row : Same as above, this is the row # of the bottommost cell on the sheet.
newColumnOrder : The third argument of Index is asking for the Column #. Instead of inputting a single value, we are inputting an entire array. Similarly to how cell references automatically shift when you Autofill a formula across a range, VBA iterates through the array as it fills in our range, and in doing so grabs the column numbers directly from it. So the first column of our target range will fill from the column # in the first element of the array, the second column will fill from the column # in the second element of the array.
Conclusion:
This line says "Into a range starting from A1 stretching to the size of the array newColumnOrder and to the last filled row of the sheet. Re-fill each cell with the value of a cell in the same row but whose column # can be found in the array newColumnOrder with index of the array being your current column number. "
To answer your second question about how to write this for a different table, I suggest that you don't write code like this. While it might be a code golf winner as fewest lines to accomplish the task, it is cryptic and hard to read or edit. Instead I would suggest code similar to the following:
Sub ReOrderingColumnsExample()
'Define the desired column order
Dim newColumnOrder() As Variant
newColumnOrder = Array(1, 3, 5, 2, 4, 6)
'Define the output range, manually or dynamically
Dim OutputRange As Range
Set OutputRange = Sheet1.Range("A1:F20")
'Define the input range, manually or dynamically
Dim InputRange As Range
Set InputRange = Sheet1.Range("R34:W53")
'Create an array to hold the columns
Dim ColArrs() As Variant
ReDim ColArrs(1 To InputRange.Columns.Count)
'Fill the array with arrays of each column's values
Dim i As Long
For i = 1 To InputRange.Columns.Count
ColArrs(i) = InputRange.Columns(i).Value
Next i
'Output the arrays into the output range with the column based on newColumnOrder
For i = 1 To OutputRange.Columns.Count
OutputRange.Columns(i) = ColArrs(newColumnOrder(i - 1))
Next i
End Sub
Significantly less confusing and all of the pieces are easily editable to fit your specific parameters.
