VBA宏代码编译报错：.range(ContRow&":"&ContRow).entirerow.Delete语法问题求助

What's Causing the Error? #

Your Syntax error and compile error expected: list separator or ) stem from a syntax mistake when building the Range reference. In VBA, the string concatenation operator & requires proper spacing around it, plus your original approach to constructing the row range string is unnecessary (and error-prone) when you already have a direct row number to work with.

Fixed Macro Code #

Sub Cont_Delete()
    With Sheet1
        If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Record") = vbNo Then Exit Sub
        If .Range("B3").Value = Empty Then Exit Sub
        
        ' Explicitly declare variable to avoid variant type issues
        Dim ContRow As Long
        ContRow = .Range("B3").Value
        
        ' Simplified row deletion (no string concatenation needed)
        .Rows(ContRow).EntireRow.Delete
        
        .Range("D18").Select
    End With
End Sub

Key Fixes Explained #

• Explicit Variable Declaration: Added Dim ContRow As Long to define the variable type. VBA defaults to variant types, which can lead to unexpected behavior; explicit declarations make your code clearer and more reliable.
• Simplified Row Targeting: Replaced .Range(ContRow&":"&ContRow).EntireRow.Delete with .Rows(ContRow).EntireRow.Delete. Since you already have the row number stored in ContRow, using the Rows object directly is cleaner and eliminates the need for error-prone string concatenation.
• Syntax Correction (If You Prefer String Concatenation): If you wanted to keep the original approach, you must add spaces around the & operator for VBA to parse the concatenation correctly:

  .Range(ContRow & ":" & ContRow).EntireRow.Delete
  

内容的提问来源于stack exchange，提问作者Franz Daryl Tan