CODEWITHSUNDEEP
excelvba
a_js12
a_js12

Reputation: 329

Reversing a Referenced Range Order in VBA

I'm trying to transfer data from workbook x into workbook y. I want to copy a horizontal range from workbook x, transpose and reverse the range and then paste the values in workbook y. I would it to look like this:

Workbook x

ColumnX ColumnXx ColumnXy ColumnXz
9 8 10 7

Workbook y

Column
7
10
8
9

So far, I can transpose the values but I've been searching how to reverse the range and haven't found any solutions using VBA. My current code:

Dim x As Workbook
Dim y As Workbook
Dim vals As Variant


Set y= ThisWorkbook
Set x= Workbooks.Open("FileName.xls", CorruptLoad:=XlCorruptLoad.xlRepairFile)

vals = x.Sheets("SheetSource").Range("G10:C10").Value

y.Sheets("Sheet1").Range("G8:G12").Value = WorksheetFunction.Transpose(vals)

Upvotes: 0

Views: 376

Answers (1)

T.M.
T.M.

Reputation: 9948

Approach using Application.Index() function

The Application.Index() function allows to rearrange columns by passing an array of column numbers in the wanted order (here in reversed order 5, 4, 3, 2, 1) - c.f. section [1] and [2].

Eventually the transposed data are written to a predefined target - c.f. [3]

Sub ExampleCall()
    'Source declaration following untested OP
    Dim src As Workbook
    Set src= Workbooks.Open("FileName.xls", CorruptLoad:=XlCorruptLoad.xlRepairFile)
    'Define target range's top cell
    Dim tgt As Range: Set tgt = Sheet1.Range("G8")  ' reference target via project's Code(Name)

    With src.Worksheets("SheetSource")
        '[0] get data
        Dim vals: vals = .Range("C10:G10").Value2       ' don't reverse addresses in code line!
        '[1] get new order (array of reversed column numbers)
        Dim cols As Long: cols = UBound(vals, 2): Dim newOrder: ReDim newOrder(1 To cols)
        Dim col As Long:  For col = 1 To cols: newOrder(col) = cols - col + 1: Next col
        '[2] reverse columns in data array
        vals = Application.Index(vals, 1, newOrder)
        
        '[3] write transposed data to target  
        tgt.Resize(UBound(vals), 1) = Application.Transpose(vals)
        
    End With
End Sub

Further reading

Some pecularities of the `Application.Index() function

Upvotes: 1

Related Questions