Exclude Data before TOTAL row for each separated range when show on userform

[Abdo]

I got code from this forum to show data on userform for specific sheet , but the problem will contain blank rows among separated ranges and I just want populating TOTAL row for each range for each client and exclude data before TOTAL row .
the separated ranges could be 20000 ranges.
here is data in sheet

DIVIDED1.xlsm
	A	B	C	D	E	F	G
1	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
2	01/01/2022	-	ABDEND1	OPENNING	20000	-	20000
3	04/01/2022	PA-B3	ABDEND1	PA	20000	-	40000
4	05/01/2022	SA-B35	ABDEND1	SA	-	1000	39000
5	TOTAL	-	-	-	40000	1000	39000
6
7
8	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
9	14/01/2022	PA-B352	ABDEND10	PA	140	-	140
10	14/01/2022	PA-B352	ABDEND10	PA	100	240	-
11	TOTAL	-	-	-	240	240	-
12
13
14	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
15	14/04/2022	PA-B442	ABDEND100	PA	1040	-	1040
16	TOTAL	-	-	-	1040	-	1040
17
18
19	DATE	INVOICE NO	CLIENT NO	DESCRIBE	DEBIT	CREDIT	BALANCE
20	30/09/2024	PA-B1342	ABDEND1000	PA	10040		10040
21	30/09/2024	SA-B1342	ABDEND1000	SA		40	10000
22	01/10/2024	SA-B1343	ABDEND1000	SA		1000	9000
23	TOTAL	-	-	-	10040	1040	9000
splitting

on userform should exclude data before TOTAL row for each separated range . just show client name and TOTAL row

thanks

 

[myall_blues]

Can you post your form code please?

 

[myall_blues]

Here is some form code that does what you need.

Private Sub UserForm_Initialize()
    Dim lastRow As Long, i As Long, count As Long
    count = 0
    lastRow = Worksheets("sheet1").Cells.Find("*", _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To lastRow
        If Cells(i, 1).Value = "TOTAL" Then
            count = count + 1
            ListBox1.AddItem count
            ListBox1.List(ListBox1.ListCount - 1, 1) = Cells(i - 1, 3).Value
            ListBox1.List(ListBox1.ListCount - 1, 2) = Cells(i, 5).Value
            ListBox1.List(ListBox1.ListCount - 1, 3) = Cells(i, 6).Value
            ListBox1.List(ListBox1.ListCount - 1, 4) = Cells(i, 7).Value
        End If
    Next i
End Sub

It produced this output when I ran it on your data.

 

[Abdo]

Hi
here is the code

Option Explicit
Dim a As Variant


Private Sub UserForm_Initialize()
a = Sheets("splitting").Range("A2:G" & Sheets("splitting").Range("G" & Rows.count).End(3).Row).Value

Dim lindex&
  Dim i As Long, j As Long, k As Long
 
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a)
    
      k = k + 1
      For j = 1 To 7
        If j = 1 Then  
          b(k, j) = Format(a(i, j), "dd/mm/yyyy")
          
        Else
          b(k, j) = a(i, j)

End If

      Next
    
  Next
  If k > 0 Then ListBox1.List = b
  With ListBox1
  
  .ColumnWidths = "70;100;90;150;100;120;120"
For i = 0 To .ListCount - 1
            .List(i, 4) = Format(.List(i, 4), "#,##0.00")
            .List(i, 5) = Format(.List(i, 5), "#,##0.00")
            .List(i, 6) = Format(.List(i, 6), "#,##0.00")
        Next i

  End With
End Sub

 

[myall_blues]

Try this.

Private Sub UserForm_Initialize()
    Dim a As Variant
    Dim i As Long, k As Long
    a = Sheets("splitting").Range("A2:G" & Sheets("splitting").Range("G" & Rows.count).End(3).Row).Value
    ListBox1.Clear
    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 1 To UBound(a)
        If a(i, 1) = "TOTAL" Then
            k = k + 1
            b(k, 1) = k
            b(k, 2) = a(i - 1, 3)
            b(k, 3) = a(i, 5)
            b(k, 4) = a(i, 6)
            b(k, 5) = a(i, 7)
        End If
    Next
    If k > 0 Then
        ListBox1.ColumnCount = 5
        ListBox1.List = b
        With ListBox1
            .ColumnWidths = "70;100;120;120"
            For i = 0 To .ListCount - 1
                .List(i, 3) = Format(.List(i, 3), "#,##0.00")
                .List(i, 4) = Format(.List(i, 4), "#,##0.00")
                .List(i, 5) = Format(.List(i, 5), "#,##0.00")
            Next i
        End With
    End If
End Sub

 

[Fuji]

Private Sub UserForm_Initialize()
    Dim a, cols, i&, ii&, n&
    With Sheets("splitting")
        a = Intersect(.UsedRange, .Columns("a:g"))
    End With
    cols = Array(3, 5, 6, 7)
    ReDim b(1 To UBound(cols) + 2, 1 To UBound(a, 1))
    For i = 1 To UBound(a, 1)
        If a(i, 1) = "DATE" Then
            If n = 0 Then
                n = n + 1
                b(1, n) = "ITEM"
                For ii = 0 To UBound(cols)
                    b(ii + 2, n) = a(i, cols(ii))
                Next
            End If
        ElseIf a(i, 1) = "TOTAL" Then
            n = n + 1
            b(1, n) = n - 1
            For ii = 0 To UBound(cols)
                b(ii + 2, n) = a(i - IIf(cols(ii) = 3, 1, 0), cols(ii))
            Next
        End If
    Next
    ReDim Preserve b(1 To UBound(b, 1), 1 To n)
    With Me.ListBox1
        .ColumnCount = UBound(b, 1)
        .Column = b
    End With
End Sub

 

[Abdo]

@myall_blues
perfect , thank for your help.

 

[Abdo]

@Fuji
awesome!
your code is really fast than original code, I like it.
could you make format numbers in column 3,4,5 like this "#,##0.00" ,please?
I tried as in original code but shows error !

 

[Fuji]

Change

                b(ii + 2, n) = a(i - IIf(cols(ii) = 3, 1, 0), cols(ii))

to

                b(ii + 2, n) = Format(a(i - IIf(cols(ii) = 3, 1, 0), cols(ii)), "#,0.00;-#,0.00;0")

 

[Abdo]

@Fuji
I was posting new thread about slowness in original code but you gave me the solution by your code.
much appreciated for your help.