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

Ali M

Active Member
Joined
Oct 10, 2021
Messages
348
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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
ABCDE
1DATEACCOUNT NAMEDEBIT CREDITBALANCE
221/06/2023CASH PR FROM ALI15,000.0015,000.00
321/06/2023CASH PR FROM ALI15,000.0015,000.00
422/06/2023CASH DM FROM ALI37,000.00-37,000.00
522/06/2023EXPENSE ADMIN65,000.00-65,000.00
623/06/2023CASH DM1,200.00-1,200.00
720/06/2023PURCHASE TO OMAR15,000.0015,000.00
821/06/2023STOCK1100,000.00100,000.00
922/06/2023PURCHASE RETURNS FROM OMAR14,000.00-14,000.00
1022/06/2023PURCHASE LOW 6,200.006,200.00
1122/06/2023EXPENSE PR37,000.0037,000.00
1222/06/2023SALES201,000.00-201,000.00
1322/06/2023SALES RETURNS3,500.003,500.00
1422/06/2023PURCHASE RETURNS FROM ALI3,200.00-3,200.00
1523/06/2023SELLING LOW 3,201.00-3,201.00
1624/06/2023SALES FROM OMAR3,202.00-3,202.00
DAILY
Cell Formulas
RangeFormula
E2:E16E2=C2-D2


copy.xlsm
ABCDE
1ITEMACCOUNT NAMEDEBIT CREDITBALANCE
220/06/2023PURCHASE15,000.0015,000.00
322/06/2023PURCHASE RETURNS4,000.0011,000.00
424/06/2023SALES 3,202.007,798.00
5TOTAL15,000.007,202.007,798.00
OMAR
Cell Formulas
RangeFormula
E2,E5E2=C2-D2
E3:E4E3=E2+C3-D3
C5:D5C5=SUM(C2:C4)




copy.xlsm
ABCDE
1DATEACCOUNT NAMEDEBIT CREDITBALANCE
220/06/2023OPENING BALANCE200,000.00200,000.00
321/06/2023CASH PR 15,000.00215,000.00
422/06/2023CASH DM30,000.00185,000.00
523/06/2023CASH DM1,200.00183,800.00
6TOTAL215,000.0031,200.00183,800.00
ALI
Cell Formulas
RangeFormula
E2,E6E2=C2-D2
E3:E5E3=E2+C3-D3
C6C6=SUM(C2:C4)
D6D6=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
ABCDE
1DATEACCOUNT NAMEDEBIT CREDITBALANCE
222/06/2023EXPENSE ADMIN65,000.00-65,000.00
323/06/2023CASH DM1,200.00-1,200.00
421/06/2023STOCK1100,000.00100,000.00
522/06/2023PURCHASE LOW 6,200.006,200.00
622/06/2023EXPENSE PR37,000.0037,000.00
722/06/2023SALES201,000.00-201,000.00
822/06/2023SALES RETURNS3,500.003,500.00
923/06/2023SELLING LOW 3,201.00-3,201.00
DAILY
Cell Formulas
RangeFormula
E2:E9E2=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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.;)
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top