VBA to delete rows with conditions

[Sufiyan97]

I want to delete rows from below data
For example Column A has dates and B has names so for each date in column A I want to delete entire row if column B has more than four cells
in below data For first date want to delete row 6 and 7 for becuase they are rows 5 and 6 after first date
For second date I want to delete row 12 becuase it's 5th number after second date

Data

Book1
	A	B	C	D
1	Date	Name	Number
2	1/1/2022	A	1
3		B
4		C
5		D
6		E
7		F
8	1/2/2022	A	2
9		B
10		C
11		D
12		E
13	1/3/2022	A	3
14		B
15		C
16		D
17	1/4/2022	A	4
18		B
19		C
20		D
21		E
22		F
23	1/5/2022	A	5
24
Sheet1

Expected Result

Book1
	F	G	H
1	Date	Name	Number
2	1/1/2022	A	1
3		B
4		C
5		D
6	1/2/2022	A	2
7		B
8		C
9		D
10	1/3/2022	A	3
11		B
12		C
13		D
14	1/4/2022	A	4
15		B
16		C
17		D
18	1/5/2022	A	5
19
Sheet1

 

[Fluff]

How about

Sub Sufiyan()
   Dim rng As Range
   
   For Each rng In Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      If rng.Count > 3 Then rng.Offset(3).Resize(rng.Count - 3).EntireRow.Delete
   Next rng
End Sub

 

[Sufiyan97]

Thank you fluff that works Perfect
Can we force code to look for only dates in column A and ignore all other text?

 

[Fluff]

It's not looking for dates, it's looking for blank cells.

 

[Sufiyan97]

Ok,
So can we edit to see Date in column A and then 3 blank cells below that date (to keep those cells one with date and 3 blanks below that date)
and delete everything except above?

 

[Fluff]

So can we edit to see Date in column A and then 3 blank cells below that date (to keep those cells one with date and 3 blanks below that date)

That's what it does.

 

[Sufiyan97]

I meant like this, if possible

Data

Book1
	A	B	C
1	Date	Name	Number
2	1/1/2022	A	1
3		B
4		C
5		D
6	Any text other than date	E
7	Any text other than date	F
8	1/2/2022	A	2
9		B
10		C
11	Any text other than date	D
12	Any text other than date	E
13	1/3/2022	A	3
14		B
15	Any text other than date	C
16	Any text other than date	D
17	1/4/2022	A	4
18		B
19		C
20		D
21		E
22	Any text other than date	F
23	1/5/2022	A	5
Sheet1

Expected Result

Book1
	F	G	H
1	Date	Name	Number
2	1/1/2022	A	1
3		B
4		C
5		D
6	1/2/2022	A	2
7		B
8		C
9	1/3/2022	A	3
10		B
11	1/4/2022	A	4
12		B
13		C
14		D
15		E
16	1/5/2022	A	5
Sheet1

 

[Fluff]

As long as the dates are real date try

Sub Sufiyan()
   Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlConstants, xlTextValues).EntireRow.Delete
End Sub

 

[Sufiyan97]

Hello Fluff

Both of the solutions works for different situations

Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.