Random Row Swap

  • Hi all,
    I have 6 columns of data. 3 columns is one team and the other 3 is for another. I'm trying to figure out how to get a code to do a swap between two random adjacent rows. For example, A4 B4 C4 swaps with D4 E4 F4, the "4" should be the same between both swaps. The code should be able to pick two random rows and swap it with the row beside it. Restating again, there are 6 columns, A = F, B = E and C = D. A must swap with F and so on... Thanks

  • Re: Random Row Swap


    Change i as needed.


    Code
    Dim i As Long
    Dim temp As Variant
    
    
    i = 4
     
    With Rows(i)
        temp = .Range("A1:F1").Value
        .Range("A1:F1").Value = Array(temp(1, 6), temp(1, 5), temp(1, 4), temp(1, 3), temp(1, 2), temp(1, 1))
    End With
  • Re: Random Row Swap


    Adding to jindons nice Application.Index code...


    To randomize the row...


    Maybe:


    Code
    Private Sub CommandButton1_Click()
        Dim rndRow As Integer
        Randomize
        rndRow = Int((Range("A" & Rows.Count).End(xlUp).Row * Rnd) + 1)
        Range(Cells(rndRow, 1), Cells(rndRow, 6)) = Application.Index(Range(Cells(rndRow, 1), Cells(rndRow, 6)), 0, [{6,5,4,3,2,1}])
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!