Invert/reverse columns that don't have a set range using a command button

Question

I know this question has been asked already but I can't seem to make what I find work for me.

I just want to take all the data starting in column A and going to column J from row 2 to whatever the end of the data might be and reverse the order(inverse the data)

I stumbled upon the the code below but it freezes and I don't want to have to make a selection.

Private Sub CommandButton2_Click()

    Dim vTop As Variant
    Dim vEnd As Variant
    Dim iStart As Integer
    Dim iEnd As Integer

    Application.ScreenUpdating = False
    iStart = 1
    iEnd = Selection.Columns.Count

    Do While iStart < iEnd
        vTop = Selection.Columns(iStart)
        vEnd = Selection.Columns(iEnd)
        Selection.Columns(iEnd) = vTop
        Selection.Columns(iStart) = vEnd
        iStart = iStart + 1
        iEnd = iEnd - 1
    Loop
    Application.ScreenUpdating = True

End Sub

To be clear, I want to make the last row the first row, and the last row the first row. This is a continuous block of data. Cheers

before after

score 1 · Accepted Answer · answered Nov 17 '16 at 16:53

Another version of the code - see if this works.

Private Sub CommandButton2_Click()

Dim v(), i As Long, j As Long, r As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With Range("A1").CurrentRegion
    Set r = .Offset(1).Resize(.Rows.Count - 1)
End With

ReDim v(1 To r.Rows.Count, 1 To r.Columns.Count)

For i = 1 To r.Rows.Count
    For j = 1 To r.Columns.Count
        v(i, j) = r(r.Rows.Count - i + 1, j)
    Next j
Next i

r = v

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

SJR · Answer 2 · 2016-11-17T16:01:01.610

0

Like so? This assumes a continuous block of data from A2 (the currentregion) so will extend beyond J if there is more data, but could be restricted

Private Sub CommandButton2_Click()

Dim v, i As Long, r As Range

Application.ScreenUpdating = False

With Range("A1").CurrentRegion
    Set r = .Offset(1).Resize(.Rows.Count - 1)
End With

v = r

For i = 1 To r.Rows.Count
    r.Rows(i).Cells = Application.Index(v, r.Rows.Count - i + 1, 0)
Next i

Application.ScreenUpdating = True

End Sub

edited Nov 17 '16 at 16:01

answered Nov 17 '16 at 14:48

SJR

22,986
6
18
26

this flopped the columns that the data was in and did not reverse them. I need the last row to become the first row.and first row be the last row – Trevor Burger Nov 17 '16 at 14:55
Edited above. It wasn't clear that that was what you are after as your original code only reversed columns. – SJR Nov 17 '16 at 15:05
something is wrong. this deleted data and then copied some to make duplicates of the same row. sorry it wasn't clear at the beginning. – Trevor Burger Nov 17 '16 at 15:13
I tested this on a sample and it appeared to work. Could you post a screenshot of what you want and clarify where you want the results? Changing this line will put the results underneath `.Cells.Offset(.Rows.Count + 1) = v` – SJR Nov 17 '16 at 15:20
I added two links at the bottom of the post to show what happens when I run it. It also flips the first row, which I want to remain the same – Trevor Burger Nov 17 '16 at 15:32
OK, have revised again. This overwrites but if you don't want this an easy change to make - just say where you do want them. – SJR Nov 17 '16 at 15:38
I just want them in the same location. Column A stays in column A and so on. this actually ends at column i but I dont think that matters. So this did the job but it moved the column locations again (reveresed the column order) – Trevor Burger Nov 17 '16 at 15:43
Do you mean you just want to reverse the rows? – SJR Nov 17 '16 at 15:51
I am very sorry. Yes that is all. I added a comment to the original post late to say that because I didn't realize the code I posted was for reversing columns – Trevor Burger Nov 17 '16 at 15:54
I received an error on the line starting with r.Row(i) – Trevor Burger Nov 17 '16 at 16:05
so it looks like it works, but it crashes excel. So I have to force close excel, asking me if I want to restart it. when I do the debug window comes up and tells me automation error, object invoked has disconnected from its clients. I think it might be stuck in a loop. Maybe something like :"LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row" – Trevor Burger Nov 17 '16 at 16:22
Strange, don't think it's anything to do with the loop. How many rows and columns of data do you actually have? Do you have any other code in the workbook? – SJR Nov 17 '16 at 16:28
only 9 columns but close to 15,000 rows. I have another button to import the data but that's it – Trevor Burger Nov 17 '16 at 16:36
How is the data imported - where from? – SJR Nov 17 '16 at 16:39
from an excel binary file .xlsb located in the same folder, the user selects the file – Trevor Burger Nov 17 '16 at 16:42
If you search for that error there seem to be a variety of causes and solutions eg http://stackoverflow.com/questions/17302918/excel-vba-automation-error-the-object-invoked-has-disconnected-from-its-clients I'm not sure of the cause - may need to add workbook references to the code if you have more than one file open when the code is run (?) – SJR Nov 17 '16 at 16:45
the other file isn't open when the code is ran though. It closes after the data is imported. It accomplished reversing the data when you had it also reversing the columns so I dont think that is the problem – Trevor Burger Nov 17 '16 at 16:48
I've added some slightly different code - see if that works. – SJR Nov 17 '16 at 16:53

score 0 · Answer 3 · answered Nov 17 '16 at 14:50

The code below copies the data in each column to column number 20 - current column index, and at the end of the For loop it deletes the original data that lies in Columns A:J.

Option Explicit

Private Sub CommandButton2_Click()

Dim LastRow As Long
Dim Col As Long
Dim ColStart As Long, ColEnd As Long

Application.ScreenUpdating = False

' Column A
ColStart = 1
' Column J
ColEnd = 10 ' Selection.Columns.Count

' modify "Sheet1" to your sheet's name
With Sheets("Sheet1")
    For Col = ColStart To ColEnd
        ' find last row with data for current column
        LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row

        ' copy in reverse order to column 20 to 11
        ' copy current column to column 20-current column index
        .Range(Cells(2, Col), Cells(LastRow, Col)).Copy .Range(Cells(2, 20 - Col), Cells(LastRow, 20 - Col))
    Next Col
End With

' delete original data in column A:J
With Sheets("Sheet1")
    .Columns("A:J").EntireColumn.Delete
End With

Application.ScreenUpdating = True

End Sub

I want to keep the reversed data in the same columns. When I tried to change the "copy" line of your code to reflect this I recieved an error — Trevor Burger, Nov 17 '16 at 15:06

Invert/reverse columns that don't have a set range using a command button

3 Answers3