Excel宏开发求助：实现删除求和为0的最后一个带表头列

Let's break down the issues in your code and fix it to properly identify and delete columns where the sum is zero (only for columns with headers):

Key Issues in Your Original Code #

• Duplicate Dim keyword: You can't use Dim twice in a single variable declaration line. It should be Dim LastCol As Long, i As Long.
• Uninitialized LastCol: You tried to use LastCol in the loop before assigning it a value. We need to get the last column with a header first, before starting the loop.
• Undefined LR variable: The Resize(LR, 1) call uses LR but you never declared or set it to the last row of data.
• Incorrect With block scope: Your Next i is placed outside the End With statement, which will throw a syntax error.
• Unqualified worksheet references: Using Cells or Columns without a leading . will reference the active worksheet instead of explicitly targeting "Sheet1".

Corrected VBA Code #

Sub DeleteZeroSumColumns()
    Dim LastCol As Long, i As Long
    Dim LastRow As Long ' Renamed LR to LastRow for clarity
    
    With ThisWorkbook.Sheets("Sheet1")
        ' Get the last column with a header in row 1
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ' Get the last row with data in column A (adjust if your data starts elsewhere)
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        ' Loop from last column backwards to avoid skipping columns when deleting
        For i = LastCol To 1 Step -1
            ' Calculate sum of the column (from header row to last data row)
            If Application.Sum(.Range(.Cells(1, i), .Cells(LastRow, i))) = 0 Then
                ' Delete the entire column
                .Columns(i).EntireColumn.Delete
            End If
        Next i
    End With
End Sub

Additional Notes #

• We loop backwards (from last column to first) because deleting a column shifts remaining columns left. If we looped forward, we'd skip the next column after deleting one.
• LastRow is calculated using column A as a reference—if your data starts in a different column, adjust "A" to match your starting column.
• Using Application.Sum handles both numeric and non-numeric values (it ignores non-numeric cells), which is safer for most Excel datasets.

内容的提问来源于stack exchange，提问作者Souza Saulo