Trying to delete rows that do not contain specific text

[MPH88]

Hiya. I am having fun learning this vba stuff! But, I'm stuck.

My Book contains businesses and address info, like this:
<style>table { }tr { }col { }br { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { color: white; font-weight: 700; border-color: rgb(142, 169, 219) currentcolor rgb(142, 169, 219) rgb(142, 169, 219); border-style: solid none solid solid; border-width: 0.5pt medium 0.5pt 0.5pt; background: rgb(68, 114, 196) none repeat scroll 0% 0%; }.xl66 { color: white; font-weight: 700; border-color: rgb(142, 169, 219) currentcolor; border-style: solid none; border-width: 0.5pt medium; background: rgb(68, 114, 196) none repeat scroll 0% 0%; }.xl67 { color: white; font-weight: 700; border-color: rgb(142, 169, 219) rgb(142, 169, 219) rgb(142, 169, 219) currentcolor; border-style: solid solid solid none; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(68, 114, 196) none repeat scroll 0% 0%; }.xl68 { border-color: rgb(142, 169, 219) currentcolor rgb(142, 169, 219) rgb(142, 169, 219); border-style: solid none solid solid; border-width: 0.5pt medium 0.5pt 0.5pt; background: rgb(217, 225, 242) none repeat scroll 0% 0%; }.xl69 { border-color: rgb(142, 169, 219) currentcolor; border-style: solid none; border-width: 0.5pt medium; background: rgb(217, 225, 242) none repeat scroll 0% 0%; }.xl70 { border-color: rgb(142, 169, 219) currentcolor; border-style: solid none; border-width: 0.5pt medium; background: rgb(217, 225, 242) none repeat scroll 0% 0%; }.xl71 { border-color: rgb(142, 169, 219) rgb(142, 169, 219) rgb(142, 169, 219) currentcolor; border-style: solid solid solid none; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(217, 225, 242) none repeat scroll 0% 0%; }.xl72 { border-color: rgb(142, 169, 219) currentcolor rgb(142, 169, 219) rgb(142, 169, 219); border-style: solid none solid solid; border-width: 0.5pt medium 0.5pt 0.5pt; }.xl73 { border-color: rgb(142, 169, 219) currentcolor; border-style: solid none; border-width: 0.5pt medium; }.xl74 { border-color: rgb(142, 169, 219) currentcolor; border-style: solid none; border-width: 0.5pt medium; }.xl75 { border-color: rgb(142, 169, 219) rgb(142, 169, 219) rgb(142, 169, 219) currentcolor; border-style: solid solid solid none; border-width: 0.5pt 0.5pt 0.5pt medium; }.xl76 { border-color: rgb(142, 169, 219) rgb(142, 169, 219) rgb(142, 169, 219) currentcolor; border-style: solid solid solid none; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(217, 225, 242) none repeat scroll 0% 0%; }.xl77 { text-align: center; }</style>

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

BureauNumber

Primary Name

Street Address

City

Zip Code

State

County

Governing Class

Description

ExMod

Updated On

ExModYear

InsurerName

CoverageDate

2

3484

M P A SALES, INC.

13700 VAN NESS AVENUE

GARDENA

90249

CA

Los Angeles County

8018

STORES-WHOLESALE

85

2/16/18

17

Employers Assurance Company

##########

3

4095

C MONDAVI & FAMILY

P. O. BOX 191 2800 NORTH MAIN STREET

ST. HELENA

94574

CA

Napa County

2142

WINERIES

133

2/16/18

17

Travelers Property Casualty Company of America

##########

4

7028

(DBA) BAY AREA BAGELS

260 LORTON AVE

BURLINGAME

94010

CA

San Mateo County

8078

SANDWICH SHOPS

77

2/16/18

18

Mid-Century Insurance Company

##########

5

9770

GREEN VALLEY LANDSCAPE AND MAINTENANCE INC

1182 INDUSTRIAL AVE

ESCONDIDO

92029

CA

San Diego County

42

LANDSCAPE GARDENING

125

2/16/18

18

<tbody>
</tbody>

My goal is to find the businesses within certain counties (Column G). I want to delete the data that is irrelevant.

I poked around online and found a walk-through that looked promising. I re-purposed the code (pasted below).

Goal: I want to delete the rows that do not contain multiple specific text values in column G. Here is the code:

Sub RemoveCounties()
'
' RemoveCounties Macro
'

'
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim Counties As Variant
    
    Counties = Array("Los Angeles County", "Orange County", "Riverside County", "San Bernardino County", "San Diego County")

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the G column in this example
            With .Cells(Lrow, "G")

                Select Case .Value
                [B]Case Is <> Counties[/B]: .EntireRow.Delete
                End Select

                'If Not IsError(.Value) Then

                    'If .Value <> Counties Then .EntireRow.Delete
                    'This will delete each row that does not contain one of the counties listed in the variable above
                    'in Column G, case sensitive.

                    'End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With


End Sub

I get a "Type mismatch" error and the debugger points to this line "Case Is <> Counties"

FYI...The walk-through outlined different options including "If Not IsError(.Value) Then" and also "Select Case .Value". I got the same error on both approaches.

Any suggestions?

Questions: Does it matter if my data is set as a table versus an array?

Thanks a bunch.

 

[NdNoviceHlp]

It's coming along. Trial...

 Dim Counties () As Variant
Counties(Lrow - 1)

Your'e using an array with positions 0 to lastrow - 1.. You don't need to manually enter the names in an array. You may want to decide do you really want to delete the whole row of just some data within it... there's a difference. HTH. Dave

 

[MPH88]

Hoping to get a little more help on this one. Again, I want to keep all the records from certain counties ("Los Angeles County", "Orange County", "Riverside County", "San Bernardino County", "San Diego County"). And, I want to delete all the other irrelevant records.

I came across two approaches that are designed to find certain text and delete the rows that do not contain it (the opposite of what I want). So, I tried swapping out the = for <>. I have tried:

Case Is <> Counties: .EntireRow.Delete

I get a "Type mismatch" error here. I realize I am trying to get "Case" to look at my variable "Counties" and find stuff that doesn't fit. Is this possible through this approach? Is there a way to use

Case Is Not =

or

Case Not Is =

?

Or, can I remove my variable and use

Case = "Los Angeles County", "Orange County", "Riverside County", "San Bernardino County", "San Diego County"

The other approach is:

If .Value <> Counties Then .EntireRow.Delete

I think I understand why this doesn't work. From what I read this approach will look for a single value...not multiple. So, instead of my "Counties" variable it wants just one string. Correct?

How would you do it?

 

[Fluff]

Try

        For Lrow = Lastrow To Firstrow [COLOR=#ff0000]+ 1[/COLOR] Step -1

            'We check the values in the G column in this example
            With .Cells(Lrow, "G")
               If Not UBound(Filter(Counties, .Value, True, vbTextCompare)) = 0 Then .EntireRow.Delete
            End With

        Next Lrow

Note the +1 in red, without that you'll delete the header row.

 

[MPH88]

Awesome, Fluff. Worked like a champ.

 

[Fluff]

Glad to help & thanks for the feedback

 

[MPH88]

Anyone who is working on the same problem...here's the final code that did the job:

Sub RemoveCounties()
'
' RemoveCounties Macro
'

'
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim Counties As Variant   ' Change this variable name to suit.

' Make sure the also change the associated array as well as the the column
    
    Counties = Array("Los Angeles County", "Orange County", "Riverside County", "San Bernardino County", "San Diego County")

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        FirstRow = .UsedRange.Cells(1).Row
        LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top). The + 1 makes sure you do not delete your header row.
        For Lrow = LastRow To FirstRow + 1 Step -1

            'We check the values in the G column in this example
            With .Cells(Lrow, "G")
               If Not UBound(filter(Counties, .Value, True, vbTextCompare)) = 0 Then .EntireRow.Delete
            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With


End Sub