Delete a row if it doesn't have anything below it

[chenmoti]

Good day everyone,

I want to program a button that can delete a row if it doesn't have anything below it. Here's an example of what I'm talking about.

Initial Example:

By clicking a button, the result should be like this:

It will delete Region C and E because it doesn't have any name below it.

 

[Joe4]

So you need a way to differentiate the names from other strings.
Will ALL the non-name entries ALWAYS begin with the phrase "From Region..."?

 

[Joe4]

If that assumption above is true, then this should work for you:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows backwards, up to row 2
    For r = lr To 2 Step -1
'       See current row starts with "From Region"
        If Left(Cells(r, "A"), 11) = "From Region" Then
'           See if row below is also "From Region", or row below is empty
            If (Left(Cells(r + 1, "A"), 11) = "From Region") Or _
                (Cells(r + 1, "A") = "") Then
'               Delete row
                Rows(r).Delete
            End If
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub