CODEWITHSUNDEEP
excelvba
The Boogeyman
The Boogeyman

Reputation: 1

Excel VBA : Swap Columns With Range

I have problem on swapping Column A to Column B then Column B to Column A, both column has row count of 2563. Is there any vba code to solve this problem?

I'm Already trying this code:

Private Sub CommandButton1_Click()
    Dim temp As Double
    temp = Range("A1").Value
    Range("A1").Value = Range("B1").Value
    Range("B1").Value = temp
End Sub

But it can only swap row 1 of both columns...

Upvotes: 0

Views: 1117

Answers (4)

Error 1004
Error 1004

Reputation: 8220

I prefer to use arrays because is much faster.

Option Explicit

Sub test()

    Dim i As Long
    Dim arrA As Variant, arrB As Variant
    Dim ValueA As Double, ValueB As Double

    'Cahng if needed
    With ThisWorkbook.Worksheets("Sheet1")

        arrA = .Range("A1:A2563")
        arrB = .Range("B1:B2563")

        For i = 1 To 2563

            ValueA = arrA(i, 1)
            ValueB = arrB(i, 1)

            arrA(i, 1) = ValueB
            arrB(i, 1) = ValueA

        Next i

            .Range("A1").Resize(UBound(arrA), 1) = arrA
            .Range("B1").Resize(UBound(arrB), 1) = arrB

    End With

End Sub

Upvotes: 1

user11509084
user11509084

Reputation:

Just to add it into the mix, there is a third way.

Insert a column after B. Copy A to C. Delete A.

With ThisWorkbook.Worksheets(1)
    .Columns(3).Insert
    .Columns(1).Copy .Columns(3)
    .Columns(1).Delete
End With

(It assumes you want to move the whole column, but you normally would.)

However, the speed at which this will run depends upon the size of the worksheet and how many formulae it has.

Upvotes: 0

pgSystemTester
pgSystemTester

Reputation: 9917

Using an array would be much faster than looping. In this example column A is copied into an array Hold_RNG. Then Column B is copied to column A, and then the Array is copied into Column B.

Sub SwapCOlumns()
    Dim hold_rng() As Variant
    Dim rowsToinclude As Long, WS As Worksheet

    Set WS = ActiveSheet '<--- make sure this is correct worksheet
    rowsToinclude = 2563 '<----- might want to make more dynamic

    With WS

        hold_rng = .Range("A1:A" & rowsToinclude)

        .Range("A1:A" & rowsToinclude).Value = .Range("B1:B" & rowsToinclude).Value
        .Range("B1:B" & rowsToinclude).Value = hold_rng

    End With

End Sub

Updated: I don't mean to pick on the competing answer as it's simple and effective, but our two answers offer a good illustration of why using Arrays to impact a spreadsheet all at once, is much more efficient than looping and editing. I built this code which will time the results of each approach (inserting in Column E) of a spreadsheet. Through one round of 2563 rows the score was 0 seconds to 4. The array continued to output in 0 seconds while the loop approach fell to 41 seconds when doing 9 trials.

Screen Shot Of Results.

Timing code can be found on my PasteBin page (I don't want this answer to look ridiculously long)

Upvotes: 1

Reza
Reza

Reputation: 40

You can swap them all by a loop. For your case, the loop should go for 2563 times.

Private Sub CommandButton1_Click()

    For i = 1 To 2563
        temp = Cells(i, 1).Value
        Cells(i, 1).Value = Cells(i, 2).Value
        Cells(i, 2).Value = temp
    Next i

End Sub

Upvotes: 1

Related Questions