Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts

    Show message Box and delete rows

    I would like macro that will show a mesage box so that I can enter a number. Once I enter the number , then all the rows in Column A (Sheet2) containing this number must be highlighted and the rows deleted . i.e Once the number Is found for eg 85174, I want all the rows containing this number in Col A on ) on Sheet2 to be deleted

    Your assistance in this regard is most appreciated


    http://www.mrexcel.com/forum/excel-q...lete-rows.html

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Howard,

    Try this:

    Code:
    Public Sub GetNextCell()
    On Error GoTo done
    x = InputBox("Enter Number")
    Do
        Set Keyword = Cells.Columns("A").Find(What:=x)
        Keyword.Select
        ActiveCell.EntireRow.Delete
    Loop
    Exit Sub
    done:
    End Sub
    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2013-08-30)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the help. Code works perfectly

    Regards

    Howard

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hey Y'all,

    Here's a slightly different take on Maud's code that also allows selection of the search column and traps user input errors (e.g. no input or cancel button).
    Code:
    Option Explicit
    
    Public Sub DeleteSelected()
    
       Dim bFound        As Boolean
       Dim zValueToFind  As String
       Dim zSearchCol    As String
       
       zSearchCol = InputBox("Enter the column to Search")
       If zSearchCol = "" Then Exit Sub 'No user input or user Cancelled
       
       zValueToFind = InputBox("Enter Value to Find")
       If zValueToFind = "" Then Exit Sub 'No user input or user Cancelled
       
       On Error Resume Next
      
       Do
         bFound = False
         bFound = Cells.Columns(zSearchCol).Find(What:=zValueToFind).Select
         If bFound Then ActiveCell.EntireRow.Delete
       Loop Until Not bFound
       
    End Sub
    Note: you can add an error message to the If statements following the InputBox's if desired.

    HTH

    VBA - Excel - Delete Rows containing Specified Value in Designated Column.xlsm
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2013-08-30)

  7. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi RG

    Impressive filename!
    ..for the benefit of others, the maximum length for the full path including the file name and extension cannot exceed 258 characters.

    zeddy

  8. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Yeah, I know it's long but when I go looking for examples it sure helps.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,422
    Thanks
    125
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the input

Posting Permissions

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