Excel VBA如何正确清空动态数组？Erase语句不生效怎么解决

问题核心原因 #

你使用Erase V, P, N清空数组的语法本身是正确的，数组未清空、数值累加的本质问题有2个：

• VBA不支持块级作用域，你写在For循环内部的Dim Index As Long这类变量声明，只会在整个Sub启动时初始化一次，不会每次循环重置数值。你三个数组对应的下标计数器Index、Index_2、Index_3每轮循环结束后没有重置为0，下一轮循环会继续沿用上一轮的最终值，导致ReDim Preserve时数组长度持续叠加，看起来就像旧值没有被清空。
• 你当前写的If Index = 0 Then Exit Sub逻辑有误，某行没有符合条件的数据时会直接终止整个过程，后续行都不会处理。

修复步骤 #

1. 在每轮外层循环的开头，将三个下标计数器重置为0
2. 将异常退出逻辑改为跳过当前循环轮次，不影响后续行处理
3. 修正原代码中rCount变量赋值换行的语法错误

修复后的完整代码 #

Sub WriteToZeroBasedArray()
    Dim E As Integer
    ' 修正原代码rCount赋值换行的语法错误
    Dim rCount As Long: rCount = Worksheets("0618").Cells.SpecialCells(xlLastCell).Row
    Dim V() As Variant, P() As Variant, N() As Variant
    Dim Index As Long, Index_2 As Long, Index_3 As Long
    Dim cCount As Long, c As Long
    Dim K As Integer, L As Integer, FP As Integer, Sum As Integer
    
    For E = 9 To rCount
        ' 每轮循环开始重置下标计数器，这是最关键的修复点
        Index = 0
        Index_2 = 0
        Index_3 = 0
        Erase V, P, N ' 此处Erase可以保留，也可以删除，不影响逻辑
        
        cCount = Worksheets("0618").Cells.SpecialCells(xlLastCell).Column
        
        ' 배열에 값 저장하기
        For c = 7 To cCount
            If 0 < Worksheets("0618").Cells(E, c).Value And Worksheets("0618").Cells(E, c).Value < 100 Then
                ReDim Preserve V(0 To Index)
                V(Index) = Worksheets("0618").Cells(2, c).Value
                Index = Index + 1
                
                ReDim Preserve P(0 To Index_2)
                P(Index_2) = Worksheets("0618").Cells(4, c).Value
                Index_2 = Index_2 + 1
                
                ReDim Preserve N(0 To Index_3)
                N(Index_3) = Worksheets("0618").Cells(E, c).Value
                Index_3 = Index_3 + 1
            End If
        Next c
        
        ' 无符合条件的数据时跳过当前轮次，不终止整个过程
        If Index = 0 Then GoTo NextLoop
        
        K = UBound(V)
        ' 배열의 값 차례로 Sheet2에 넣기
        For L = 0 To K
            If L < 5 Then
                Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9), 1).Value = V(L)
                Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9), 2).Value = P(L)
                Worksheets("sheet2").Cells(L + 6 + 14 * (E - 9), 3).Value = N(L)
            Else
                Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9), 5).Value = V(L)
                Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9), 6).Value = P(L)
                Worksheets("sheet2").Cells(L + 1 + 14 * (E - 9), 7).Value = N(L)
            End If
        Next
        
        Sum = 0
        ' 수량*단가 구하기
        For FP = (6 + 14 * (E - 9)) To (10 + 14 * (E - 9))
            Worksheets("sheet2").Cells(FP, 4).Value = Worksheets("sheet2").Cells(FP, 2).Value * Worksheets("sheet2").Cells(FP, 3).Value
            Worksheets("sheet2").Cells(FP, 8).Value = Worksheets("sheet2").Cells(FP, 6).Value * Worksheets("sheet2").Cells(FP, 7).Value
            
            ' 총액 구하기
            Sum = Sum + Worksheets("sheet2").Cells(FP, 4).Value + Worksheets("sheet2").Cells(FP, 8).Value
            
            ' 0 지우기
            If Worksheets("sheet2").Cells(FP, 4).Value = 0 Then
                Worksheets("sheet2").Cells(FP, 4).ClearContents
            End If
            If Worksheets("sheet2").Cells(FP, 8).Value = 0 Then
                Worksheets("sheet2").Cells(FP, 8).ClearContents
            End If
        Next
        
        ' 총액 구하기2
        Worksheets("sheet2").Cells(12 + 14 * (E - 9), 8).Value = Sum
        
        ' 받으실 분
        Worksheets("sheet2").Cells(3 + 14 * (E - 9), 2).Value = Worksheets("0618").Cells(E, 3).Value
        
        ' 주소
        Worksheets("sheet2").Cells(3 + 14 * (E - 9), 5).Value = Worksheets("0618").Cells(E, 2).Value
        
        ' 서식 복사
        Worksheets("sheet3").Range("A1:H13").Copy
        Worksheets("sheet2").Range("A" & 15 + 14 * (E - 9)).PasteSpecial
NextLoop:
    Next
End Sub

内容的提问来源于stack exchange，提问作者김다영