我有一个放置0而不是空白行的宏,它们有65000多个都可以正常工作,但是问题是宏停止在第62000行,即使下一行有数据也是如此。这是代码:
Sub QuickCull()
On Error Resume Next
Columns("a").SpecialCells(xlBlanks).EntireRow.Delete
Columns("b").SpecialCells(xlBlanks).EntireRow.Delete
Columns("d").SpecialCells(xlBlanks).EntireRow.Delete
Dim col As Range
Set col = Cells(Rows.Count, "E").End(xlUp)
Dim r As Range
Set r = Range("E2", col).Resize(, 4)
Dim cell As Range
For Each cell In r
If cell.Value = "" Then
cell.Value = 0
Else
cell.Value = 1
End If
Next cell
Cells("J1").Activate
End Sub
在我看来,问题出在范围上,但根本不确定。是什么原因造成的?
该Range.Cells财产不接受单元格地址引用相同的样式,一个Range对象一样。
Cells("J1").Activate
'should be,
Range("J1").Activate
如果E:H列的值更改为1,而空白值更改为0,则可以使用xlCellTypeBlanks和xlCellTypeConstants继续使用Range.SpecialCells方法。
Sub QuickCull()
Dim col As Range, r As Range
With Worksheets("data") '<~~ you should know ehat worksheet you are on!
On Error Resume Next
.Columns("a").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns("b").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns("d").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Set col = .Cells(Rows.Count, "E").End(xlUp)
Set r = .Range("E2", col).Resize(col.Row - 1, 4)
r.SpecialCells(xlCellTypeConstants) = 1
r.SpecialCells(xlCellTypeBlanks) = 0
.Range("J1").Activate '<~~ or .Cells(4, "J").Activate
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句