VBA sort and flatten all non zero values

[t1mduma5]

Simplifying this quite a bit ...
1. I have four sheets three of them are being aggregated into the fourth.
2. Each populated cell on the fourth sheet is an aggregate of fifteen cells from the other three sheets (5 cells per sheet).
3. However to complicate matters before they are comma delimited and written out to the cell they need to be sorted and all zero values eliminated.

For example
Sheet1.M5 = 37 Sheet1.N5 = 0 Sheet1.O5 = 0 Sheet1.P5 =0 Sheet1.Q5 = 0
Sheet2.M5 = 1 Sheet2.N5 = 9 Sheet2.O5 = 0 Sheet2.P5 =0 Sheet2.Q5 = 0
Sheet3.M5 = 29 Sheet3.N5 = 50 Sheet3.O5 = 61 Sheet3.P5 =0 Sheet3.Q5 = 0

Should aggregate to
Sheet4.L5 = "1, 9, 29, 37, 50, 61"


I started with

' The flattened array with all zero value elements removed and comma delimited
Dim SeptemberTerm1Aggregate As String

' Values to be sorted
Dim SeptemberTerm1(0 To 14) As Integer

' Populating the array from the other 3 sheets
SeptemberTerm1(0) = Sheet5.Range("M5").Value
SeptemberTerm1(1) = Sheet5.Range("N5").Value
SeptemberTerm1(2) = Sheet5.Range("O5").Value
SeptemberTerm1(3) = Sheet5.Range("P5").Value
SeptemberTerm1(4) = Sheet5.Range("Q5").Value
SeptemberTerm1(5) = Sheet6.Range("M5").Value
SeptemberTerm1(6) = Sheet6.Range("N5").Value
SeptemberTerm1(7) = Sheet6.Range("O5").Value
SeptemberTerm1(8) = Sheet6.Range("P5").Value
SeptemberTerm1(9) = Sheet6.Range("Q5").Value
SeptemberTerm1(10) = Sheet7.Range("M5").Value
SeptemberTerm1(11) = Sheet7.Range("N5").Value
SeptemberTerm1(12) = Sheet7.Range("O5").Value
SeptemberTerm1(13) = Sheet7.Range("P5").Value
SeptemberTerm1(14) = Sheet7.Range("Q5").Value

' Using the BubbleSrt function from here http://www.mrexcel.com/forum/excel-...al-basic-applications-sort-array-numbers.html
SeptemberTerm1 = BubbleSrt(SeptemberTerm1, True)

' Remove all zero value elements
' Flatten array

Sheet8.Range("L5").Value = SeptemberTerm1Aggregate


Only I'm not sure how to drop all zero value array elements and flatten the array. Or even if this is the best way to go about accomplishing my goal. The five cells from each of the first three sheets might have zero to five significant values. A significant value being a positive non-zero integer.

Any help would be appreciated.
Thank you,
Tim

 

[t1mduma5]

Im writing this for clarification.

The code below does return an error in sub AggregateSeptember() the line that returns the error has a comment explaining it.

After execution Sheet 8 L5 should = "1, 9, 29, 37, 50, 61"

I am totally stumped by this and haven't written any VB before, I would appreciate any help with this.

Thanks in advance for your time and consideration,
Tim

DATA
Sheet 5
M5 N5 O5 P5 Q5 R5
37 0 0 0 0 0

Sheet 6
M5 N5 O5 P5 Q5 R5
1 9 0 0 0 0

Sheet 7
M5 N5 O5 P5 Q5 R5
29 50 61 0 0 0

Sheet 8
L5
0

Sub AggregateSeptember()

Dim i As Integer
Dim j As Integer
Dim SeptemberTerm1Aggregate As String
Dim SeptemberTerm1(0 To 14) As Integer
Dim SeptemberCols
SeptemberCols = Array("M5", "N5", "O5", "P5", "Q5")


For i = 0 To 14
    If i < 5 Then
        If Sheet5.Range(SeptemberCols(i)) <> 0 Then
            SeptemberTerm1(i) = Sheet5.Range(SeptemberCols(i))
        End If
    ElseIf i < 10 Then
        If Sheet6.Range(SeptemberCols(i - 5)) <> 0 Then
            SeptemberTerm1(i - 5) = Sheet6.Range(SeptemberCols(i - 5))
        End If
    ElseIf i < 15 Then
        If Sheet7.Range(SeptemberCols(i - 10)) <> 0 Then
            SeptemberTerm1(i - 10) = Sheet7.Range(SeptemberCols(i - 10))
        End If
    End If
Next i

' This next line returns can't assign array
SeptemberTerm1 = BubbleSrt(SeptemberTerm1, True)


For j = 0 To 14
    If SeptemberTerm1(j) > 0 Then SeptemberTerm1Aggregate = SeptemberTerm1Aggregate & SeptemberTerm1(j)
    If j > 0 And j < 14 Then SeptemberTerm1Aggregate = SeptemberTerm1Aggregate & ", "
Next j


Sheet8.Range("L5").Value = SeptemberTerm1Aggregate


End Sub


Public Function BubbleSrt(ArrayIn, Ascending As Boolean)


Dim SrtTemp As Variant
Dim i As Long
Dim j As Long




If Ascending = True Then
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i) > ArrayIn(j) Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
Else
    For i = LBound(ArrayIn) To UBound(ArrayIn)
         For j = i + 1 To UBound(ArrayIn)
             If ArrayIn(i) < ArrayIn(j) Then
                 SrtTemp = ArrayIn(j)
                 ArrayIn(j) = ArrayIn(i)
                 ArrayIn(i) = SrtTemp
             End If
         Next j
     Next i
End If


BubbleSrt = ArrayIn


End Function