vba code to delete rows with string of text found

**yankeekid86** · 09-13-2011, 11:59 PM

i have a large spreadsheet with certain rows i need to delete if they have certain strings of text. i need coding to delete rows only if they contain any of the various certain strings of text as a whole. i found code that deletes rows if any single word is found, but i need it to find only exact words together (e.g. county of residence, - not just if any of those words are found elsewhere like when "of" is found in "zip code of residence"

so: i need to delete rows that contain "county or residence" or "age of respondent" or "zip code of employment"

I also need it to search 2 different columns rather than just one.

(i think macros would be the easiest solution??)
any help would be appreciated! thanks!

**gjlindn** · 09-14-2011, 12:20 AM

Maybe something like this?

Option Compare Text
Sub DeleteRowsMatchingText()
    Dim rFoundText  As Range
    Dim rLookRange  As Range
    Dim vText()     As Variant
    Dim vItem       As Variant
    Dim lCalc       As Long
    
    With Application
        lCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    vText = Array("County of Residence", "Age of Respondent", "Zip Code of Employment")
    Set rLookRange = Range("A:A,C:C") 'Change A:A and C:C to your search columns
    
    For Each vItem In vText
        Do
            Set rFoundText = rLookRange.Find(vItem, LookIn:=xlValues, lookat:=xlWhole)
            If Not rFoundText Is Nothing Then
                rFoundText.EntireRow.Delete
            End If
        Loop Until rFoundText Is Nothing
    Next
    
    Set rLookRange = Nothing
    Set rFoundText = Nothing
    
    With Application
        .ScreenUpdating = True
        .Calculation = lCalc
    End With
End Sub

**gjlindn** · 09-14-2011, 12:32 AM

How about this?

Option Compare Text
Option Explicit

Sub DeleteRowsMatchingText()
    Dim rFoundText  As Range
    Dim rLookRange  As Range
    Dim vText()     As Variant
    Dim vItem       As Variant
    Dim lCalc       As Long
    
    With Application
        lCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    vText = Array("County of Residence", "Age of Respondent", "Zip Code of Employment")
    Set rLookRange = Range("A:A,C:C") 'Change A:A and C:C to your search columns
    
    For Each vItem In vText
        Do
            Set rFoundText = rLookRange.Find(vItem, LookIn:=xlValues, lookat:=xlWhole)
            If Not rFoundText Is Nothing Then
                rFoundText.EntireRow.Delete
            End If
        Loop Until rFoundText Is Nothing
    Next
    
    Set rLookRange = Nothing
    Set rFoundText = Nothing
    
    With Application
        .ScreenUpdating = True
        .Calculation = lCalc
    End With
End Sub

**mudraker** · 09-14-2011, 01:18 AM

If you need to do this an a regular bases then a macor is a good way to go - It also depends on how many rows need to be checked.

It may alo possible to use formulas, manual sorting and manula deleting

The early part of thread

HTML Code:

http://www.excelforum.com/excel-programming/792012-remove-multiple-rows.html

has various methods of using a macro to search for a whole cell content in a single column
These various methods can be modified to search a 2nd column.

**yankeekid86** · 09-14-2011, 01:56 PM

works! thanks!!

**gjlindn** · 09-14-2011, 07:08 PM

Glad I could help

If you haven't already, you should also check out the link mudraker posted. It's great to be able to use vba to solve an issue, but with a little bit of formula and native Excel function knowledge you can decide for yourself if vba is appropriate. Have a great day!