Excel VBA : Swap Columns With Range

Question

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

Reza · Accepted Answer · 2019-07-04 03:26:32Z

1

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

answered Jul 4, 2019 at 3:26

Reza

404 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pgSystemTester Over a year ago

I didn't mean to slam your answer. I would have posted something like yours not too long ago. I included the detail of why mine is more efficient to help others learn the values of arrays. I wish someone would have helped me understand this sooner. I gave you an upvote.☑

pgSystemTester · Accepted Answer · 2019-07-04 05:42:19Z

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)

Error 1004 · Accepted Answer · 2019-07-05 06:21:22Z

1

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

edited Jul 5, 2019 at 6:21

answered Jul 4, 2019 at 11:58

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

2 Comments

pgSystemTester Over a year ago

Not to be territorial, but this seems like a weak answer. Yes, Arrays are faster, but your approach then goes on to make individual updates to each cell by looping. The benefit of the array is that it only edits the workbook once. Your answer is no different as far as performance than Reza's code only yours is unnecessarily complex. If I'm overlooking something feel free to correct me since you have practically double my rating on SO. I included your results in the timing event I posted and it does as I expect of being similar to Reza. pastebin.com/n5UbyiGy

pgSystemTester Over a year ago

I see the change. That makes more sense, but I guess I don't see how it is much of an improvement over mine. I gave an uptick just because it's Sunday.

user11509084 · Accepted Answer · 2019-07-04 10:39:08Z

0

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.

answered Jul 4, 2019 at 10:39

user11509084

Collectives™ on Stack Overflow

Excel VBA : Swap Columns With Range

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related