VBA Shuffle Coding in Excel

Question

Below is the code I have created to shuffle the cells listed in the specified range. I am unsure what is wrong with it because it will only shuffle cells C19:C29. I have tried some other variations but those have caused the columns in between C, F, I, L and O to also shuffle. I just what C19:C29, F19:F29, I19:I29, L19:L29, O19:O29 to be the cells that shuffle. Also I do not want the values in C19:C29 to go into F19:F29 or vice versa. What is in the column needs to stay in that column. I you could help add or delete code to do this that would be great! Thanks!

Public Sub ShuffleGrid()

Sheets("template").Select 

Dim rngSelected As Range 

Set rngSelected = Range("C19:C29, F19:F29, I19:I29, L19:L29, O19:O29") 

'

' Shuffle Macro

'

'

Dim horizontalLength As Long

Dim verticalLength As Long

horizontalLength = rngSelected.Columns.Count

verticalLength = rngSelected.Rows.Count

Dim i As Long

Dim j As Long

For i = verticalLength To 1 Step -1

For j = horizontalLength To 1 Step -1 

  Dim rndRow As Long 

  Dim rndColumn As Long 

  rndRow = Application.WorksheetFunction.RoundDown(verticalLength \* Rnd + 1, 0) 

  rndColumn = Application.WorksheetFunction.RoundDown(horizontalLength \* Rnd + 1, 0) 

  Dim temp As String 

  Dim tempColor As Long 

  Dim tempFontColor As Long 

  temp = rngSelected.Cells(i, j).Formula 

  tempColor = rngSelected.Cells(i, j).Interior.Color 

  tempFontColor = rngSelected.Cells(i, j).Font.Color 

  rngSelected.Cells(i, j).Formula = rngSelected.Cells(rndRow, rndColumn).Formula 

  rngSelected.Cells(i, j).Interior.Color = rngSelected.Cells(rndRow, rndColumn).Interior.Color 

  rngSelected.Cells(i, j).Font.Color = rngSelected.Cells(rndRow, rndColumn).Font.Color 

  rngSelected.Cells(rndRow, rndColumn).Formula = temp 

  rngSelected.Cells(rndRow, rndColumn).Interior.Color = tempColor 

  rngSelected.Cells(rndRow, rndColumn).Font.Color = tempFontColor 

Next j 

Next i

Sheets("template").Select

End Sub

Answer 1

Try this:

Sub ShuffleGrid()
    Call ShuffleCol(Sheets("template").Range("C19:C29"))
    Call ShuffleCol(Sheets("template").Range("F19:F29"))
    Call ShuffleCol(Sheets("template").Range("I19:I29"))
    Call ShuffleCol(Sheets("template").Range("L19:L29"))
    Call ShuffleCol(Sheets("template").Range("O19:O29"))
End Sub

Sub ShuffleCol(rngSelected As Range)
    Dim verticalLength As Long
    Dim i As Long
    Dim rndRow As Long
    Dim temp As String
    Dim tempColor As Long
    Dim tempFontColor As Long
    verticalLength = rngSelected.Rows.Count
    For i = verticalLength To 1 Step -1
        rndRow = Application.WorksheetFunction.RoundDown(verticalLength * Rnd + 1, 0)
        temp = rngSelected.Cells(i, 1).Formula
        tempColor = rngSelected.Cells(i, 1).Interior.Color
        tempFontColor = rngSelected.Cells(i, 1).Font.Color
        rngSelected.Cells(i, 1).Formula = rngSelected.Cells(rndRow, 1).Formula
        rngSelected.Cells(i, 1).Interior.Color = rngSelected.Cells(rndRow, 1).Interior.Color
        rngSelected.Cells(i, 1).Font.Color = rngSelected.Cells(rndRow, 1).Font.Color
        rngSelected.Cells(rndRow, 1).Formula = temp
        rngSelected.Cells(rndRow, 1).Interior.Color = tempColor
        rngSelected.Cells(rndRow, 1).Font.Color = tempFontColor
    Next i
End Sub