Deleting row if keyword not found

[kaffal]

I would like to have a macro to loop through all the worksheet except for "Summary" worksheet.

The macro will look for the keyword in the worksheet starting from row 2 from every sheet.

if the keyword is found on the row, the entire row will be deleted and shift row up.
Keyword for example is found in H13 in the Summary worksheet

Excel Workbook
	G	H
13	Keyword	London
Summary

 

[Colin Legg]

Hi John,

That's a good question. Let's take the first example in post #6 on the link I gave you earlier because that one sounds like the right one for you and work through the changes required.

So instead of using vList we need to use a range; rename the variable as rngList and declare it as an Excel range.

Dim vList

Change to:

Dim rngList As Range

We no longer need to load the array so we can simply delete this line:

vList = Array("Here", "There", "Everywhere")

But instead we need to set a reference to the range of cells with the keywords in. This could be on a different sheet so, for our example, let's set a reference to Sheet2!A1:A3. Add in this line:

Set rngList = Sheet2.Range("A1:A3")

Next we need to adjust the loop so it iterates through the cells in rngList instead of the old array.
Change:

For lCounter = LBound(vList) To UBound(vList)

to:

For lCounter = 1 To rngList.Cells.Count

Finally, we need to adjust the Range.Find() method to use the values from the cells in rngList rather than the elements from the old array.
Change:

            Set rngFound = .Find( _
                                What:=vList(lCounter), _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True)

To:

            Set rngFound = .Find( _
                                What:=rngList.Cells(lCounter).Value, _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True)

Putting all those changes in place, the example looks like this:

Sub Example1()
    Dim rngFound As Range, rngToDelete As Range, rngList As Range
    Dim sFirstAddress As String
    Dim lCounter As Long
    Application.ScreenUpdating = False
 
    Set rngList = Sheet2.Range("A1:A3")
 
    For lCounter = 1 To rngList.Cells.Count
        With Sheet1.Range("A:A")
            Set rngFound = .Find( _
                                What:=rngList.Cells(lCounter).Value, _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True)
 
            If Not rngFound Is Nothing Then
                If rngToDelete Is Nothing Then
                    Set rngToDelete = rngFound
                Else
                    Set rngToDelete = Union(rngToDelete, rngFound)
                End If
 
                sFirstAddress = rngFound.Address
                Set rngFound = .FindNext(After:=rngFound)
 
                Do Until rngFound.Address = sFirstAddress
                    Set rngToDelete = Union(rngToDelete, rngFound)
                    Set rngFound = .FindNext(After:=rngFound)
                Loop
            End If
        End With
    Next lCounter
 
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

Hope that helps...

 

[ethical]

Hi Colin,

Your help has been tremendous, that works like a charm and no way i would have figured it out. If I may ask one more question though, is there a way to make the search to not be case sensitive? So basically automatically changing the case of the cells I am comparing against (the Sheet2 keywords list) first before running this sub?

thanks so much.

John

 

[Colin Legg]

Hi John,

Sure, but no need to change the case of the text in the cells.

Have a look at the Range.Find() method in your VBA helpfile. It says something like this:

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

expression Required. An expression that returns a Range object.
......
......
MatchCase Optional Variant. True to make the search case sensitive. The default value is False.

The example we have currently (deliberately) performs a case sensitive find:

            Set rngFound = .Find( _
                                What:=rngList.Cells(lCounter).Value, _
                                Lookat:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True)

So you just need to change MatchCase to False.

Hope that helps...

 

[ethical]

ok thank you! i should have seen that, I have got a head cold so not exactly thinking clearly today!

cheers

John