vba - 管理一长串值(value)观

Question

感谢 Stackoverflow 的工作人员，我已经让下面的脚本运行良好。它基本上删除了除了 A 列中的值与脚本中的值列表匹配的行之外的所有行。

但是，我想在大约 400 条记录上运行该脚本。当我将下面示例中的值 10049, 10046 替换为遵循相同结构的 400 个值(所有数字，每个 5 个字符长)时，我收到一条错误消息，指出:

Compile error. Syntax error

值的数量有限制吗？如果是这样，有什么办法可以解决吗？

我非常需要您的建议!谢谢!

//彼得

这很好用:

Sub DeleteArticles()
Dim i As Long
Dim ws As Worksheet
Dim lastRow As Long

For Each ws In Worksheets
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 8 Step -1
    'if ID isn't present in array of IDs to keep, then we delete entire row
    If UBound(Filter(Array(10049, 10046), ws.Cells(i, 1).Value)) = -1 Then
        ws.Rows(i).EntireRow.Delete
    End If
    Next
Next
End Sub

这是行不通的

Sub DeleteArticles()
Dim i As Long
Dim ws As Worksheet
Dim lastRow As Long

For Each ws In Worksheets
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 8 Step -1
    'if ID isn't present in array of IDs to keep, then we delete entire row
    If UBound(Filter(Array(10049 ,10048 ,10047 ,10046 ,10045 ,10044 ,10043 ,10042 ,10041 ,10050 ,10053 ,10052 ,10051 ,10054 ,10012 ,10010 ,10008 ,10007 ,10006 ,10004 ,10013 ,10022 ,10021 ,10020 ,10019 ,10017 ,10016 ,10015 ,10023 ,10032 ,10030 ,10029 ,10027 ,10024 ,10039 ,10038 ,10037 ,10036 ,10035 ,10034 ,10040 ,10063 ,10062 ,10061 ,10060 ,10059 ,10058 ,10057 ,10056 ,10055 ,10064 ,10066 ,10065 ,10067 ,10195 ,10218 ,10217 ,10216 ,10215 ,10214 ,10213 ,10212 ,10211 ,10219 ,10873 ,10870 ,10868 ,11029 ,11301 ,11302 ,11303 ,11304 ,11305 ,11306 ,11307 ,11308 ,11309 ,11333 ,11331 ,11330 ,11328 ,11327 ,11334 ,11342 ,11341 ,11339 ,11337 ,11353 ,11349 ,11348 ,11363 ,11362 ,11360 ,11359 ,11358 ,11357 ,11374 ,11372 ,11370 ,11369 ,11368 ,11367 ,11387 ,11385 ,11384 ,11383 ,11382 ,11381 ,11380 ,11390 ,10000 ,10002 ,10097 ,10207 ,10206 ,10205 ,10204 ,10203 ,10202 ,10201 ,10208 ,10209 ,10210 ,10230 ,10229 ,10228 ,10227 ,10226 ,10225 ,11040 ,11071 ,11112 ,11110 ,11108 ,11117 ,11215 ,11214 ,11209 ,11226 ,11225 ,11224 ,11223 ,11221 ,11227 ,11236 ,11234 ,11233 ,11232 ,11231 ,11229 ,11228 ,11237 ,11246 ,11245 ,11244 ,11242 ,11240 ,11239 ,11238 ,11247 ,11278 ,11167 ,11169 ,11168 ,11179 ,11191 ,11206 ,11203 ,11201 ,11207 ,11256 ,11255 ,11253 ,11252 ,11251 ,11249 ,11248 ,11257 ,11266 ,11265 ,11261 ,11260 ,11259 ,11267 ,11275 ,11272 ,11270 ,11268 ,11276 ,11083 ,11084 ,11085 ,11088 ,10251 ,10301 ,10302 ,10312 ,10311 ,10310 ,10309 ,10308 ,10307 ,10306 ,10305 ,10304 ,10313 ,10317 ,10316 ,10315 ,10314 ,10323 ,10326 ,10343 ,10348 ,10350 ,10415 ,10414 ,10413 ,10416 ,10423 ,10424 ,10426 ,10428 ,10446 ,10445 ,10444 ,10443 ,10442 ,10441 ,10440 ,10439 ,10438 ,10447 ,10466 ,10465 ,10464 ,10463 ,10462 ,10461 ,10460 ,10459 ,10458 ,10467 ,10471 ,10470 ,10469 ,10468 ,10472 ,10448 ,10449 ,10450 ,10451 ,10452 ,10453 ,10454 ,10455 ,10456 ,10494 ,10495 ,10500 ,10507 ,10508 ,10509 ,10510 ,10511 ,10512 ,10513 ,10514 ,10517 ,10518 ,10628 ,10643 ,10732 ,10731 ,10730 ,10729 ,10728 ,10727 ,10726 ,10725 ,10724 ,10733 ,10742 ,10741 ,10740 ,10739 ,10738 ,10737 ,10736 ,10735 ,10734 ,10743 ,10799 ,10809 ,10808 ,10807 ,10805 ,10804 ,10803 ,10802 ,10801 ,10810 ,10819 ,10818 ,10817 ,10816 ,10815 ,10814 ,10813 ,10812 ,10811 ,10820 ,10829 ,10828 ,10827 ,10826 ,10825 ,10824 ,10823 ,10822 ,10821 ,10830 ,10839 ,10838 ,10837 ,10836 ,10835 ,10834 ,10833 ,10832 ,10831 ,10840 ,10844 ,10843 ,10842 ,10841 ,10845 ,10293 ,10294 ,10303 ,10320 ,10331 ,10344 ,10327 ,10368 ,10367 ,10366 ,10365 ,10364 ,10363 ,10387 ,10381 ,10379 ,10377 ,10373 ,10412 ,10430 ,10432 ,10473 ,10474 ,10477 ,10629 ,10621 ,10620 ,10652 ,10654 ,10744 ,10749 ,10750 ,10751 ,10752 ,10756 ,10291 ,10962 ,10963 ,10964 ,10974 ,10975 ,10971), ws.Cells(i, 1).Value)) = -1 Then
        ws.Rows(i).EntireRow.Delete
    End If
    Next
Next
End Sub

Answer 1

这是因为该行超出了单行代码的长度限制(1024 个字符)。在逗号后面使用下划线将其分开，这样您就可以在下一行继续很长的表达式。您可能需要将其分成两行以上。

例如:

If UBound(Filter(Array(10049 ,10048 ,10047 ,10046 ,10045 ,10044 ,10043 , _
10049 ,10048 ,10047 ,10046 ,10045 ,10044 ,10043 , _
10049 ,10048 ,10047 ,10046 ,10045 ,10044 ,10043 , _
10971), ws.Cells(i, 1).Value)) = -1 Then

行延续(下划线)可以使“逻辑代码行”跨越多个“物理代码行”。

请注意，在单个逻辑代码行上可以使用的行延续数量也有限制。

此技术还可用于排列类似的表达式以检查错误，并使您无需水平滚动即可立即查看所有代码。