shuffle names, ignoring blanks, macro

[aikenhead]

Hello all,

I have a macro assigned to a shape that shuffles names around that assigns the workers to a machine. Not all machines run so there are blank spaces. But when I use the macro, it shuffles the names around and it might move it to a machine that is not running. Here is my code:

Sub ShuffleArrayInPlace(InArray() As Variant)
     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     ' ShuffleArrayInPlace
     ' This shuffles InArray to random order, randomized in place.
     ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long
     
    Randomize
    For N = LBound(InArray) To UBound(InArray)
        J = CLng(((UBound(InArray) - N) * Rnd) + N)
        If N <> J Then
            Temp = InArray(N)
            InArray(N) = InArray(J)
            InArray(J) = Temp
        End If
    Next N
End Sub


Public Sub Afternoons()


Dim x() As Variant




x = Application.Transpose(Range("AF5:AF16"))
ShuffleArrayInPlace x
Range("AF5:aF16") = Application.Transpose(x)


x = Application.Transpose(Range("AF18:AF23"))
ShuffleArrayInPlace x
Range("AF18:AF23") = Application.Transpose(x)




End Sub

This will shuffle the names in cells AF5 -AF16 & AF18-AF23. What code do I need to add to this so it will ignore blank spaces that maybe within those cells?

thanks everyone.

 

[aikenhead]

Bump,
Just wanted to bump my post as i am still looking for a solution,
thanks everyone!