Help Deleting some blank rows based on a condition?

[Coyotex3]

Good afternoon, I have some codes which help me delete certain rows based on a blank cell, having a hard time getting it to go from this

Book1
	A	B	C	D	E	F	G	H
1	Date
2	Time
3	User
4	Rights
5
6	Number	City	Country	Description	Owed	Notes
7	0000012-000			School
8				Change	0
9
10	0000013-000			Playground
11	Central	LA	US	Park United	100	Busiest Location
12	West	LA	US	Park West	150	2nd Busiest
13				Change	250
14
15	0000015-000			Museum
16				Change	0
17
18	0000017-000			Concert
19	Europe	Paris	France	Venue1	50	100% Capacity
20	North America	NYC	USA	Venue2	100	95% Capacity
21	South America	Santiago	Chile	Venue3	150	95% Capacity
22	Africa	Lagos	Nigeria	Venue4	200	95% Capacity
23	Asia	Tokyo	Japan	Venue5	250	95% Capacity
24	Australia	Melbourne	Austrlia	Venue6	300	95% Capacity
25	Antartica	N/A	N/A	Venue7	450	0% Capacity
26				Change	1500
27
28
29
30
Sheet1

To this:

Book1
	A	B	C	D	E	F	G
1	Date
2	Time
3	User
4	Rights
5
6	Number	City	Country	Description	Owed	Notes
7	0000013-000			Playground
8	Central	LA	US	Park United	100	Busiest Location
9	West	LA	US	Park West	150	2nd Busiest
10	0000017-000			Concert
11	Europe	Paris	France	Venue1	50	100% Capacity
12	North America	NYC	USA	Venue2	100	95% Capacity
13	South America	Santiago	Chile	Venue3	150	95% Capacity
14	Africa	Lagos	Nigeria	Venue4	200	95% Capacity
15	Asia	Tokyo	Japan	Venue5	250	95% Capacity
16	Australia	Melbourne	Austrlia	Venue6	300	95% Capacity
17	Antartica	N/A	N/A	Venue7	450	0% Capacity
18
19
20
21
22
23
24
25
Sheet1

I'm going to have spreadsheets with variable ranges. I'm trying to get my code to look at Range("A7") and since there is nothing corresponding to this account (If the line below it is empty), delete all the rows up to the next non-blank cell which in this case would be until Range("A10"). Since there is data below Range("A10"), leave most of it alone, but row 13 should be deleted as there is no nothing important on that row. And repeat until the end of the spreadsheet.

Unsure what condition I can use to make this work. Any tips?

 

[Michael M]

Maybe like this but your explanation is a bit confusing

Sub MM1()
Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(4).EntireRow.Delete
End Sub

 

[Coyotex3]

Maybe like this but your explanation is a bit confusing

Sub MM1()
Range("A7:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(4).EntireRow.Delete
End Sub

Michael, thank you for your help and sorry for the confusing statements.

Essentially, I would like for the code to look at Column A, If Column A does not have anything below it i.e Range("A7"), then Delete all rows until it gets to the next non-blank cell which in this scenario is Range("A10").

Since Range("A10") has values beneath Range("A11:A12"), leave those alone, but since Range("A13") is blank, we can delete the entire row as well.

Hopefully this makes a little more sense.

 

[Michael M]

Did you try the code ??

 

[bebo021999]

Try this code:

Option Explicit
Sub delRows()
Dim lr&, i&, k&, rng, arr(1 To 10000, 1 To 1)
lr = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
Range("A7:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
lr = Cells(Rows.Count, "D").End(xlUp).Row
rng = Range("B7:B" & lr).Value
For i = 1 To UBound(rng) - 1
    If rng(i, 1) = "" And rng(i + 1, 1) = "" Then
            k = k + 1
            arr(k, 1) = i + 6 & ":" & i + 6
            GoTo z:
    End If
z:
Next
For i = 1 To k
    Range(arr(i, 1)).Delete
Next
End Sub

 

[Coyotex3]

Good afternoon, I have some codes which help me delete certain rows based on a blank cell, having a hard time getting it to go from this

Book1
	A	B	C	D	E	F	G	H
1	Date
2	Time
3	User
4	Rights
5
6	Number	City	Country	Description	Owed	Notes
7	0000012-000			School
8				Change	0
9
10	0000013-000			Playground
11	Central	LA	US	Park United	100	Busiest Location
12	West	LA	US	Park West	150	2nd Busiest
13				Change	250
14
15	0000015-000			Museum
16				Change	0
17
18	0000017-000			Concert
19	Europe	Paris	France	Venue1	50	100% Capacity
20	North America	NYC	USA	Venue2	100	95% Capacity
21	South America	Santiago	Chile	Venue3	150	95% Capacity
22	Africa	Lagos	Nigeria	Venue4	200	95% Capacity
23	Asia	Tokyo	Japan	Venue5	250	95% Capacity
24	Australia	Melbourne	Austrlia	Venue6	300	95% Capacity
25	Antartica	N/A	N/A	Venue7	450	0% Capacity
26				Change	1500
27
28
29
30
Sheet1

To this:

Book1
	A	B	C	D	E	F	G
1	Date
2	Time
3	User
4	Rights
5
6	Number	City	Country	Description	Owed	Notes
7	0000013-000			Playground
8	Central	LA	US	Park United	100	Busiest Location
9	West	LA	US	Park West	150	2nd Busiest
10	0000017-000			Concert
11	Europe	Paris	France	Venue1	50	100% Capacity
12	North America	NYC	USA	Venue2	100	95% Capacity
13	South America	Santiago	Chile	Venue3	150	95% Capacity
14	Africa	Lagos	Nigeria	Venue4	200	95% Capacity
15	Asia	Tokyo	Japan	Venue5	250	95% Capacity
16	Australia	Melbourne	Austrlia	Venue6	300	95% Capacity
17	Antartica	N/A	N/A	Venue7	450	0% Capacity
18
19
20
21
22
23
24
25
Sheet1

I'm going to have spreadsheets with variable ranges. I'm trying to get my code to look at Range("A7") and since there is nothing corresponding to this account (If the line below it is empty), delete all the rows up to the next non-blank cell which in this case would be until Range("A10"). Since there is data below Range("A10"), leave most of it alone, but row 13 should be deleted as there is no nothing important on that row. And repeat until the end of the spreadsheet.

Unsure what condition I can use to make this work. Any tips?

Did you try the code ??

When I read ran the code, It kept a few extra rows I would like to get rid of. This code kept rows A7, A10, A15, and A18. Ideally I would like to delete Rows A7 and A15 as well as there is no relevant information belonging to that group(anything below it).

 

[Coyotex3]

Try this code:

Option Explicit
Sub delRows()
Dim lr&, i&, k&, rng, arr(1 To 10000, 1 To 1)
lr = Cells(Rows.Count, "D").End(xlUp).Row
On Error Resume Next
Range("A7:A" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
lr = Cells(Rows.Count, "D").End(xlUp).Row
rng = Range("B7:B" & lr).Value
For i = 1 To UBound(rng) - 1
    If rng(i, 1) = "" And rng(i + 1, 1) = "" Then
            k = k + 1
            arr(k, 1) = i + 6 & ":" & i + 6
            GoTo z:
    End If
z:
Next
For i = 1 To k
    Range(arr(i, 1)).Delete
Next
End Sub

This one works. Thank you so much!