How to Reverse the Order of a Selected Range of Excel Columns using VBA

Question

I am new to VBA and am trying to figure out how to reverse the order of a selected range of columns without hard coding. Every example I look out either hard codes the columns to reverse the order of, or assumes you want to reverse all the columns in the worksheet. I'm not trying to ask someone to write this for me, but as simple as this should be, as soon as someone explains what I am missing, I should be OK.

Does anyone have any advice?

Answer 1

From here: Flipping Or Mirroring A Range:

This macro will reverse the order of a range of data. You may flip data in a single row or in a single column of data (i.e., an N by 1 array or an 1 by N array). You may not select and entire row or an entire column.

Public Sub FlipSelection()

Dim Arr() As Variant
Dim Rng As Range
Dim C As Range
Dim Rw As Long
Dim Cl As Long

On Error GoTo EndMacro

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Set Rng = Selection
Rw = Selection.Rows.Count
Cl = Selection.Columns.Count
If Rw > 1 And Cl > 1 Then
   MsgBox "Selection May Include Only 1 Row or 1 Column", _
    vbExclamation, "Flip Selection"
Exit Sub
End If

If Rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
    MsgBox "You May Not Select An Entire Row", vbExclamation, _
        "Flip Selection"
    Exit Sub
End If
If Rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
    MsgBox "You May Not Select An Entire Column", vbExclamation, _
        "Flip Selection"
    Exit Sub
End If

If Rw > 1 Then
    ReDim Arr(Rw)
Else
    ReDim Arr(Cl)
End If

Rw = 0
For Each C In Rng
    Arr(Rw) = C.Formula
    Rw = Rw + 1
Next C

Rw = Rw - 1
For Each C In Rng
    C.Formula = Arr(Rw)
    Rw = Rw - 1
Next C

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Answer 2

Selection.Columns(Selection.Columns.Count).Column - gives you the ending column number
Selection.Columns(1).Column - gives you the starting column number

You can do a reverse for loop using the above.

Answer 3

Record a macro with relative cell references then examine the code. I do this whenever I am feeling too lazy to look up some functionality (because, lets face it, the documentation from MS is getting worse and worse for VBA).

Answer 4

this code flips a whole range horizontally, one row at a time is a pain. enjoy

Sub FlipHorizontal()

On Error GoTo EndMacro

Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False

Set Rng = Selection rw = Selection.Rows.Count cl = Selection.Columns.Count 'If Rw > 1 And cl > 1 Then ' MsgBox "Selection May Include Only 1 Row or 1 Column", _ ' vbExclamation, "Flip Selection" 'Exit Sub 'End If

If Rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then MsgBox "You May Not Select An Entire Row", vbExclamation, _ "Flip Selection" Exit Sub End If If Rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then MsgBox "You May Not Select An Entire Column", vbExclamation, _ "Flip Selection" Exit Sub End If

'If Rw > 1 Then ' ReDim Arr(Rw) 'Else ' ReDim Arr(cl) 'End If Stop ReDim Arr(rw, cl)

For cc = 1 To cl ' = Rng.Columns.Count For rr = 1 To rw 'rr = Rng.Rows.Count Arr(rr, cc) = Rng.Cells(rr, cc) '.Formula a = Arr(rr, cc) Next

Next Stop 'copy arry to range flippingnhorizontal cc = cl For a = 1 To cl ' to loop the columns

For rr = 1 To rw 'rr = Rng.Rows.Count
Rng.Cells(rr, cc) = Arr(rr, a) '=  .Formula

Next cc = cc - 1 Next

EndMacro: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True

End Sub

Answer 5

You can use sort to do this.

Using this method, you can arrange the columns any way you want.

If you have data that looks like this:

    A       B       C
1   header1 header2 header3
2   dataA   dataE   dataI
3   dataB   dataF   dataJ
4   dataC   dataG   dataK
5   dataD   dataH   dataL

Add some numbers at the first row:

    A       B       C
1   1       2       3      
2   header1 header2 header3
3   dataA   dataE   dataI
4   dataB   dataF   dataJ
5   dataC   dataG   dataK
6   dataD   dataH   dataL

Then use sort, with no header rows, and then choose the option to sort left to right instead of top to bottom.

Sorting by row 1, descending:

    A       B       C
1   3       2       1
2   header3 header2 header1
3   dataI   dataE   dataA
4   dataJ   dataF   dataB
5   dataK   dataG   dataC
6   dataL   dataH   dataD

If you want to script this, use the option to sort by rows instead of by columns.

I wrote a .vbs that does this here:

http://gallery.technet.microsoft.com/ScriptCenter/en-us/f6085342-a1ae-49d8-acfc-38368256ee42?lc=1033