exclude zero and precede it on userform when populate data in listbox

[abdelfattah]

Hi
I want when select sheet from combobox1 , then will just show data don't contain zero for lastrow and ignores the whole data contains zero and what precede it .
so the original code does like this

but what I want

as you see the last column contains zero . so any lastrow contains zero should ignore the whole data contains zero and what precede it .
and the difficult part how should change the values in TOTAL row after delete data contains zero and what precede it.
as you see the result in picture 2 in TOTAL row should sum columns DEBIT,CREDIT , as to BALANCE will subtract column DEBIT from CREDIT .

Private Sub LBoxPop()
    Dim r          As Long, c As Long
    Dim Data()     As Variant
    Dim rng        As Range
    
    Set rng = ws.Cells(1, 1).CurrentRegion
    ReDim Data(1 To rng.Rows.Count, 1 To rng.Columns.Count + 1)
 
    For r = 1 To UBound(Data, xlRows)
        For c = 1 To UBound(Data, xlColumns)
            Data(r, c) = rng.Cells(r, c).Text
        Next c
        On Error Resume Next
        Data(r, 6) = rng.Cells(r, 5).Interior.Color
    Next r
 
    With UserForm1.ListBox1
        .ColumnCount = 5
        .columnWidths = "90;290;120;120;100"
        .List = Data
    End With
    
    For i = UBound(Data, xlRows) To 1 Step -1
      If Data(i, 6) = 255 Then
        UserForm1.ListBox1.Selected(i - 1) = True
         UserForm1.ListBox1.TopIndex = i - 1
        'UserForm1.ListBox1.Left = i - 1
         
        Exit For
      End If
    Next
End Sub

thanks in advance

 

[DanteAmor]

This is what the code does.
1. Pass the information from rng to DATA.
2. Check where you have a 0,
3. Then pass the data from that row of DATA to ary
4. Pass from ary to listbox.

Private Sub LBoxPop()
    Dim r          As Long, c As Long, i As Long, ini As Long
    Dim Data()     As Variant, ary As Variant
    Dim rng        As Range
    Dim totDebit   As Double, totCredit As Double
    
    Set rng = ws.Cells(1, 1).CurrentRegion
    Data = rng.Value
 
    ini = 2
    For i = UBound(Data, 1) To 1 Step -1
      If Data(i, 5) = 0 Then
        ini = i + 1
        Exit For
      End If
    Next
    
    ReDim ary(1 To UBound(Data, 1) - ini + 2, 1 To rng.Columns.Count + 1)
    For c = 1 To UBound(Data, 2)
      ary(1, c) = Data(1, c)
    Next
    i = 1
    
    For r = ini To UBound(Data, xlRows)
      i = i + 1
      For c = 1 To UBound(Data, xlColumns)
        ary(i, c) = rng.Cells(r, c).Text
      Next c
      
      If r < UBound(Data, xlRows) Then
        If Data(r, 3) <> "" And IsNumeric(Data(r, 3)) Then
          totDebit = totDebit + Data(r, 3)
        End If
        
        If Data(r, 4) <> "" And IsNumeric(Data(r, 4)) Then
          totCredit = totCredit + Data(r, 4)
        End If
      End If
      
      On Error Resume Next
      ary(i, 6) = rng.Cells(r, 5).Interior.Color
      On Error GoTo 0
    Next r
    ary(UBound(ary, 1), 3) = totDebit
    ary(UBound(ary, 1), 4) = totCredit
    ary(UBound(ary, 1), 5) = totDebit - totCredit
 
    With UserForm1.ListBox1
      .ColumnCount = 5
      .ColumnWidths = "90;290;120;120;100"
      .List = ary
    End With
    
    For i = UBound(ary, xlRows) To 1 Step -1
      If ary(i, 6) = 255 Then
        UserForm1.ListBox1.Selected(i - 1) = True
         UserForm1.ListBox1.TopIndex = i - 1
        'UserForm1.ListBox1.Left = i - 1
        Exit For
      End If
    Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[abdelfattah]

Amazing !
just I need showing number format in last row for BALANCE column . I succeeded with columns DEBIT,CREDIT, but still doesn't show for BALANCE column . may you help me for that ,please ?
I did like this

    ary(UBound(ary, 1), 3) = Format(totDebit, "#,##0.00")
    ary(UBound(ary, 1), 4) = Format(totCredit, "#,##0.00")
    ary(UBound(ary, 1), 5) = Format(totDebit, "#,##0.00") - Format(totCredit, "#,##0.00")

 

[DanteAmor]

Try:

    ary(UBound(ary, 1), 3) = Format(totDebit, "#,##0.00")
    ary(UBound(ary, 1), 4) = Format(totCredit, "#,##0.00")
    ary(UBound(ary, 1), 5) = Format(totDebit - totCredit, "#,##0.00")

 

[abdelfattah]

perfect !
many thanks again.