best method of bulk move of data?

Hi all,

I have a spreadsheet that has 6 columns of data -- 1 record ID and 5 pieces of associated data. these rows of data are intermittently interspereced with group identification rows like so:

(first ID starts in cell(14, 2))

Cohort 1
Id1 data1 data2 data3 data4 data5
Id2 data1 data2 data3 data4 data5
Id3 data1 data2 data3 data4 data5

cohort 2
Id4 data1 data2 data3 data4 data5
Id5 data1 data2 data3 data4 data5
Id6 data1 data2 data3 data4 data5

I need to quickly randomize this data (usually 300+ rows of data) based on the IDs.

Unfortunatly the two ways I worked out (shown below) to do this are *very* slow. I need something that goes much faster because I want to loop the randomization process until certain criteria are met. Could be as many as 500 or more re-randomizations.

Method 1: copy and paste of rows

private sub cmdRandomize_click()
    Dim lRow As Integer
        lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Dim I As Integer
    Dim X As Long
    Dim Y As Long
    Dim cohortCheck1 As Boolean
    Dim cohortCheck2 As Boolean

For X = 14 To lRow
    
    Randomize
        Y = Int(Rnd * (lRow - 14) + 14)
                
        If Y > 0 Then
            cohortCheck1 = ActiveSheet.Cells(X, 2).value Like "*Cohort*"
            cohortCheck2 = ActiveSheet.Cells(Y, 2).value Like "*Cohort*"
        
            If cohortCheck1 = False And cohortCheck2 = False And _
                ActiveSheet.Cells(X, 2) <> "" And ActiveSheet.Cells(Y, 2) <> "" Then
                ActiveSheet.Cells(X, 2).Resize(1, 6).Copy
                Paste (ActiveSheet.Cells(lRow + 5, 2))
                ActiveSheet.Cells(Y, 2).Cells.Resize(1, 6).Copy
                Paste (ActiveSheet.Cells(X, 2))
                ActiveSheet.Cells(lRow + 5, 2).Resize(1, 6).Cut
                Paste (ActiveSheet.Cells(Y, 2))
            ElseIf cohortCheck1 = False And cohortCheck2 = True And _
                ActiveSheet.Cells(X, 2) <> "" And ActiveSheet.Cells(Y, 2) <> "" Then
                Y = Y + 1
                ActiveSheet.Cells(X, 2).Resize(1, 6).Copy
                Paste (ActiveSheet.Cells(lRow + 5, 2))
                ActiveSheet.Cells(Y, 2).Cells.Resize(1, 6).Copy
                Paste (ActiveSheet.Cells(X, 2))
                ActiveSheet.Cells(lRow + 5, 2).Resize(1, 6).Cut
                Paste (ActiveSheet.Cells(Y, 2))
            End If
        End If
            
    Next X

End Sub

This method is a bit clunky but it works. I was hoping that maybe if I used variables instead of cut/paste that things would go faster as follows:

Private Sub cmdRandomize_Click()

    Dim lRow As Integer
        lRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Dim I As Integer
    Dim X As Long
    Dim Y As Long
    Dim tmp1 As Single
    Dim tmp2 As Single
    Dim tmp3 As Single
    Dim tmp4 As Single
    Dim tmp5 As Single
    Dim tmp6 As Single
    Dim cohortCheck1 As Boolean
    Dim cohortCheck2 As Boolean
    
    For X = 14 To lRow
    
    Randomize
        Y = Int(Rnd * (lRow - 14) + 14)  
                
        If Y > 0 Then
            cohortCheck1 = ActiveSheet.Cells(X, 2).value Like "*Cohort*"
            cohortCheck2 = ActiveSheet.Cells(Y, 2).value Like "*Cohort*"
        
            If cohortCheck1 = False And cohortCheck2 = False And _
                ActiveSheet.Cells(X, 2) <> "" And ActiveSheet.Cells(Y, 2) <> "" Then
                
                tmp1 = ActiveSheet.Cells(X, 2).value
                tmp2 = ActiveSheet.Cells(X, 3).value
                tmp3 = ActiveSheet.Cells(X, 4).value
                tmp4 = ActiveSheet.Cells(X, 5).value
                tmp5 = ActiveSheet.Cells(X, 6).value
                tmp6 = ActiveSheet.Cells(X, 7).value
                
                ActiveSheet.Cells(X, 2).value = ActiveSheet.Cells(Y, 2).value
                ActiveSheet.Cells(X, 3).value = ActiveSheet.Cells(Y, 3).value
                ActiveSheet.Cells(X, 4).value = ActiveSheet.Cells(Y, 4).value
                ActiveSheet.Cells(X, 5).value = ActiveSheet.Cells(Y, 5).value
                ActiveSheet.Cells(X, 6).value = ActiveSheet.Cells(Y, 6).value
                ActiveSheet.Cells(X, 7).value = ActiveSheet.Cells(Y, 7).value
                
                ActiveSheet.Cells(Y, 2).value = tmp1
                ActiveSheet.Cells(Y, 3).value = tmp2
                ActiveSheet.Cells(Y, 4).value = tmp3
                ActiveSheet.Cells(Y, 5).value = tmp4
                ActiveSheet.Cells(Y, 6).value = tmp5
                ActiveSheet.Cells(Y, 7).value = tmp6
                
            ElseIf cohortCheck1 = False And cohortCheck2 = True And _
                ActiveSheet.Cells(X, 2) <> "" And ActiveSheet.Cells(Y, 2) <> "" Then
                Y = Y + 1
                
                tmp1 = ActiveSheet.Cells(X, 2).value
                tmp2 = ActiveSheet.Cells(X, 3).value
                tmp3 = ActiveSheet.Cells(X, 4).value
                tmp4 = ActiveSheet.Cells(X, 5).value
                tmp5 = ActiveSheet.Cells(X, 6).value
                tmp6 = ActiveSheet.Cells(X, 7).value
                
                ActiveSheet.Cells(X, 2).value = ActiveSheet.Cells(Y, 2).value
                ActiveSheet.Cells(X, 3).value = ActiveSheet.Cells(Y, 3).value
                ActiveSheet.Cells(X, 4).value = ActiveSheet.Cells(Y, 4).value
                ActiveSheet.Cells(X, 5).value = ActiveSheet.Cells(Y, 5).value
                ActiveSheet.Cells(X, 6).value = ActiveSheet.Cells(Y, 6).value
                ActiveSheet.Cells(X, 7).value = ActiveSheet.Cells(Y, 7).value
                
                ActiveSheet.Cells(Y, 2).value = tmp1
                ActiveSheet.Cells(Y, 3).value = tmp2
                ActiveSheet.Cells(Y, 4).value = tmp3
                ActiveSheet.Cells(Y, 5).value = tmp4
                ActiveSheet.Cells(Y, 6).value = tmp5
                ActiveSheet.Cells(Y, 7).value = tmp6
            End If
        End If
            
    Next X

    Call averagesFormat

End Sub

But unfortunarly this is even slower than the cut/paste method.

Is there *any* other way to achieve my goal here? I have to randomize the data until each of the cohorts have standard deviations, means, and medians fall withing certain ranges for each of the 5 data points. I have all that written out already, but using the randomization routines above means the user hits "Randomize" and then walks away for a half hour or more. Not ideal.