Delete rows when specific words and criteria are found

[man]

Hi,

I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net...............................big tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row 3......thebigshortdog.net........................the big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table


Thanks <!-- / message --> <!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --> <!-- END TEMPLATE: ad_showthread_firstpost_sig --> <!-- edit note -->

 

[kpark91]

To delete an Entire Row, Use:

Rows(rowNumber).EntireRow.Delete

To concatenate the rightmost letters, Use Right function

Right$(string, numberofletters)

To find a certain combination of letters, use InStr function

InStr(string1, string2)

 

[man]

kpark91, I am new to this. How do I use the code you provided?

 

[kpark91]

Copy and Paste this code into VBE

by right clicking your worksheet's tab -> View Tab

Sub Test()
   Dim last As Integer

    last = Range("A" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False
    
    For c = 1 To last
        If LCase(Left$(Range("A" & c).Value, 3)) = "big" Then
             Rows(c).EntireRow.Interior.Color = RGB(255,255,0)
        ElseIf LCase(Left$(Range("A" & c).Value, 5)) = "black" Then
            Rows(c).EntireRow.Interior.Color = RGB(255,255,0)
        ElseIf LCase(Right$(Range("A" & c).Value, 4)) = ".net" Then
            Rows(c).EntireRow.Interior.Color = RGB(255,255,0)
        ElseIf Instr(LCase(Range("A" & c).Value), "tall") > 0 Then
            Rows(c).EntireRow.Interior.Color = RGB(255,255, 0)
        End If
    Next c
    

    For c = 1 To Last
        If Rows(c).EntireRow.Color = RGB(255,255,0) Then
            Rows(c).EntireRow.Delete
        End If
    Next c

End Sub

 

[man]

I right clicked on the tab, then clicked on 'view code', then a Microsoft Visual Basic window pops up. I copy and paste the code you posted, then what shall I do? I am using Excel 2002

 

[kpark91]

Press F5 and select Test and press Run

 

[man]

I pasted in and press F5 and got this error popup

Run-time error '438';
Object doesn't support this property or method

 

[kpark91]

Try

Sub Test()
   Dim last As Integer

    last = Range("A" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False
    
    For c = 1 To last
        If LCase(Left$(Range("A" & c).Value, 3)) = "big" Then
             Rows(c).EntireRow.Interior.Color = RGB(255, 255, 0)
        ElseIf LCase(Left$(Range("A" & c).Value, 5)) = "black" Then
            Rows(c).EntireRow.Interior.Color = RGB(255, 255, 0)
        ElseIf LCase(Right$(Range("A" & c).Value, 4)) = ".net" Then
            Rows(c).EntireRow.Interior.Color = RGB(255, 255, 0)
        ElseIf InStr(LCase(Range("A" & c).Value), "tall") > 0 Then
            Rows(c).EntireRow.Interior.Color = RGB(255, 255, 0)
        End If
    Next c
    

    For d = 1 To last
        If Rows(d).EntireRow.Interior.Color = RGB(255, 255, 0) Then
            Rows(d).EntireRow.Delete
            d = d - 1
        End If
    Next d

End Sub

 

[man]

It works. Thanks a lot!

Just to confirm, in the microsoft visual basic, there is 2 drop down box on top, left box is (General) right box is Test

I am doing this for many sheets, is other way to store the code and apply it when I want to use it, sometimes I will open the Visual Basic to edit some of the criterias when needed. Or are the steps right clicking the sheet and view code then pasting in the code is the most convenient way?

One more question, after running the code once, the excel file prompt me to enable marco to work and everytime I open the file there is a box to ask me enable marco.

 

[man]

Hi, I got another question. Instead of deleting the entire row, is it possible to just fill the row with grey color?