CODEWITHSUNDEEP
excelvbareverse
Trevor Burger
Trevor Burger

Reputation: 67

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

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

Upvotes: 0

Views: 570

Answers (3)

SJR
SJR

Reputation: 23081

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

Upvotes: 1

SJR
SJR

Reputation: 23081

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

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 0

Related Questions