Delete rows when specific words and criteria are found

man

New Member
Joined
Jul 26, 2010
Messages
37
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 -->
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
To delete an Entire Row, Use:

Code:
Rows(rowNumber).EntireRow.Delete
To concatenate the rightmost letters, Use Right function
Code:
Right$(string, numberofletters)
To find a certain combination of letters, use InStr function
Code:
InStr(string1, string2)
 

man

New Member
Joined
Jul 26, 2010
Messages
37
kpark91, I am new to this. How do I use the code you provided?
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Copy and Paste this code into VBE

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

Code:
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

New Member
Joined
Jul 26, 2010
Messages
37
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
 

man

New Member
Joined
Jul 26, 2010
Messages
37
I pasted in and press F5 and got this error popup

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

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Try

Code:
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

New Member
Joined
Jul 26, 2010
Messages
37
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

New Member
Joined
Jul 26, 2010
Messages
37
Hi, I got another question. Instead of deleting the entire row, is it possible to just fill the row with grey color?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,934
Messages
5,508,220
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top