VBA to delete all rows NOT equal to value in inputbox
Results 1 to 3 of 3

Thread: VBA to delete all rows NOT equal to value in inputbox
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to delete all rows NOT equal to value in inputbox

    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?

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,379
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: VBA to delete all rows NOT equal to value in inputbox

    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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to delete all rows NOT equal to value in inputbox

    I think I found a solution:

    Code:
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •