Not Deleting Selected Rows

[OldRookie]

May I ask for your help with a row deletion problem that’s opposite of everything I’ve been able to find on my own? Instead of deleting selected rows in a specific range, I want to delete all the rows in a specific range that aren’t selected. I want to keep them. It’s sort of the negative/compliment/opposite to what I’ve been able to find. The use I envision is selecting the rows and pushing a form button that runs this code in a dedicated macro. Can this be done?

 

[Trebor76]

Hi OldRookie,

Try this while on the sheet with the data:

Option Explicit
Sub Macro1()

    Dim rngKeep As Range, rngAllData As Range, rng As Range, rngDelete As Range
    
    Application.ScreenUpdating = False
    
    Set rngKeep = Selection
    Set rngAllData = ActiveSheet.UsedRange
    
    For Each rng In rngAllData.Rows
        If Intersect(rng, rngKeep.Rows) Is Nothing Then
            If rngDelete Is Nothing Then
                Set rngDelete = rng
            Else
                Set rngDelete = Union(rngDelete, rng)
            End If
        End If
    Next rng
    
    If Not rngDelete Is Nothing Then
        rngDelete.EntireRow.Delete
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[OldRookie]

Robert,
Wow! That works great! Even better, you used things I’ve never seen before. I’m looking forward to researching them out to understand and improve my skills.

Thank you so much!

 

[Trebor76]

Robert,
Wow! That works great! Even better, you used things I’ve never seen before. I’m looking forward to researching them out to understand and improve my skills.

Thank you so much!

Thanks for letting us know and I'm glad we were able to provide you a suitable solution