Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA - Delete entire row if cell contains search string

  1. #1
    Join Date
    17th September 2010
    Posts
    20

    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th September 2010
    Posts
    20

    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.

    VB:
    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?

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th January 2011
    Posts
    11

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

    Hi GBagley

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

    VB:
     
    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!!!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th May 2012
    Posts
    1

    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
    VB:
    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 at 05:48. Reason: delete quote, add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Delete Entire Row If Cell Contains String
    By sharisent in forum EXCEL HELP
    Replies: 7
    Last Post: October 6th, 2013, 06:48
  2. Search For String & Delete All Rows Above
    By goat312 in forum EXCEL HELP
    Replies: 10
    Last Post: February 6th, 2008, 14:19
  3. Delete entire row based on cell
    By alanandrade in forum EXCEL HELP
    Replies: 2
    Last Post: March 2nd, 2006, 22:07
  4. Replies: 4
    Last Post: February 2nd, 2006, 23:55
  5. Delete entire worksheet from certain cell
    By Eeuw in forum EXCEL HELP
    Replies: 6
    Last Post: January 28th, 2005, 00:33

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
  •  
porno