VBA代码抛出编译错误:预期数组

Question

我有一个CSV文件，其中包含一个工作表中的许多表。我在一个过程中编写了部分代码，以检查它是否在为每个表执行我期望的操作。最后的结果，我粘贴在一个过程，现在我得到一个编译错误，在代码的开头，在"Sub IP_VBA()"，我不知道如何解决它，有人能帮我吗？如果我一分为二地运行它，它会做我想做的事情，但当我把所有的代码放在一起时，它就不会了。

Sub IP_VBA()
    ' Format IP table
    
    Dim LR1 As Long, LR2 As Long, LR3 As Long, LR4 As Long, LR5 As Long
    Dim FR1 As Long, FR2 As Long, FR3 As Long, FR4 As Long
    Dim Rows As Long, i As Long
    Dim Data As Range
    
    'Delete tables 1, 2 and 3
    Rows("1:3").EntireRow.Delete
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown).Offset(1, 0)).Delete Shift:=xlUp
    Rows("1:2").EntireRow.Delete
    Columns("A:I").EntireColumn.Delete
    
    'Get necessary data from table 4
    LR1 = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
    Range("B1:B" & LR1 & "," & "D1:E" & LR1 & "," _
        & "G1:O" & LR1).Delete Shift:=xlToLeft
    
    'Delete table 5
    Rows(LR1 + 2).Select
    Range(Selection, Selection.End(xlDown).Offset(1, 0)).Delete Shift:=xlUp
    
    'Get necessary data from table 6
    FR1 = Range("A" & LR1).Offset(2, 0).Row
    Rows(FR1).EntireRow.Delete
    LR2 = ActiveSheet.Range("A" & FR1).CurrentRegion.Rows.Count + LR1 + 1
    Range("A" & FR1 & ":B" & LR2 & "," & "E" & FR1 & ":J" & LR2 & "," & _
        "L" & FR1 & ":T" & LR2).Delete Shift:=xlToLeft
    
    'Get necessary data from tables 7 and 8
    FR2 = Range("A" & LR2).Offset(2, 0).Row
    Rows(FR2).EntireRow.Delete
    LR3 = ActiveSheet.Range("A" & FR2).CurrentRegion.Rows.Count + LR2 + 1
    FR3 = Range("A" & LR3).Offset(2, 0).Row
    Rows(FR3).EntireRow.Delete
    LR3 = ActiveSheet.Range("A" & FR3).CurrentRegion.Rows.Count + LR3 + 1
    Range("A" & FR2 & ":D" & LR3 & "," & "G" & FR2 & ":L" & LR3 & "," & _
        "N" & FR2 & ":V" & LR3).Delete Shift:=xlToLeft
        
    'Delete table 9
    FR4 = Range("A" & LR3).Offset(2, 0).Row
    LR4 = ActiveSheet.Range("A" & FR4).CurrentRegion.Rows.Count + LR3 + 1
    Range("A" & FR4 & ":W" & LR4).Delete Shift:=xlToLeft
    
    'Delete Empty Rows
    LR5 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    Set Data = ActiveSheet.Range("A1:C" & LR5)
    Rows = Data.Rows.Count
    For i = Rows To 1 Step (-1)
        If WorksheetFunction.CountA(Data.Rows(i)) = 0 Then Data.Rows(i).Delete
    Next
    
    'Rename Columns
    Range("A1").Formula = "Part Number"
    Range("B1").Formula = "IP Qty"
    Range("C1").Formula = "IP Value"
    
        
End Sub

Answer 1

你有

Dim Rows As Long

但是后来

Rows("1:3").EntireRow.Delete

我不会使用Rows作为变量名，也不会对任何和所有对行/范围/单元格/列的调用使用特定的工作表限定符

Dim ws As Worksheet

Set ws = ActiveSheet

ws.Rows("1:3").EntireRow.Delete
'etc