excel - 如果单元格包含某些单词，则删除行（语法错误）

Question

我有数据，其中有每个国家的出口价值列表。我将与人口超过 350 万的国家合作。所以，我需要摆脱那些不是。这是我得到的代码：

Sub Loop_Example()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    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 A column in this example
            With .Cells(Lrow, "J")

                If Not IsError(.Value) Then

                    Select Case .Value


         Case Is = "Bosnia and Herzegovina", "Mongolia", "Armenia", "Jamaica", "Qatar", "Albania", "Puerto Rico", "Lithuania", "Namibia", "Gambia", "Botswana", "Gabon", "Lesotho", "TFYR of Macedonia", "Slovenia", "Guinea-Bissau", "Latvia", "Bahrain", "Equatorial", "Guinea", "Trinidad and Tobago", "Estonia", "Timor-Leste", "Mauritius", "Cyprus", "Eswatini", "Djibouti", "Fiji", "Réunion Comoros", Guyana Bhutan Solomon Islands Macao Montenegro Luxembourg Western Sahara Suriname Cabo Verde Maldives Malta Brunei "Guadeloupe", "Belize", "Bahamas", "Martinique", "Iceland", "Vanuatu", "French Guiana", "Barbados", "New Caledonia", "French Polynesia", "Mayotte", "Sao Tome & Principe Samoa", "Saint Lucia Channel Islands", "Guam", "Curaçao", "Kiribati", "FS Micronesia", "Grenada", "Saint Vincent and the Grenadines", "Aruba", "Tonga", "U.S. Virgin Islands","Seychelles", "Antigua and Barbuda", "Isle of Man", "Andorra", "Dominica", "Cayman Islands", "Bermuda", "Marshall Islands", "Northern Mariana Islands", "Greenland",
     "American Samoa", "Saint Kitts and Nevis", "Faeroe Islands", "Sint Maarten", "Monaco", "Turks and Caicos", "Saint Martin", "Liechtenstein", "San Marino", "Gibraltar", "British Virgin Islands", "Caribbean Netherlands", "Palau", "Cook Islands", "Anguilla", "Tuvalu", "Wallis & Futuna", "Nauru", "Saint Barthelemy", "Saint Helena", "Saint Pierre & Miquelon", "Montserrat", "Falkland Islands", "Niue", "Tokelau", "Holy See", "Rep. of Moldova", "Macedonia", "Neth. Antilles", "EU", "Asia n.i.e.": .EntireRow.Delete
            End Select
                    'This will delete each row with the countries written above
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

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

End Sub

我可以寻找上面列出的国家吗（我知道很多）？我应该如何编辑该部分以避免出现“语法错误”。抱歉，我是 VBA 新手。

标签： excelvbasyntax-errorcell

Answer 1

使用换行符

从圭亚那到文莱，你有点累了。

