Delete 100000 rows based on matching the value for first row with last row

[Maklil]

Hello
I have multiple ranges in Details sheet if the second row (under header for each range contains value is the same value where is in lastrow(TOTAL) in column E or the lastrow(TOTAL) contains zero in column E then should delete the ranges , otherwise should keep the rest
I have about 100000 rows and increasable .

Merging ranges_3.xlsb
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2000	CSS-100	INV-A123	ITTT-100/AS-1	200
3	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
4	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	40
5	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	50
6	TOTAL				190
7
8
9	DATE	CUSTOMER	INV NO	ITEM	QTY
10	11/12/2000	CSS-100	INV-A123	ITTT-100/AS-2	300
11	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-2	400
12	TOTAL				400
13
14
15	DATE	CUSTOMER	INV NO	ITEM	QTY
16	11/15/2000	CSS-100	INV-A124	ITTT-100/AS-2	600
17	1/7/2023	CSS-100	INV-A124	ITTT-100/AS-2	300
18	1/7/2023	CSS-100	INV-A124	ITTT-100/AS-2	100
19	1/7/2023	CSS-100	INV-A124	ITTT-100/AS-2	200
20	TOTAL				600
21
22
23	DATE	CUSTOMER	INV NO	ITEM	QTY
24	11/17/2000	CSS-100	INV-A129	ITTT-100/AS-7	800
25	1/7/2023	CSS-100	INV-A129	ITTT-100/AS-7	500
26	1/7/2023	CSS-100	INV-A129	ITTT-100/AS-7	200
27	1/7/2023	CSS-100	INV-A129	ITTT-100/AS-7	100
28	TOTAL				0
DETAILS

should be

Merging ranges_3.xlsb
	A	B	C	D	E
1	DATE	CUSTOMER	INV NO	ITEM	QTY
2	11/11/2000	CSS-100	INV-A123	ITTT-100/AS-1	200
3	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	100
4	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	40
5	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-1	50
6	TOTAL				190
7
8
9	DATE	CUSTOMER	INV NO	ITEM	QTY
10	11/12/2000	CSS-100	INV-A123	ITTT-100/AS-2	300
11	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-2	400
12	TOTAL				400
OUTPUT

thanks

 

[johnnyL]

Sorry, I don't understand what you are asking for based on your examples.

 

[bebo021999]

If I understood correctly. Loop through each non contiguous range in column E, then delete rows.
It employ a helper column (col ZZ in this sample) to help code faster.

Option Explicit
Sub delete()
Dim lr&, i&, area, fi As Range, se As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row ' last used cell in column A
Set area = Range("E1:E" & lr).SpecialCells(xlCellTypeConstants, xlNumbers).Areas ' collection of non contiguous range
For i = 1 To area.Count ' loop thru each non contiguous range
    Set fi = area(i).Cells(1, 1): Set se = area(i).Cells(area(i).Count, 1) 'fi = first cell, se = last cell
    If fi.Value = se.Value Or se.Value = 0 Then
        Range(fi.Offset(-1, 0), se.Offset(2, 0)).Offset(, 697).Value = "x" ' input "x" into helper column "ZZ"
    End If
Next
With ActiveSheet.Range("A1:ZZ" & lr + 1)
    .AutoFilter field:=702, Criteria1:="x" ' filter column ZZ then delete rows with "x"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.delete
    .AutoFilter
End With
End Sub

 

[Maklil]

@johnnyL
just take range from row15:20 see the number in column E from second row(ROW16) the number is 600 and the lastrow in TOTAL (ROW20) the number is 600 then it's the same value should delete the whole this range .
as to
range from row23:28 see the number in column E for the lastrow in TOTAL (ROW28) the number is 0 then it's the should delete the whole this range.

 

[Peter_SSs]

With such large data and potentially many disjoint ranges to delete, I think that you should find this much faster.

Sub Del_Blocks()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, j As Long, k As Long, fr As Long, rws As Long
  Dim v As Double, tot As Double
 
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("E1", Range("E" & Rows.Count).End(xlUp).Offset(4)).Value
  a(UBound(a) - 1, 1) = "QTY"
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    Select Case a(i, 1)
      Case "QTY"
        If rws > 0 Then
          If a(i - 3, 1) = v Or a(i - 3, 1) = 0 Then
            For j = fr To fr + rws - 1
              k = k + 1
              b(j, 1) = 1
            Next j
          End If
        End If
        fr = i
        v = a(i + 1, 1)
        rws = 1
      Case Else
        rws = rws + 1
    End Select
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

@bebo021999
It is not a good idea to name a procedure the same as any word that vba already uses (eg "delete")

 

[johnnyL]

@johnnyL
just take range from row15:20 see the number in column E from second row(ROW16) the number is 600 and the lastrow in TOTAL (ROW20) the number is 600 then it's the same value should delete the whole this range .

That part I totally get.

@johnnyL

as to
range from row23:28 see the number in column E for the lastrow in TOTAL (ROW28) the number is 0 then it's the should delete the whole this range.

That is where I get lost.

second row(ROW24) the number is 800 and the lastrow in TOTAL (ROW28) the number is 0

So why should that range be deleted? The second row column E doesn't = the total row column E in that range. ???

 

[Maklil]

So why should that range be deleted? The second row column E doesn't = the total row column E in that range. ???

Then just search for zero in last Total row without see the second row .

 

[johnnyL]

Then just search for zero in last Total row without see the second row .

I must have missed the post where you previously mentioned:
If 2nd row Column E doesn't = the Total Row Column E, or the Total Row column E = 0 then ...

 

[Maklil]

@bebo021999
thanks but seem to be slow gives about 20sec and should show in sheet OUTPUT I want keeping the orginal data in DETAILS sheet .

 

[Maklil]

If 2nd row Column E doesn't = the Total Row Column E, or the Total Row column E = 0 then ...

the right is :
If 2nd row Column E = the Total Row Column E, or the Total Row column E = 0 then should delete it