Trying to delete rows that do not contain specific text

M

MPH88

New Member
Joined
Sep 28, 2017
Messages
13
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> [TABLE="width: 923"]
<tbody>[TR]
[TD="width: 17"][/TD]
[TD="class: xl77, width: 87"]A[/TD]
[TD="class: xl77, width: 87"]B[/TD]
[TD="class: xl77, width: 87"]C[/TD]
[TD="class: xl77, width: 87"]D[/TD]
[TD="class: xl77, width: 87"]E[/TD]
[TD="class: xl77, width: 87"]F[/TD]
[TD="class: xl77, width: 87"]G[/TD]
[TD="class: xl77, width: 87"]H[/TD]
[TD="class: xl77, width: 87"]I[/TD]
[TD="class: xl77, width: 87"]J[/TD]
[TD="class: xl77, width: 87"]K[/TD]
[TD="class: xl77, width: 87"]L[/TD]
[TD="class: xl77, width: 87"]M[/TD]
[TD="class: xl77, width: 87"]N[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65"]BureauNumber[/TD]
[TD="class: xl66"]Primary Name[/TD]
[TD="class: xl66"]Street Address[/TD]
[TD="class: xl66"]City[/TD]
[TD="class: xl66"]Zip Code[/TD]
[TD="class: xl66"]State[/TD]
[TD="class: xl66"]County[/TD]
[TD="class: xl66"]Governing Class[/TD]
[TD="class: xl66"]Description[/TD]
[TD="class: xl66"]ExMod[/TD]
[TD="class: xl66"]Updated On[/TD]
[TD="class: xl66"]ExModYear[/TD]
[TD="class: xl66"]InsurerName[/TD]
[TD="class: xl67"]CoverageDate[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl68, align: right"]3484[/TD]
[TD="class: xl69"]M P A SALES, INC.[/TD]
[TD="class: xl69"]13700 VAN NESS AVENUE[/TD]
[TD="class: xl69"]GARDENA[/TD]
[TD="class: xl69, align: right"]90249[/TD]
[TD="class: xl69"]CA[/TD]
[TD="class: xl69"]Los Angeles County[/TD]
[TD="class: xl69, align: right"]8018[/TD]
[TD="class: xl69"]STORES-WHOLESALE[/TD]
[TD="class: xl69, align: right"]85[/TD]
[TD="class: xl70, align: right"]2/16/18[/TD]
[TD="class: xl69, align: right"]17[/TD]
[TD="class: xl69"]Employers Assurance Company[/TD]
[TD="class: xl71, align: center"]##########[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl68, align: right"]4095[/TD]
[TD="class: xl69"]C MONDAVI & FAMILY[/TD]
[TD="class: xl69"]P. O. BOX 191 2800 NORTH MAIN STREET[/TD]
[TD="class: xl69"]ST. HELENA[/TD]
[TD="class: xl69, align: right"]94574[/TD]
[TD="class: xl69"]CA[/TD]
[TD="class: xl69"]Napa County[/TD]
[TD="class: xl69, align: right"]2142[/TD]
[TD="class: xl69"]WINERIES[/TD]
[TD="class: xl69, align: right"]133[/TD]
[TD="class: xl70, align: right"]2/16/18[/TD]
[TD="class: xl69, align: right"]17[/TD]
[TD="class: xl69"]Travelers Property Casualty Company of America[/TD]
[TD="class: xl71, align: center"]##########[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl72, align: right"]7028[/TD]
[TD="class: xl73"](DBA) BAY AREA BAGELS[/TD]
[TD="class: xl73"]260 LORTON AVE[/TD]
[TD="class: xl73"]BURLINGAME[/TD]
[TD="class: xl73, align: right"]94010[/TD]
[TD="class: xl73"]CA[/TD]
[TD="class: xl73"]San Mateo County[/TD]
[TD="class: xl73, align: right"]8078[/TD]
[TD="class: xl73"]SANDWICH SHOPS[/TD]
[TD="class: xl73, align: right"]77[/TD]
[TD="class: xl74, align: right"]2/16/18[/TD]
[TD="class: xl73, align: right"]18[/TD]
[TD="class: xl73"]Mid-Century Insurance Company[/TD]
[TD="class: xl75, align: center"]##########[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="class: xl68, align: right"]9770[/TD]
[TD="class: xl69"]GREEN VALLEY LANDSCAPE AND MAINTENANCE INC[/TD]
[TD="class: xl69"]1182 INDUSTRIAL AVE[/TD]
[TD="class: xl69"]ESCONDIDO[/TD]
[TD="class: xl69, align: right"]92029[/TD]
[TD="class: xl69"]CA[/TD]
[TD="class: xl69"]San Diego County[/TD]
[TD="class: xl69, align: right"]42[/TD]
[TD="class: xl69"]LANDSCAPE GARDENING[/TD]
[TD="class: xl69, align: right"]125[/TD]
[TD="class: xl70, align: right"]2/16/18[/TD]
[TD="class: xl69, align: right"]18[/TD]
[TD="class: xl69"][/TD]
[TD="class: xl76"][/TD]
[/TR]
</tbody>[/TABLE]

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:

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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
It's coming along. Trial...
Code: 
 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
 
Upvote 0
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
Click to expand...

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
Code: 
Case Is Not =
or
Code: 
Case Not Is =
?

Or, can I remove my variable and use
Code: 
Case = "Los Angeles County", "Orange County", "Riverside County", "San Bernardino County", "San Diego County"

The other approach is:

If .Value <> Counties Then .EntireRow.Delete
Click to expand...

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?
 
Upvote 0
Try
Code: 
        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.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Anyone who is working on the same problem...here's the final code that did the job:

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   ' 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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
W
  • Question Question
Return Related URLs into one string (Textjoin)
Replies
1
Views
975
WilldoSEO
W
S
  • Question Question
Sending email from VBA (Excel) with Outlook
Replies
4
Views
2K
SaskiaHe
S
leopardhawk
  • Question Question
Need help with #REF! error
Replies
2
Views
807
leopardhawk
leopardhawk
E
  • Question Question
How to copy values from one workbook sheet to another, based on criterias ?
Replies
7
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top