VBA 计数错误 1004

Question

我不断收到错误 1004。我不知道如何声明我的对象以避免此错误:

Sub DeleteBlank()

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    ws.Activate

    'Delete Blank Columns
    For col = 1 To 4
        If WorksheetFunction.CountA(ws.Columns(i)) = 0 Then
            ws.Columns.Delete
        End If
    Next col


End Sub

Answer 1

退后一步，使用 col 而不是 i。另外，可以用 With 包裹起来。

Option Explicit
Sub DeleteBlank()

    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim col As Long
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")

    With ws
        For col = 4 To 1 Step -1
            If WorksheetFunction.CountA(.Columns(col)) = 0 Then
                .Columns(col).Delete
            End If
        Next col
    End With
End Sub