Delete entire row based on last part of item matches with sheet name

[Ali M]

Hi
I search for macro to delete entire row for DAILY sheet if the last part of item contains the same sheets names when matching with others sheets names

copy.xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	21/06/2023	CASH PR FROM ALI	15,000.00		15,000.00
3	21/06/2023	CASH PR FROM ALI	15,000.00		15,000.00
4	22/06/2023	CASH DM FROM ALI		37,000.00	-37,000.00
5	22/06/2023	EXPENSE ADMIN		65,000.00	-65,000.00
6	23/06/2023	CASH DM		1,200.00	-1,200.00
7	20/06/2023	PURCHASE TO OMAR	15,000.00		15,000.00
8	21/06/2023	STOCK1	100,000.00		100,000.00
9	22/06/2023	PURCHASE RETURNS FROM OMAR		14,000.00	-14,000.00
10	22/06/2023	PURCHASE LOW	6,200.00		6,200.00
11	22/06/2023	EXPENSE PR	37,000.00		37,000.00
12	22/06/2023	SALES		201,000.00	-201,000.00
13	22/06/2023	SALES RETURNS	3,500.00		3,500.00
14	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	-3,200.00
15	23/06/2023	SELLING LOW		3,201.00	-3,201.00
16	24/06/2023	SALES FROM OMAR		3,202.00	-3,202.00
DAILY

Cell Formulas
Range	Formula
E2:E16	E2	=C2-D2

copy.xlsm
	A	B	C	D	E
1	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	PURCHASE	15,000.00		15,000.00
3	22/06/2023	PURCHASE RETURNS		4,000.00	11,000.00
4	24/06/2023	SALES		3,202.00	7,798.00
5		TOTAL	15,000.00	7,202.00	7,798.00
OMAR

Cell Formulas
Range	Formula
E2,E5	E2	=C2-D2
E3:E4	E3	=E2+C3-D3
C5:D5	C5	=SUM(C2:C4)

copy.xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE	200,000.00		200,000.00
3	21/06/2023	CASH PR	15,000.00		215,000.00
4	22/06/2023	CASH DM		30,000.00	185,000.00
5	23/06/2023	CASH DM		1,200.00	183,800.00
6		TOTAL	215,000.00	31,200.00	183,800.00
ALI

Cell Formulas
Range	Formula
E2,E6	E2	=C2-D2
E3:E5	E3	=E2+C3-D3
C6	C6	=SUM(C2:C4)
D6	D6	=SUM(D2:D5)

so I don't need matching data for the others sheets .
you will note the last part in item in column B for daily sheet will contain names OMAR, ALI and there are sheets names(OMAR,ALI) then will delete entire row contains names sheets from DAILY sheet to be like this after deletion

copy.xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	22/06/2023	EXPENSE ADMIN		65,000.00	-65,000.00
3	23/06/2023	CASH DM		1,200.00	-1,200.00
4	21/06/2023	STOCK1	100,000.00		100,000.00
5	22/06/2023	PURCHASE LOW	6,200.00		6,200.00
6	22/06/2023	EXPENSE PR	37,000.00		37,000.00
7	22/06/2023	SALES		201,000.00	-201,000.00
8	22/06/2023	SALES RETURNS	3,500.00		3,500.00
9	23/06/2023	SELLING LOW		3,201.00	-3,201.00
DAILY

Cell Formulas
Range	Formula
E2:E9	E2	=C2-D2

the data in my real project could be 5000 rows in DAILY sheet and sheets could be 20 sheets after DAILY sheet.
thanks

 

[Ali M]

no answering and posted here
Delete entire row based on last part of item matches with sheet name

 

[Ali M]

it solved by three members in the other forum .
here is solved by mr.jindon
I hope this is useful for the others members if anybody interests.

Sub test()
    Dim a(), i As Long
    ReDim a(1 To Worksheets.Count)
    For i = 1 To Worksheets.Count
        a(i) = "* " & Sheets(i).Name
    Next
    With Sheets("daily")
        .[h2].Formula = "=sum(countif(b2,{""" & Join(a, """,""") & """}))"
        .[a1].CurrentRegion.AdvancedFilter 1, .[h1:h2]
        .[a1].CurrentRegion.Offset(1).EntireRow.Delete
        If .FilterMode Then .ShowAllData
        .[h2] = ""
    End With
End Sub