Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

VBA - Delete entire row if cell contains search string

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA - Delete entire row if cell contains search string

    Trying to get something that seems like it should be simple. I want to enter a search string into an inputbox, and then loop through a selected range deleting any row that does not contain that search string. Whenever I run this, it seems to delete a random assortment of rows. Then if I run it again, it will delete different, new rows. Below is my code snippet. What am I doing wrong here? If I change cell.EntireRow.Delete to MsgBox cell.value, then it loops through and shows all the correct values in the rows that I would expect to be deleted.

    Code:
    Sub DeleteRows()
        Dim c As Range
        Dim cell As Range
        Dim SrchRng As Range
        Dim SrchStr As String
    
        Set SrchRng = ActiveSheet.Range("B1:B25")
        SrchStr = InputBox("Please Enter A Search String")
        For Each cell In SrchRng
            If cell.Value <> SrchStr Then cell.EntireRow.Delete
        Next cell
    End Sub

  • #2
    Re: VBA - Delete entire row if cell contains search string

    Ah. I think it's deleting the entire row, but since the cells are all shifting up, when it goes to the "next" cell it's skipping the row that had just been shifted up. If I use clearcontents instead so that nothing gets shifted, then it works fine. Add code to delete rows with empty cells at the end, and it mostly works how I imagined.

    Code:
    Sub DeleteRows()
        Dim c As Variant
        Dim cell As Range
        Dim SrchRng As Range
        Dim SrchStr As String
        Dim RangeDel As Range
    
        Set SrchRng = ActiveSheet.Range("B1:B25")
        SrchStr = InputBox("Please Enter A Search String")
        For Each c In SrchRng
            If c.Value <> SrchStr Then
            Range(c.Address).EntireRow.ClearContents
            End If
        Next c
        SrchRng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
    End Sub
    Is there a better workaround? Should I be using something other than a for each loop maybe?

    Comment


    • #3
      Re: VBA - Delete entire row if cell contains search string

      Hi GBagley

      Its better to use "For..Next" rather than "For..Each".

      Code:
       
      Sub DeleteRows()
          Dim c As Range
          Dim cell As Range
          Dim SrchRng As Range
          Dim SrchStr As String
          Set SrchRng = ActiveSheet.Range("B1:B25")
          SrchStr = InputBox("Please Enter A Search String")
          For i = SrchRng.Rows.Count To 1 Step -1
              If Cells(i, "B") <> SrchStr Then Rows(i).Delete
          Next i
      End Sub
      But if there are large number of rows, it is better to use Auto filter rather than loop.

      For detailed understanding visit the following page:
      http://www.rondebruin.nl/delete.htm

      Ron is great :-)

      This is my 1st reply ever on this forum...Hope it helps..Thanks!!!

      Comment


      • #4
        Re: VBA - Delete entire row if cell contains search string

        I am a relative beginner trying to do VBA, so I am in the copy and modify writing stage - on a good day.

        I would like to modify this macro such that is looks at the value in another sheet rather than ask you to input a value. for example, On my sheet called "updatedata" and have a cell (Y13)that automatically displays a string based upon various rules. I then want to jump over to my data sheet and entirely remove the row of data that has this string in the first column. So, i have tried this macro
        Code:
        Sub DeleteRows()
            Dim c As Range
            Dim cell As Range
            Dim SrchRng As Range
            Dim SrchStr As String
            
            Sheets("updatedata").Select
            SrchStr = ActiveSheet.cell("Y13").Value
            Sheets("datasummary").Select
            Set SrchRng = ActiveSheet.Range("a1:a10000")
        '   SrchStr = InputBox("Please Enter A Search String")
            For Each cell In SrchRng
                If cell.Value = SrchStr Then cell.EntireRow.Delete
            Next cell
        End Sub
        Thanks for any help, but it fails to run.
        Last edited by AAE; May 15th, 2012, 05:48. Reason: delete quote, add code tags

        Comment


        • #5
          Re: VBA - Delete entire row if cell contains search string

          teckstein,

          Welcome to Ozgrid.

          Please read the forum rules and learn to use code tags.
          When posting VBA code, you must use code tags - I've added them for you this time.

          All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.
          I've added the tags for you this time only. Be sure to use them in future posts.

          How to use code tags

          [code]
          your code goes between these tags
          [/code]

          Or, just highlight all of the code and press the # button to add the code tags

          Please do not quote entire replies in your posts.
          1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
          2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

          This will keep thread clutter to a minimum and make the discussion easier to follow.


          Lastly, always start a new thread for your own questions.
          Start a new thread, please.

          You can help by posting a sample workbook (dummy data, exact structure) to provide accurate context and clarity for those helping with the solution.
          AAE
          ----------------------------------------------------

          Forum Rules | Message to Cross Posters | How to use Tags

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X