Results 1 to 11 of 11

Help required - User input for text search and delete remaining rows

Threaded View

  1. #1
    Registered User
    Join Date
    08-18-2015
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    9

    Help required - User input for text search and delete remaining rows

    Hello,

    First post and very much a noob, so please go easy on me.

    Initially my idea was to have the user enter a text search term via an input box and for this term to be highlighted blue and all other rows (not containing the search term) to be deleted/removed.

    I have searched and searched for the answer but come up with no joy

    So I have botched this crazy work around (See code below) using the interior cell colours to indicate which rows require deleting. This strange method does work, except for when the user enters a search term that is NOT contained within the data, all rows (because they are white!) are deleted. Can I somehow add msgbox to intervene if the search term isn't found? I've tried to do this but unfortunately with no success. Or failing that can someone come up with a bit of smart code to do exactly what I originally required - Search worksheet based on user input, highlight matches, delete all other rows that do not contain a match.

    Hopefully you wonderful people can help.


    Sub DeleteRows()
    
        Dim SrchStr, Prompt, Title As String
        Dim rCell As Range
        Dim x As Long
        Dim y As Long
        Dim rngTempb As Range
        Dim LastRow As Long
        Dim i As Long
         
        Application.ScreenUpdating = False
         
        
    startSearch1:
        Title = "Please enter Search Term?"
        SrchStr = Application.InputBox(Prompt, Title, "")
        
        If SrchStr = False Then Exit Sub
    'Force valid entry
         If SrchStr = "" Then
           If MsgBox("The Search Field Can Not Be Left Blank" _
           & vbLf & vbLf & "Do You Want To Try Again?", vbYesNo + vbQuestion, "...") = _
                            vbNo Then Exit Sub
              GoTo startSearch1
         End If
        
        Set rngTempb = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not rngTempb Is Nothing Then
        Range(Cells(1, 1), rngTempb).Select
    End If
        
        y = Len(SrchStr)
     For Each rCell In Selection
         x = 1
         Do
           x = InStr(x, UCase(rCell.Value), UCase(SrchStr))
           If x > 0 Then
               rCell.Characters(x, y).Font.Color = vbBlue
               rCell.Characters(x, y).Font.Bold = True
               rCell.EntireRow.Interior.Color = RGB(255, 255, 254)
               x = x + 1
                End If
         Loop Until x = 0
     Next rCell
        
        LastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row - 1
        
        For i = LastRow To 2 Step -1
            If Cells(i, "A").Interior.Color = RGB(255, 255, 255) Or Cells(i, "A").Interior.Color = RGB(218, 150, 148) Then
                Rows(i).Delete
            End If
        Next i
        
        
      Application.ScreenUpdating = True
           
    End Sub
    Last edited by ssss2005; 08-19-2015 at 05:31 AM. Reason: Updated prefix to [SOLVED]

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to filter 2 columns and then delete remaining rows
    By jimmisavage in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2014, 11:01 AM
  2. [SOLVED] Delete rows, then reorganise remaining data
    By Steve_123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2014, 04:08 AM
  3. Automatically Add/Delete rows based on user input but check current table row count
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2011, 11:39 AM
  4. Code will delete first 2 rows but not the remaining 10
    By 00Able in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2011, 07:16 AM
  5. Replies: 4
    Last Post: 09-08-2010, 11:17 AM
  6. Find data and delete remaining rows (For each loop)
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-24-2009, 11:45 AM
  7. Delete rows based on user input
    By Militia in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-10-2009, 06:33 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1