Delete Rows based on Cell content from multiple columns

[Shaner73]

MrExcel crew...

I have searched high and low, and I just can not find the code I need.

I need to Delete all rows from Columns A through E that contain the word TRUE.

What I have done is insert 5 columns and used some =IF, =LEFT, =RIGHT, =MID statements, copied and paste special.

Or, if someone has a nice generic macro that will do my statements and delete the Rows if "TRUE", that would be even better.

Any and all help is greatly appreciated.

 

[Shaner73]

Bump...please.

 

[TechTank]

Bump...please.

I've found this on the net:

Option Explicit

Sub Remove_True_Rows()

Dim SearchCriteria As String:   SearchCriteria = "TRUE"
Dim CriteriaRow As Long

    ActiveSheet.Range("A1").Activate
    On Error GoTo Next1
    CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=True, SearchFormat:=False).Row
    
    While CriteriaRow > 0
        ActiveSheet.Rows(CriteriaRow & ":" & CriteriaRow).Delete Shift:=xlUp
        On Error GoTo Next1
        CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False).Row
    Wend
    GoTo Next1

Next1:

The

GoTo Next1

can be anything but it basically give the loop some direction once the search criteria is no longer met.

I really hope this helps someone out as I'm very new to VBA and these forums too.

Regards,

Mark.

 

[TechTank]

Even better:

Try this for using multiple times in a macro if you have data imports in between:

Sub Delete_Rows_With_TRUE()

Dim SearchCriteria As String: SearchCriteria = "TRUE"

Set rFind = Cells.Find(What:=SearchCriteria, _
                           LookIn:=xlFormulas, _
                           LookAt:=xlWhole)
 
    Do While Not rFind Is Nothing
        rFind.EntireRow.Delete
        Set rFind = Cells.FindNext
    Loop

Or see my question posed here if you are unsure of what this will do or why it's better than my first post:

http://www.mrexcel.com/forum/showthread.php?t=578639

 

[Shaner73]

Thank you TT. I'll see if these will be helpful to me.

 

[Rick Rothstein]

I need to Delete all rows from Columns A through E that contain the word TRUE.

Is the word TRUE the only text in the cell or could there be other text in the cell with it? What is in the cells... constants or formulas?

 

[TechTank]

Hope it helps. I saw it was an old thread from last year but thought I'd share what I'd picked up from other members here.

The data I'm using it on is copied in below for two rows worth:

<table border="0" cellpadding="0" cellspacing="0" width="803"><colgroup><col style="mso-width-source:userset;mso-width-alt:2090;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:3456;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:1877;width:33pt" width="44"> <col style="mso-width-source:userset;mso-width-alt:1578;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:2858;width:50pt" width="67"> <col style="mso-width-source:userset;mso-width-alt:2773;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:2816;width:50pt" width="66"> <col style="mso-width-source:userset;mso-width-alt:1920;width:34pt" width="45"> <col style="mso-width-source:userset;mso-width-alt:2560;width:45pt" width="60"> <col style="mso-width-source:userset;mso-width-alt:2218;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:2432;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2346;width:41pt" width="55"> <col style="mso-width-source:userset;mso-width-alt:3669;width:65pt" width="86"> <col style="mso-width-source:userset;mso-width-alt:1664;width:29pt" width="39"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:37pt" align="right" height="17" width="49">59551</td> <td style="width:61pt" align="right" width="81">11</td> <td style="width:33pt" align="right" width="44">37</td> <td style="width:28pt" align="right" width="37">32767</td> <td style="width:50pt" align="right" width="67">2224723468</td> <td style="width:49pt" align="right" width="65">19</td> <td style="width:50pt" align="right" width="66">1</td> <td style="width:34pt" align="center" width="45">FALSE</td> <td class="xl66" style="width:45pt" align="right" width="60">40:27.0</td> <td style="width:39pt" align="right" width="52">90</td> <td style="width:43pt" align="right" width="57">60004756</td> <td style="width:41pt" align="right" width="55">10000030</td> <td style="width:65pt" align="right" width="86">30002543</td> <td style="width:29pt" align="right" width="39">1</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">60.63</td> <td align="right">838785</td> <td align="right">37</td> <td align="right">0</td> <td align="right">2288561839</td> <td align="right">1000000</td> <td align="right">1</td> <td align="center">TRUE</td> <td class="xl66" align="right">42:25.0</td> <td align="right">90</td> <td align="right">60005734</td> <td align="right">10000030</td> <td align="right">30002545</td> <td align="right">2</td> </tr> </tbody></table>

 

[Shaner73]

Is the word TRUE the only text in the cell or could there be other text in the cell with it? What is in the cells... constants or formulas?

In this case, it's a formula. I did make a macro that somewhat works...but I'm not using it too much now anyway.

It's all either =IF, =LEFT, =RIGHT, =MID formulas.