exclude zero, populate last amount for adjacent last enter cell for each range

[Alaa mg]

Hi experts,
I have about 300 customers contains ranges and they are different how many rows for each range . so I expect to reach 9000 rows in the sheet.
what I look for macro populate last amount is existed in column E and put in adjacant cell in column F , but if the last amount in column E is zero then don't populate last amount in adjacant cell in column F. after that will create report in column H:M from row2.
in column (I) will brings names from column(C) and column J will brings the first date , column K will brings the last date and column L will brings amount from column F , if there is duplicates names then should merge in column M for last duplicates names (when there are duplicates names ,then should put under each other of them in column (I). also insert BALANCE row to sum column L
Example:

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA
3	DATE	Describe	DEBIT	CREDIT	BALANCE
4	05/01/2022	NOT PAID	1,233.00		1,233.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00
6	07/01/2022	PAID		2,000.00	466.00
7	08/01/2022	PAID		466.00	0.00
8
9
10			NAME
11			MAL
12	DATE	Describe	DEBIT	CREDIT	BALANCE
13	08/01/2022				0.00
14	09/01/2022	NOT PAID	2,000.00		2,000.00
15	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
16	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00
17
18
19			NAME
20			MOUNIRR
21	DATE	Describe	DEBIT	CREDIT	BALANCE
22	11/01/2022				1,000.00
23	12/01/2022	NOT PAID	1,500.00		2,500.00
24	13/01/2022	NOT PAID	500.00		2,000.00
25	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
26	15/01/2022	PAID		1,010.00	1,990.00
27	16/01/2022	PAID		1,000.00	990.00
28	17/01/2022	PAID		100.00	880.00
CSS

output should be like this

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	MAL	08/01/2022	11/01/2022	2,000.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MOUNIRR	11/01/2022	17/01/2022	880.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			BALANCE				2,880.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00
6	07/01/2022	PAID		2,000.00	466.00
7	08/01/2022	PAID		466.00	0.00
8
9
10			NAME
11			MAL
12	DATE	Describe	DEBIT	CREDIT	BALANCE
13	08/01/2022				0.00
14	09/01/2022	NOT PAID	2,000.00		2,000.00
15	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
16	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
17
18
19			NAME
20			MOUNIRR
21	DATE	Describe	DEBIT	CREDIT	BALANCE
22	11/01/2022				1,000.00
23	12/01/2022	NOT PAID	1,500.00		2,500.00
24	13/01/2022	NOT PAID	500.00		2,000.00
25	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
26	15/01/2022	PAID		1,010.00	1,990.00
27	16/01/2022	PAID		1,000.00	990.00
28	17/01/2022	PAID		100.00	880.00	880.00
CSS

Cell Formulas
Range	Formula
L4	L4	=SUM(L2:L3)

when add new data for any customer like this

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE
2			ALA					1	MAL	08/01/2022	11/01/2022	2,000.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MOUNIRR	11/01/2022	17/01/2022	880.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			BALANCE
5	06/01/2022	NOT PAID	1,233.00		2,466.00
6	07/01/2022	PAID		2,000.00	466.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		100.00	-100.00
9	10/01/2022	PAID		100.00	-200.00
10
11
12			NAME
13			MAL
14	DATE	Describe	DEBIT	CREDIT	BALANCE
15	08/01/2022				0.00
16	09/01/2022	NOT PAID	2,000.00		2,000.00
17	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
18	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
19	12/01/2022	NOT PAID	1,000.00		3,000.00
20	13/01/2022	PAID		2,000.00	1,000.00
21
22
23			NAME
24			MOUNIRR
25	DATE	Describe	DEBIT	CREDIT	BALANCE
26	11/01/2022				1,000.00
27	12/01/2022	NOT PAID	1,500.00		2,500.00
28	13/01/2022	NOT PAID	500.00		2,000.00
29	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
30	15/01/2022	PAID		1,010.00	1,990.00
31	16/01/2022	PAID		1,000.00	990.00
32	17/01/2022	PAID		100.00	880.00	880.00
CSS

output should start from old last amount is existed in column F.

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	09/01/2022	10/01/2022	-200.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MAL	08/01/2022	11/01/2022	2,000.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MAL	12/01/2022	13/01/2022	1,000.00	3,000.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	11/01/2022	17/01/2022	880.00
6	07/01/2022	PAID		2,000.00	466.00			BALANCE				3,680.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		100.00	-100.00
9	10/01/2022	PAID		100.00	-200.00	-200.00
10
11
12			NAME
13			MAL
14	DATE	Describe	DEBIT	CREDIT	BALANCE
15	08/01/2022				0.00
16	09/01/2022	NOT PAID	2,000.00		2,000.00
17	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
18	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
19	12/01/2022	NOT PAID	1,000.00		3,000.00
20	13/01/2022	PAID		2,000.00	1,000.00	1,000.00
21
22
23			NAME
24			MOUNIRR
25	DATE	Describe	DEBIT	CREDIT	BALANCE
26	11/01/2022				1,000.00
27	12/01/2022	NOT PAID	1,500.00		2,500.00
28	13/01/2022	NOT PAID	500.00		2,000.00
29	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
30	15/01/2022	PAID		1,010.00	1,990.00
31	16/01/2022	PAID		1,000.00	990.00
32	17/01/2022	PAID		100.00	880.00	880.00
CSS

Cell Formulas
Range	Formula
L6	L6	=SUM(L2:L5)

 

[DanteAmor]

At least with the 2 examples you gave it works.

Copy all the code and run the create_report macro

Sub create_report()
  Dim dic As Object
  Dim sName As String
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, k As Long, m As Long, lr As Long
  Dim iniDate As Double, finDate As Double, nCount As Double, subtot As Double
  Dim nBalance As Double, sumBal As Double
 
  Set dic = CreateObject("Scripting.Dictionary")
  dic.RemoveAll
  lr = Range("E" & Rows.Count).End(3).Row + 2
  a = Range("A1:E" & lr).Value2
  b = Range("F1:F" & lr).Value2
  ReDim c(1 To UBound(a, 1), 1 To 6)
  
  For i = 1 To UBound(a) - 1
    If a(i, 3) = "NAME" Then
      sName = a(i + 1, 3)
      iniDate = a(i + 3, 1)
      
      For k = i + 3 To UBound(a) - 1
        
        If a(k, 5) = 0 And a(k, 5) <> "" Then
          If k = i + 3 Then iniDate = a(k, 1) Else iniDate = a(k + 1, 1)
        End If
        
        If b(k, 1) <> "" And a(k + 1, 1) <> "" Then
          nBalance = a(k - 1, 5)
          Call addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
        End If
        
        If a(k, 5) = "" Then
          nBalance = a(k - 1, 5)
          If nBalance <> 0 Then
            b(k - 1, 1) = nBalance
            Call addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
          End If
          i = k
          Exit For  'k
        End If
        
      Next
    End If
  Next
  
  Application.ScreenUpdating = False
  
  c(m + 1, 1) = "BALANCE"
  c(m + 1, 5) = sumBal
  Range("H2:M" & Rows.Count).Clear
  Range("F1").Resize(UBound(b)).Value = b
  Range("H2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  
  'FORMAT cells
  Range("J:K").NumberFormat = "dd/mm/yyyy"
  Range("L:M").NumberFormat = "#,##0.00;-#,##0.00;"
  Range("H:M").HorizontalAlignment = xlCenter
  Range("H1").Copy
  lr = Range("H" & Rows.Count).End(3).Row
  Range("H" & lr).PasteSpecial xlPasteFormats
  Range("H2:M" & lr).Borders.LineStyle = xlContinuous
  
  Application.ScreenUpdating = False
  Application.CutCopyMode = False
End Sub

Sub addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
  If Not dic.exists(sName) Then
    dic(sName) = 1 & "|" & nBalance
    nCount = 0
    subtot = 0
  Else
    nCount = Split(dic(sName), "|")(0) + 1
    subtot = Split(dic(sName), "|")(1) + nBalance
    dic(sName) = nCount & "|" & subtot
  End If
  sumBal = sumBal + nBalance
  finDate = a(k - 1, 1)
  m = m + 1
  c(m, 1) = m
  c(m, 2) = sName
  c(m, 3) = iniDate
  c(m, 4) = finDate
  c(m, 5) = nBalance
  c(m, 6) = IIf(nCount > 1, subtot, "")
  iniDate = a(k + 1, 1)
End Sub

 

[Alaa mg]

thanks for the code.
I think the code show result incorrectly
when entering new data

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	MAL	08/01/2022	11/01/2022	2,000.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MOUNIRR	11/01/2022	17/01/2022	880.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			BALANCE				2,880.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00
6	07/01/2022	PAID		2,000.00	466.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		466.00	-466.00
9
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		880.00
31	19/01/2022	PAID		100.00	880.00
CSS

Cell Formulas
Range	Formula
L4	L4	=SUM(L2:L3)

error result as highlighted by red ( the dates also amount in column L)

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	09/01/2022	09/01/2022	-466.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MAL	08/01/2022	11/01/2022	2,000.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MOUNIRR	11/01/2022	16/01/2022	990.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	18/01/2022	19/01/2022	880.00	1,870.00
6	07/01/2022	PAID		2,000.00	466.00			BALANCE				3,404.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		466.00	-466.00	-466
9
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		990.00
31	19/01/2022	PAID		100.00	880.00	880
CSS

the right result should be as highlighted by yellow

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	09/01/2022	09/01/2022	-466.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MAL	08/01/2022	11/01/2022	2,000.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MOUNIRR	11/01/2022	17/01/2022	880.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	18/01/2022	19/01/2022	880.00	1,760.00
6	07/01/2022	PAID		2,000.00	466.00			BALANCE				4,226.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		466.00	-466.00	-466
9
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		990.00
31	19/01/2022	PAID		100.00	880.00	880
CSS

another example when enter new data

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	09/01/2022	09/01/2022	-466.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	MAL	08/01/2022	11/01/2022	2,000.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MOUNIRR	11/01/2022	17/01/2022	880.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	18/01/2022	19/01/2022	880.00	1,760.00
6	07/01/2022	PAID		2,000.00	466.00			BALANCE				4,226.00
7	08/01/2022	PAID		466.00	0.00
8	09/01/2022	PAID		466.00	-466.00	-466
9	10/01/2022	NOT PAID	500.00		34.00
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		990.00
31	19/01/2022	PAID		100.00	880.00	880
32	20/01/2022	PAID		100.00	780.00
33	21/01/2022	PAID		100.00	680.00
CSS

error result

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	09/01/2022	08/01/2022
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	ALA	10/01/2022	10/01/2022	34.00	34.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MAL	08/01/2022	11/01/2022	2,000.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	11/01/2022	16/01/2022	990.00
6	07/01/2022	PAID		2,000.00	466.00			5	MOUNIRR	18/01/2022	18/01/2022	990.00	1,980.00
7	08/01/2022	PAID		466.00	0.00			6	MOUNIRR	20/01/2022	21/01/2022	680.00	2,660.00
8	09/01/2022	PAID		466.00	-466.00	-466		BALANCE				4,694.00
9	10/01/2022	NOT PAID	500.00		34.00	34
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		990.00
31	19/01/2022	PAID		100.00	880.00	880
32	20/01/2022	PAID		100.00	780.00
33	21/01/2022	PAID		100.00	680.00	680
CSS

right result should be as highlighted by yellow

Alaa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			NAME					ITEM	NAME	FROM DATE	TO DATE	BALANCE	TOTAL
2			ALA					1	ALA	05/01/2022	09/01/2022	-466.00
3	DATE	Describe	DEBIT	CREDIT	BALANCE			2	ALA	10/01/2022	10/01/2022	34.00	-432.00
4	05/01/2022	NOT PAID	1,233.00		1,233.00			3	MAL	08/01/2022	11/01/2022	2,000.00
5	06/01/2022	NOT PAID	1,233.00		2,466.00			4	MOUNIRR	11/01/2022	17/01/2022	880.00
6	07/01/2022	PAID		2,000.00	466.00			5	MOUNIRR	18/01/2022	19/01/2022	880.00
7	08/01/2022	PAID		466.00	0.00			6	MOUNIRR	20/01/2022	21/01/2022	680.00	2,440.00
8	09/01/2022	PAID		466.00	-466.00	-466.00		BALANCE				4,008.00
9	10/01/2022	NOT PAID	500.00		34.00	34
10
11			NAME
12			MAL
13	DATE	Describe	DEBIT	CREDIT	BALANCE
14	08/01/2022				0.00
15	09/01/2022	NOT PAID	2,000.00		2,000.00
16	11/01/2022	NOT PAID	2,000.00	1,000.00	2,000.00
17	11/01/2022	NOT PAID	2,000.00	2,000.00	2,000.00	2,000.00
18
19
20			NAME
21			MOUNIRR
22	DATE	Describe	DEBIT	CREDIT	BALANCE
23	11/01/2022				1,000.00
24	12/01/2022	NOT PAID	1,500.00		2,500.00
25	13/01/2022	NOT PAID	500.00		2,000.00
26	14/01/2022	PAID	2,000.00	1,000.00	3,000.00
27	15/01/2022	PAID		1,010.00	1,990.00
28	16/01/2022	PAID		1,000.00	990.00
29	17/01/2022	PAID		100.00	880.00	880.00
30	18/01/2022	NOT PAID	100.00		990.00
31	19/01/2022	PAID		100.00	880.00	880
32	20/01/2022	PAID		100.00	780.00
33	21/01/2022	PAID		100.00	680.00	680
CSS

Cell Formulas
Range	Formula
F8	F8	=L2

 

[DanteAmor]

You have problems with your definition of the "FROM DATE". In an example from the OP, one date is after 0 and the other date is in the same row as 0.

Now on to your last example. You are not considering 0 for the name "ALA"

While you decide how to get the FROM DATE, try the following macro. It works correctly for the example in post #3.
As you will understand, it is a complicated macro, it requires several tests, since the combinations can be very diverse.
So keep trying.

Sub create_report()
  Dim dic As Object
  Dim sName As String
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, k As Long, m As Long, lr As Long
  Dim iniDate As Double, finDate As Double, nCount As Double, subtot As Double
  Dim nBalance As Double, sumBal As Double
 
  Set dic = CreateObject("Scripting.Dictionary")
  dic.RemoveAll
  lr = Range("E" & Rows.Count).End(3).Row + 2
  a = Range("A1:E" & lr).Value2
  b = Range("F1:F" & lr).Value2
  ReDim c(1 To UBound(a, 1), 1 To 6)
  
  For i = 1 To UBound(a) - 1
    If a(i, 3) = "NAME" Then
      sName = a(i + 1, 3)
      iniDate = a(i + 3, 1)
      
      For k = i + 3 To UBound(a) - 1
        
        If a(k, 5) = 0 And a(k, 5) <> "" Then
          If k = i + 3 Then iniDate = a(k, 1) Else iniDate = a(k + 1, 1)
        End If
        
        If b(k, 1) <> "" And a(k + 1, 1) <> "" Then
          nBalance = a(k, 5)
          Call addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
        End If
        
        If a(k, 5) = "" Then
          nBalance = a(k - 1, 5)
          If nBalance <> 0 Then
            b(k - 1, 1) = nBalance
            Call addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
          End If
          i = k
          Exit For  'k
        End If
        
      Next
    End If
  Next
  
  Application.ScreenUpdating = False
  
  c(m + 1, 1) = "BALANCE"
  c(m + 1, 5) = sumBal
  Range("H2:M" & Rows.Count).Clear
  Range("F1").Resize(UBound(b)).Value = b
  Range("H2").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  
  'FORMAT cells
  Range("J:K").NumberFormat = "dd/mm/yyyy"
  Range("L:M").NumberFormat = "#,##0.00;-#,##0.00;"
  Range("H:M").HorizontalAlignment = xlCenter
  Range("H1").Copy
  lr = Range("H" & Rows.Count).End(3).Row
  Range("H" & lr).PasteSpecial xlPasteFormats
  Range("H2:M" & lr).Borders.LineStyle = xlContinuous
  
  Application.ScreenUpdating = False
  Application.CutCopyMode = False
End Sub

Sub addItem(dic, sName, nBalance, nCount, subtot, sumBal, k, m, a, c, iniDate, finDate)
  If Not dic.exists(sName) Then
    dic(sName) = 1 & "|" & nBalance
    nCount = 0
    subtot = 0
  Else
    nCount = Split(dic(sName), "|")(0) + 1
    subtot = Split(dic(sName), "|")(1) + nBalance
    dic(sName) = nCount & "|" & subtot
  End If
  sumBal = sumBal + nBalance
  If a(k, 1) = "" Then finDate = a(k - 1, 1) Else finDate = a(k, 1)
  m = m + 1
  c(m, 1) = m
  c(m, 2) = sName
  c(m, 3) = iniDate
  c(m, 4) = finDate
  c(m, 5) = nBalance
  Select Case nCount
    Case 0, 1
    Case 2
      c(m, 6) = subtot
    Case Else
      c(m - 1, 6) = ""
      c(m, 6) = subtot
  End Select
  iniDate = a(k + 1, 1)
End Sub

 

[Alaa mg]

You have problems with your definition of the "FROM DATE". In an example from the OP, one date is after 0 and the other date is in the same row as 0.

you're right!
the code works great, but I would fix zero case .
I would when specify from date and if there is zero then should be after zero when specify from date.
example MAl name should start from 09/01/2022 , not 08/01/2022 as the code does it.

 

[DanteAmor]

the code works great

example MAl name should start from 09/01/2022 , not 08/01/2022 as the code does it.

The code does what you put in your image:

I guess it must be: "example MAl name should start from 09/01/2022 , not 08/01/2022 as you put it in your example.."

Change this line:

If k = i + 3 Then iniDate = a(k, 1) Else iniDate = a(k + 1, 1)

For this:

iniDate = a(k + 1, 1)

 

[Alaa mg]

much appreciated for your assistance.