相关片段（快速修复）

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

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

        If Not IsError(.Value) Then

            Select Case .Value
                Case "Bosnia and Herzegovina", "Mongolia", "Armenia", "Jamaica", "Qatar", "Albania", "Puerto Rico", "Lithuania", _
                  "Namibia", "Gambia", "Botswana", "Gabon", "Lesotho", "TFYR of Macedonia", "Slovenia", "Guinea-Bissau", "Latvia", _
                  "Bahrain", "Equatorial", "Guinea", "Trinidad and Tobago", "Estonia", "Timor-Leste", "Mauritius", "Cyprus", _
                  "Eswatini", "Djibouti", "Fiji", "Réunion Comoros", "Guyana", "Bhutan", "Solomon Islands", "Macao", "Montenegro", _
                  "Luxembourg", "Western Sahara", "Suriname", "Cabo Verde", "Maldives", "Malta", "Brunei", "Guadeloupe", "Belize", _
                  "Bahamas", "Martinique", "Iceland", "Vanuatu", "French Guiana", "Barbados", "New Caledonia", "French Polynesia", _
                  "Mayotte", "Sao Tome & Principe Samoa", "Saint Lucia Channel Islands", "Guam", "Curaçao", "Kiribati", _
                  "FS Micronesia", "Grenada", "Saint Vincent and the Grenadines", "Aruba", "Tonga", "U.S. Virgin Islands", _
                  "Seychelles", "Antigua and Barbuda", "Isle of Man", "Andorra", "Dominica", "Cayman Islands", "Bermuda", _
                  "Marshall Islands", "Northern Mariana Islands", "Greenland", "American Samoa", "Saint Kitts and Nevis", _
                  "Faeroe Islands", "Sint Maarten", "Monaco", "Turks and Caicos", "Saint Martin", "Liechtenstein", "San Marino", _
                  "Gibraltar", "British Virgin Islands", "Caribbean Netherlands", "Palau", "Cook Islands", "Anguilla", "Tuvalu", _
                  "Wallis & Futuna", "Nauru", "Saint Barthelemy", "Saint Helena", "Saint Pierre & Miquelon", "Montserrat", _
                  "Falkland Islands", "Niue", "Tokelau", "Holy See", "Rep. of Moldova", "Macedonia", "Neth. Antilles", "EU", _
                  "Asia n.i.e."
                    .EntireRow.Delete
            End Select
            'This will delete each row with the countries written above
            'in Column A, case sensitive.

        End If

    End With

Next Lrow

引入数组（更好的解决方案）

Option Explicit

Sub Loop_Example()

    Dim LessPop As Variant
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    LessPop = Array("Bosnia and Herzegovina", "Mongolia", "Armenia", "Jamaica", "Qatar", "Albania", "Puerto Rico", "Lithuania", _
      "Namibia", "Gambia", "Botswana", "Gabon", "Lesotho", "TFYR of Macedonia", "Slovenia", "Guinea-Bissau", "Latvia", _
      "Bahrain", "Equatorial", "Guinea", "Trinidad and Tobago", "Estonia", "Timor-Leste", "Mauritius", "Cyprus", _
      "Eswatini", "Djibouti", "Fiji", "Réunion Comoros", "Guyana", "Bhutan", "Solomon Islands", "Macao", "Montenegro", _
      "Luxembourg", "Western Sahara", "Suriname", "Cabo Verde", "Maldives", "Malta", "Brunei", "Guadeloupe", "Belize", _
      "Bahamas", "Martinique", "Iceland", "Vanuatu", "French Guiana", "Barbados", "New Caledonia", "French Polynesia", _
      "Mayotte", "Sao Tome & Principe Samoa", "Saint Lucia Channel Islands", "Guam", "Curaçao", "Kiribati", _
      "FS Micronesia", "Grenada", "Saint Vincent and the Grenadines", "Aruba", "Tonga", "U.S. Virgin Islands", _
      "Seychelles", "Antigua and Barbuda", "Isle of Man", "Andorra", "Dominica", "Cayman Islands", "Bermuda", _
      "Marshall Islands", "Northern Mariana Islands", "Greenland", "American Samoa", "Saint Kitts and Nevis", _
      "Faeroe Islands", "Sint Maarten", "Monaco", "Turks and Caicos", "Saint Martin", "Liechtenstein", "San Marino", _
      "Gibraltar", "British Virgin Islands", "Caribbean Netherlands", "Palau", "Cook Islands", "Anguilla", "Tuvalu", _
      "Wallis & Futuna", "Nauru", "Saint Barthelemy", "Saint Helena", "Saint Pierre & Miquelon", "Montserrat", _
      "Falkland Islands", "Niue", "Tokelau", "Holy See", "Rep. of Moldova", "Macedonia", "Neth. Antilles", "EU", "Asia n.i.e.")

    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 A column in this example
            With .Cells(Lrow, "J")

                If Not IsError(.Value) Then

                    If Not IsError(Application.Match(.Value, LessPop, 0)) Then
                        .EntireRow.Delete
                    End If
                    'This will delete each row with the countries written above
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow

    End With

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

End Sub