Sort Rows Randomly?

[hellothere4]

Hi, I wanted to sort rows randomly. My current thinking is to use rand() in column A after moving everything right a cell. However, I don't know how insert rand() only in rows which originally had a value. Can anyone help me with that? (in vba)

 

[Abgar]

Hi,

If you're data is in consequetive rows with data in Column A to begin with, then the below should work for you:

Sub test()
Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1:A" & Lastrow).Formula = "=rand()"
End Sub

Cheers

 

[AlphaFrog]

Sub Random_Sort()
    
    Application.ScreenUpdating = False
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=RAND()"
    Cells.Sort Key1:=Range("A1"), Order1:=xlDescending
    Columns("A:A").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
        
End Sub

 

[testing lotto]

What would I change to randomize sort in the other direction or both ways, columns and or rows ?

 

[AlphaFrog]

Sub Random_Sort_Rows()
    
    Application.ScreenUpdating = False
    Columns("A:A").Insert Shift:=xlToRight
    Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Formula = "=RAND()"
    Cells.Sort Key1:=Range("A1"), Order1:=xlDescending, Orientation:=xlTopToBottom
    Columns("A:A").Delete Shift:=xlToLeft
    Application.ScreenUpdating = True
        
End Sub

Sub Random_Sort_Columns()
    
    Application.ScreenUpdating = False
    Rows(1).Insert Shift:=xlShiftDown
    Range("A1", Cells(2, Columns.Count).End(xlToLeft).Offset(-1)).Formula = "=RAND()"
    Cells.Sort Key1:=Range("A1"), Order1:=xlDescending, Orientation:=xlLeftToRight
    Rows(1).Delete
    Application.ScreenUpdating = True
        
End Sub

Sub Random_Sort_Columns_and_Rows()

    Random_Sort_Columns
    Random_Sort_Rows
    
End Sub