VBA to delete all rows NOT equal to value in inputbox

[Dustinkli]

Hello,


I would like to be able to delete all rows in a spreadsheet based on a specified value in a selected column. I currently have a macro to delete a row based on specified cells but I'm not sure how to make it delete all rows with cells in a column EXCEPT a specified cell based on an input box. The one I have is based on .autofilter but I don't know if it has the capability to do what I need.

Basically I'd like to put the cell to preserve and it deletes all rows where that cell isn't in that column.

Can anyone point me in the right direction?

 

[Peter_SSs]

What about you post the code you have and it would probably just require a slight modification to do the new job.

About how many rows of data altogether are you likely yo have in your sheet?

 

[Dustinkli]

I think I found a solution:

Sub DeleteRows()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "DeleteRows"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Select Column Range :", xTitleId, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Text to Preserve", xTitleId, Type:=2)
For Each rng In InputRng
    If rng.Value <> DeleteStr Then
        If DeleteRng Is Nothing Then
            Set DeleteRng = rng
        Else
            Set DeleteRng = Application.Union(DeleteRng, rng)
        End If
    End If
Next
DeleteRng.EntireRow.Delete
 End Sub