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

chenmoti

New Member
Joined
Oct 5, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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:
1666103184358.png


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


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

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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..."?
 
Upvote 0
If that assumption above is true, then this should work for you:
VBA Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top