Deleting blank rows in array before displaying in a msg box

[cooldread]

I'm trying to display the names of the accounts that need to be billed during the current week. So far the code works except for the output; the Msg baox displays the empty rows as well therefore my message box is extremely big and un-usable. This is what my code looks like:

Private Sub CommandButton1_Click()


Dim today As Date
Dim P(156) As Date, Account(156) As String, product(156) As String, opportunity(156) As String, final(156, 3) As Variant
Dim i As Integer, x As Integer, y As Integer, txt As String


'Populate different arrays from Excel Sheet
For i = 0 To 155
    Account(i) = Cells(i + 2, 21).Value
    product(i) = Cells(i + 2, 29).Value
    opportunity(i) = Cells(i + 2, 2).Value
    'Populate closed date Array
    P(i) = Cells(i + 2, 16).Value
Next

'Puts today's date in cell AR1
Range("AR1").Value = Date
today = Range("AR1").Value

'Compare closed date to today and see if its less than 7 days away
'If so, put in final array
For x = 0 To 155
For y = 0 To 2
    If DateDiff("m", today, P(x)) = 0 And DateDiff("d", today, P(x)) <= 7 Then


        final(x, 1) = Account(x)
        final(x, 2) = product(x)
        final(x, 3) = opportunity(x)
                             
    End If

'Convert the array to txt   
txt = txt & final(x, y) & vbCrLf
Next y
Next

'Message box to display accounts needed to be billed
MsgBox ("Accounts needed to be billed " & txt)

End Sub

Can someone help me find a way to display the message box without the empty cells of the final array please?
Much appreciated in advance

 

[AlphaFrog]

You wouldn't need to create the final array to create the msgbox text string. Do you need the final array for anything else?

Try this (not tested). The final array is commented out as if it's not required for something else.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
    
    [COLOR=darkblue]Dim[/COLOR] P [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], Account [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], product [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR], opportunity [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
[COLOR=green]'    Dim final(1 To 156, 1 [COLOR=darkblue]To[/COLOR] 3) As Variant, i as long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] txt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=green]'Populate different arrays from Excel Sheet[/COLOR]
    Account = Cells(2, 21).Resize(156).Value
    product = Cells(2, 29).Resize(156).Value
    opportunity = Cells(2, 2).Resize(156).Value
    [COLOR=green]'Populate closed date Array[/COLOR]
    P = Cells(2, 16).Resize(156).Value
    
    [COLOR=green]'Puts today's date in cell AR1[/COLOR]
    Range("AR1").Value = [COLOR=darkblue]Date[/COLOR]
    
    [COLOR=green]'Compare closed date to today and see if its less than 7 days away[/COLOR]
    [COLOR=green]'If so, put in final array[/COLOR]
    [COLOR=darkblue]For[/COLOR] x = 1 To 156
    
        [COLOR=darkblue]If[/COLOR] P(x, 1) <> "" [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Month(Date) = Month(P(x, 1)) And Date - P(x, 1) <= 7 [COLOR=darkblue]Then[/COLOR]
            
                [COLOR=green]'Populate the final array[/COLOR]
[COLOR=green]'                i = i + 1[/COLOR]
[COLOR=green]'                final(i, 1) = Account(x, 1)[/COLOR]
[COLOR=green]'                final(i, 2) = product(x, 1)[/COLOR]
[COLOR=green]'                final(i, 3) = opportunity(x, 1)[/COLOR]
                
                [COLOR=green]'Build the text string[/COLOR]
                txt = txt & Account(x, 1) & ", " & _
                            product(x, 1) & ", " & _
                            opportunity(x, 1) & vbCrLf
                                     
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=darkblue]Next[/COLOR] x
    
    [COLOR=green]'Message box to display accounts needed to be billed[/COLOR]
    MsgBox txt, , "Accounts needed to be billed"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[cooldread]

That worked perfectly!
Thank you so much