CODEWITHSUNDEEP
excelvba
Lamasil
Lamasil

Reputation:

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

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?

Upvotes: 9

Views: 55732

Answers (6)

Nate F
Nate F

Reputation: 41

I believe what you want to do looks something like this:

A1 B1 C1 D1 E1 | E1 D1 C1 B1 A1

A2 B2 C2 D2 E2 | E2 D2 C2 B2 A2

If that's what you're trying to do, try this worksheet function:

=INDEX($A$1:$B$5,ROW(A1),6-COLUMN(A1))

Where 6 is 1 + the number of columns in the range you want to reverse. There's probably a better way to do that part, but I was going for simple here.

Put that function in cell F1 and copy/paste it to the same dimensions as the original range. Then you can just cut/paste values.

Upvotes: 0

lguerra
lguerra

Reputation:

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 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

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

'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

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33474

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.

Upvotes: 0

imfrancisd
imfrancisd

Reputation: 21

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

Upvotes: 13

jheriko
jheriko

Reputation: 3113

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).

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 0

Related Questions