Deleting Rows with certain text strings

[viraim]

Hi,

Sorry if this is not an appropriate post, but I know nothing about Excel VBA and macros, , but wish to automate the following task, and am hoping someone can pass me some VBA code that would work:

I wish to delete certain rows in my spreadsheet that do not include each of three seperate text strings (we can call them tstring1, tstring2 & tstring3).

Thanks in advance for your help.

Vince Raimondi
Manager of Marketing Sciences
The Marketing Workshop, Inc.
Norcross, GA

 

[stanleydgromjr]

viraim,

Welcome to the MrExcel board.

A screenshot will help us create the code for you.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Gary McMaster]

Here's a quick and dirty version of what I think you're asking.

Change the tstring1, 2 & 3 values (lines 11,12,13) to whatever you need.

The delete method is commented out (last line). You can test it with the color change "oUnion.Interior.ColorIndex = 3" before you buy it.

Hope it helps.

Gary

Dim oCell As Range
Dim lRows As Long
Dim lCols As Long
Dim lRowCount As Long
Dim lColCount As Long
Dim iFlags As Integer
Dim oUnion As Range
Dim tstring1 As String
Dim tstring2 As String
Dim tstring3 As String
 
tstring1 = "Text 1 to search for"
tstring2 = "Text 2 to search for"
tstring3 = "Text 3 to search for"
 
lRows = ActiveSheet.UsedRange.Rows.Count
lCols = ActiveSheet.UsedRange.Columns.Count
 
For lRowCount = 1 To lRows
    For lColCount = 1 To lCols
    
        If InStr(1, UCase(ActiveSheet.Cells(lRowCount, lColCount)), UCase(tstring1)) Then
            iFlags = iFlags Or 1
        End If
        
        If InStr(1, UCase(ActiveSheet.Cells(lRowCount, lColCount)), UCase(tstring2)) Then
            iFlags = iFlags Or 2
        End If
        
        If InStr(1, UCase(ActiveSheet.Cells(lRowCount, lColCount)), UCase(tstring3)) Then
            iFlags = iFlags Or 4
        End If
    Next lColCount
    
    If iFlags <> 7 Then
        If oUnion Is Nothing Then
            Set oUnion = ActiveSheet.Rows(lRowCount)
        Else
            Set oUnion = Union(oUnion, ActiveSheet.Rows(lRowCount))
        End If
    End If
    
    iFlags = 0
    
Next lRowCount
 
oUnion.Interior.ColorIndex = 3
'oUnion.Rows.Delete