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]

Oops, you are right there. I said doesn't when I should have said does.

 

[johnnyL]

Now that is solved, did you test @Peter_SSs code?

 

[Maklil]

@Peter_SSs
your code is too fast , awesome !
but I need fixing two thing
when delete some ranges , it doesn't delete correctly the data doesn't become arranged !

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/19/2000	CSS-108	INV-A131	ITTT-100*12GF	1000
11	1/7/2023	CSS-108	INV-A131	ITTT-100*12GF	2
12	1/7/2023	CSS-108	INV-A131	ITTT-100*12GF	2
13	TOTAL				4
14
15
16	DATE	CUSTOMER	INV NO	ITEM	QTY
17	11/20/2000	CSS-109	INV-A132	ITTT-100*12GF	1100
18	1/7/2023	CSS-109	INV-A132	ITTT-100*12GF	200
19	1/7/2023	CSS-109	INV-A132	ITTT-100*12GF	20
20	TOTAL				220
21
OUTPUT

second the data should show OUTPUT SHEET
to see closly problem formatting after delete it see the picture .

 

[Peter_SSs]

data should show OUTPUT SHEET

OK, I will fix that. Does OUTPUT already exist in the workbook?
If so, can I delete it with the code and make a new one?

when delete some ranges , it doesn't delete correctly the data doesn't become arranged !

I don't understand that part. With your sample data from post #1 my code seemed to me to produce exactly what you showed as the output (apart from not being on a different sheet)
Can you give a small set of sample data where my code does not do what you want and also post the results that you do want for that sample data?

 

[Maklil]

Does OUTPUT already exist in the workbook?

yes

If so, can I delete it with the code and make a new one?

Do you mean making new code from scratch ?
it's up to you buddy.

my code seemed to me to produce exactly what you showed as the output (apart from not being on a different sheet)

yes surley without any doubt .
just the problem is relating with formatting for each range when delete some ranges and move some ranges to up then the formatting becomes untidy
see the picrure before

after run the macro

I hope this help you.

 

[Peter_SSs]

then the formatting becomes untidy

From your 'before' picture it looks like the formatting already is untidy in that the blocks of data are not uniformly spaced with 2 blank rows between every block like there was in your post #1 sample.
Is that the problem you are referring to?

My code simply deleted whole rows and leaves the remaining rows formatted as they were. If the issue is something other than the number of blank rows then I would think that the formatting problems must already be there.

In any case I can not do any testing with a picture so if there are other issues can you give a small sample with XL2BB that demonstrate those other issues?

 

[Maklil]

just when I posted the pictures instead of using XL2BB to see clearly how the formatting was for each range .
for me it doesn't seem to show as in picture when using XL2BB .
ok I make sure there are spaced with 2 blank rows between every block like there was in your post #1 sample. even if that the problem still continues.
here is orginal data before

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	300
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				390
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	40
12	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-2	50
13	TOTAL				300
14
15
16	DATE	CUSTOMER	INV NO	ITEM	QTY
17	11/15/2000	CSS-100	INV-A124	ITTT-100/AS-2	600
18	1/7/2023	CSS-100	INV-A124	ITTT-100/AS-2	200
19	1/7/2023	CSS-100	INV-A124	ITTT-100/AS-2	400
20	TOTAL				0
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	900
26	TOTAL				100
27
28
29	DATE	CUSTOMER	INV NO	ITEM	QTY
30	5/7/2003	CSS-1000	INV-A1030	ITTT-100/AS-902	2493
31	1/7/2023	CSS-1000	INV-A1030	ITTT-100/AS-902	1000
32	1/7/2023	CSS-1000	INV-A1030	ITTT-100/AS-902	400
33	1/7/2023	CSS-1000	INV-A1030	ITTT-100/AS-902	1000
34	1/7/2023	CSS-1000	INV-A1030	ITTT-100/AS-902	93
35	TOTAL				2493
36
37
38	DATE	CUSTOMER	INV NO	ITEM	QTY
39	5/8/2003	CSS-1001	INV-A1031	ITTT-100/AS-903	2494
40	TOTAL				2494
41
42
43	DATE	CUSTOMER	INV NO	ITEM	QTY
44	5/9/2003	CSS-1002	INV-A1032	ITTT-100/AS-904	2495
45	TOTAL				2495
46
47
48	DATE	CUSTOMER	INV NO	ITEM	QTY
49	5/10/2003	CSS-1003	INV-A1033	ITTT-100/AS-905	2496
50	TOTAL				2496
DETAILS

this is what I got after run the macro

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	300
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				390
7
8
9	DATE	CUSTOMER	INV NO	ITEM	QTY
10	11/17/2000	CSS-100	INV-A129	ITTT-100/AS-7	800
11	1/7/2023	CSS-100	INV-A129	ITTT-100/AS-7	900
12	TOTAL				100
OUTPUT

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	300
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				390
7
8
9	DATE	CUSTOMER	INV NO	ITEM	QTY
10	11/17/2000	CSS-100	INV-A129	ITTT-100/AS-7	800
11	1/7/2023	CSS-100	INV-A129	ITTT-100/AS-7	900
12	TOTAL				100
OUTPUT

 

[bebo021999]

Again:

Option Explicit
Sub delRange()
Dim lr&, i&, j&, t&, seri, rng
Sheets("DETAILS").Copy after:=Sheets("DETAILS") ' dupplicate sheet DETAILS
If Evaluate("=ISREF(OUTPUT!A1)") Then Sheets("OUTPUT").Delete ' delete the old OUTPUT sheet
ActiveSheet.Name = "OUTPUT"
lr = Cells(Rows.count, "A").End(xlUp).Row
rng = Range("A1:F" & lr).Value
For i = 1 To UBound(rng) - 1
    rng(i, 6) = ""
    If rng(i, 1) = "DATE" Then
        For j = i + 1 To UBound(rng)
            If rng(j, 1) = "TOTAL" Then
                If rng(j, 5) <> 0 And rng(j, 5) <> rng(i + 1, 5) Then
                    For t = i To IIf(j >= UBound(rng), UBound(rng), j + 2)
                        seri = seri + 1
                        rng(t, 6) = seri
                    Next
                    i = j + 1
                End If
                Exit For
            End If
        Next
    End If
Next
With Range("A1").Resize(UBound(rng), 6)
    .Value = rng
    .Sort Range("F1")
End With
lr = Cells(Rows.count, "F").End(xlUp).Row
Range(Cells(lr + 1, 1), Cells(Rows.count, 1)).EntireRow.Delete
Columns("F").ClearContents
MsgBox "Done!"
End Sub

 

[Alex Blakenburg]

I believe the issue is that the Borders don't move with the sort. The XL2BB does not include the borders so they are not in our test data.

 

[Maklil]

@bebo021999
thanks again !
your first code does every thing , the only problem is running speed .
the second code is very fast . the only problem is formatting
see this picture and compare with picture for post#17 how 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	DATE	CUSTOMER	INV NO	ITEM	QTY
9	11/12/2000	CSS-100	INV-A123	ITTT-100/AS-2	300
10	1/7/2023	CSS-100	INV-A123	ITTT-100/AS-2	400
11	TOTAL				400
12
OUTPUT