VBA Code throwing Compile Error: Expected Array-CodePudding

I have a CSV file that contains lots of tables inside of one worksheet. I was writing parts of the code in one procedure to check if it was doing what I expected for each table. The final result I pasted in one single procedure and now I'm getting a Compile Error right in the beginning of the code, in "Sub IP_VBA()", and I have no idea how to solve it, can someone help me? If I run it part by part, it does what I want, but not when I put all the code together.

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

CodePudding user response：

You have

Dim Rows As Long

but then

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

I would not use Rows as a variable name, and also use a specific worksheet qualifier for any and all calls to Rows/Range/Cells/Columns

Dim ws As Worksheet

Set ws = ActiveSheet

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