shuffle names in cells, ignoring blank cells, macro

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.

So you're suggesting to edit the Macro each time and remove any cells that are blank?

if so, that wouldn't be the ideal way,

It would be our supervisors on the floor using this file and some have little excel experience to go in and edit macros.

i would think there has to be a code to "ignore blank" cells.

Oh ok,

To break it down, i have names in cells between say, A5 -A16. I may have A6 and A13 with no names in it. As of right now, with the code, it will shuffle all the cells from A5-A16 and maybe put the blank cells now at A8 and A10. I dont want that to happen. I want all the names to still shuffle, but for the cells at A6 and A13 to remain blank.

The next day, i might have cells A7 and A9 blank, and want them to remain blank when i press the shuffle button. So i am hoping for a code to ignore blank cells when it does the shuffle as the blank cells maybe in a different location each day.

That seems to work perfectly! Don't understand what it means but it works :P

thanks everyone!