我有这段代码(见下文(,它查找当删除所有需要删除的列时,是否必须删除工作表前面的列。 该代码查找上次使用的列并重新填充工作表,直到它到达 DO 列。
但是现在,当我上次按下按钮时列已经填充并且没有删除任何行时。 代码崩溃并说
执行时出现错误 1004。方法自动填充类范围失败。
仅当尚未使用列 DO 时,它才有效。
这是我的代码:
Dim lColumn As Long
Dim iCntr As Long
lColumn = 20
For iCntr = lColumn To 12 Step -1
If Cells(3, iCntr) = 0 Then
Columns(iCntr).Delete
End If
Next
With Workbooks("Bureauplanning2backup.xlsm").Worksheets("Planning")
Dim rngStart As Range
Set rngStart = .Cells(2, .Columns.Count).End(xlToLeft).Offset(,
-1).EntireColumn
Dim rngEnd As Range
Set rngEnd = .Cells(2, .Columns.Count).End(xlToLeft).EntireColumn
Dim rng As Range
Set rng = .Range(rngStart, .Columns("DO"))
Dim rngX As Range
Set rngX = .Range(rngStart, rngEnd)
End With
rngX.AutoFill rng, Type:=xlFillDefault
有没有办法检查 DO 列是否已填充,并且在填充时代码只是不运行该行:
rngX.AutoFill rng, Type:=xlFillDefault
您可以计算 DO 中有多少个单元格包含数据
If Application.WorksheetFunction.CountA(.Columns("DO")) = 0 Then
'zero cells have data so autofill
rngX.AutoFill rng, Type:=xlFillDefault
Else
'at least one cell has data so don't autofill
MsgBox "Column DO has already data"
End If