如何在VBA数组中添加200+值？附城市筛选代码问题

VBA的Variant数组本身不存在只能容纳35个值的限制，你碰到的问题应该是手动在代码里堆砌长列表时容易出错，或者误判了数组的容量。下面提供两种解决方案，同时优化原代码的执行效率：

一、直接扩展数组支持更多城市 #

你完全可以在Array()里继续添加城市元素，把每个城市单独放在一行、用逗号分隔，这样可读性强且不容易写错，支持的元素数量远不止35个：

citiesToFilter = Array(
    "Jacksonville, FL",
    "Houston-The Woodlands-Sugar Land, TX",
    "Seattle-Tacoma-Bellevue, WA",
    "Minneapolis-St. Paul-Bloomington, MN-WI",
    ' 此处可继续添加任意数量的城市
    "Tucson, AZ",
    "Chicago, IL",
    "New York, NY"
)

二、用工作表存储城市列表（推荐） #

如果城市列表很长或需要频繁修改，直接在代码里维护太麻烦。可以新建一个名为「城市列表」的工作表，把城市名依次放在A列（从A1开始），然后修改代码从工作表读取列表，不用改代码就能更新城市清单：

Sub CopyDataByCity()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim cityListSheet As Worksheet
    Dim lastRowSource As Long
    Dim lastRowCity As Long
    Dim i As Long, j As Long
    Dim citiesToFilter() As Variant
    Dim currentRow As Long
    
    ' 指定工作表
    Set sourceSheet = ThisWorkbook.Sheets("Portfolio Summary")
    Set targetSheet = ThisWorkbook.Sheets("RAW DATA")
    Set cityListSheet = ThisWorkbook.Sheets("城市列表")
    
    ' 从工作表读取所有城市
    lastRowCity = cityListSheet.Cells(Rows.Count, "A").End(xlUp).Row
    citiesToFilter = cityListSheet.Range("A1:A" & lastRowCity).Value
    
    ' 获取源数据最后一行
    lastRowSource = sourceSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 目标表起始行（假设表头在第1行）
    currentRow = 2
    
    ' 遍历每个城市
    For j = 1 To UBound(citiesToFilter)
        Dim city As String
        city = citiesToFilter(j, 1)
        
        i = 8
        Do While i <= lastRowSource
            ' 匹配当前城市
            If sourceSheet.Cells(i, "Q").Value = city Then
                ' 复制对应列数据到目标表
                targetSheet.Cells(currentRow, "A").Value = sourceSheet.Cells(i, "C").Value
                targetSheet.Cells(currentRow, "B").Value = sourceSheet.Cells(i, "H").Value
                targetSheet.Cells(currentRow, "C").Value = sourceSheet.Cells(i, "D").Value
                targetSheet.Cells(currentRow, "D").Value = sourceSheet.Cells(i, "S").Value
                targetSheet.Cells(currentRow, "E").Value = sourceSheet.Cells(i, "I").Value
                targetSheet.Cells(currentRow, "F").Value = sourceSheet.Cells(i, "R").Value
                targetSheet.Cells(currentRow, "G").Value = sourceSheet.Cells(i, "W").Value
                targetSheet.Cells(currentRow, "H").Value = sourceSheet.Cells(i, "N").Value
                targetSheet.Cells(currentRow, "I").Value = city
                
                currentRow = currentRow + 1
            End If
            
            ' 跳过空行或不同城市的行，避免重复遍历
            If i < lastRowSource Then
                If sourceSheet.Cells(i + 1, "A").Value = "" Or sourceSheet.Cells(i + 1, "Q").Value <> city Then
                    i = i + 1
                End If
            End If
            
            i = i + 1
        Loop
    Next j
End Sub

三、原代码的优化说明 #

• 将嵌套For循环改为Do While，避免手动修改循环变量i导致的逻辑异常
• 移除了未使用的headerRow变量，精简代码
• 通过工作表存储城市列表，彻底解决长列表维护问题

内容的提问来源于stack exchange，提问作者dhanesh sharma