+ Reply to Thread
Results 1 to 6 of 6

If Or statement to check cell text and delete entire row if text is found

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    If Or statement to check cell text and delete entire row if text is found

    I'm having a little trouble getting this sorted. In column A I have a list of letters: A, B, D, M and N. I need to loop through each cell, and delete all rows that contain B, M, N, as well as blanks. Here's my code:

    Sub Main()
    Dim lastrow As Double
    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    For Each cell In ThisWorkbook.ActiveSheet.Range("A1:A" & lastrow)
        If cell.Text = "B" Or cell.Text = "M" Or cell.Text = "N" Or cell.Text = "" Then
        cell.EntireRow.Delete
        End If
    Next cell
    
    End Sub
    My problem here is this isn't deleting the rows that contain the letter N. If I run the macro, it deletes rows that contain B, M, and blanks, and then if I run it a second time, it WILL delete the rows containing the letter N. Any ideas on what's going on here? I've attached a workbook with the code contained in a module. Thanks
    Attached Files Attached Files
    Last edited by VBA FTW; 08-08-2013 at 11:31 AM.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: If Or statement to check cell text and delete entire row if text is found

    Sub Main()
    Dim lastrow As Double
    lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = lastrow to 1 step -1
        If cells(r,1).Text = "B" Or cells(r,1).Text = "M" Or cells(r,1).Text = "N" Or cells(r,1).Text = "" Then
          rows(r).Delete
        End If
    Next
    
    End Sub
    If solved remember to mark Thread as solved

  3. #3
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: If Or statement to check cell text and delete entire row if text is found

    Thanks, patel45! Any way I could talk you into explaining what's wrong with my approach?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: If Or statement to check cell text and delete entire row if text is found

    When you delete a row it knackers up the placement in the loop and effectively skips a row.

    It is not actually ignoring the N's, that's just how it appears...

    stepping through the loop backwards fixing this as deleting a row doesn't alter the rows above it.

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: If Or statement to check cell text and delete entire row if text is found

    Alternate version...
    Sub Main()
        
        Dim ACell As Range
        Dim rngDel As Range
        
        For Each ACell In Range("A1", Cells(Rows.Count, "A").End(xlUp)).Cells
            Select Case ACell.Text
                Case "B", "M", "N", ""
                    If rngDel Is Nothing Then Set rngDel = ACell Else Set rngDel = Union(rngDel, ACell)
            End Select
        Next ACell
        
        If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
        
        Set ACell = Nothing
        Set rngDel = Nothing
        
    End Sub
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Contributor
    Join Date
    07-26-2012
    Location
    USA
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    351

    Re: If Or statement to check cell text and delete entire row if text is found

    Great, that makes a lot of sense, thanks everyone!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Check for values in a table and if found add value found in column to left to list
    By robhargreaves in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2013, 02:57 PM
  2. [SOLVED] Macro To Delete Entire Row If Dublicate Is Found
    By SilverFox in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2012, 06:07 AM
  3. Replies: 5
    Last Post: 12-07-2005, 06:30 PM
  4. How to delete values of a cell if it is found in another coloumn
    By karty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2005, 12:05 PM
  5. Check if cell value is found in a seperate range of values
    By wilby31 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2005, 08:05 PM

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