以下Excel 2010 VBA溢出错误6

Question

下面的代码将按照我想要的方式正确格式化我的模板。但是，如果模板为空，并且用户点击工作表上的准备上传按钮，我将收到溢出错误6。有什么方法可以消除导致此错误的原因吗？

Sub PrepForUpload()
Dim cel As Range, rng As Range
Set rng = Range("A2", Range("A65536").End(xlUp))
For Each cel In rng
If cel.Value = "" Then
If cel.Offset(, 2).Value = "" Then
cel.EntireRow.Delete
End If
End If
Next cel
Dim rowNumber As Integer
With Sheets("Initiatives")
If Len(.Cells(2, 1)) = 0 Then
rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1
Else: rowNumber = .Cells(2, 1).End(xlDown).Row + 1
End If
.Rows(rowNumber & ":" & .Rows.Count).Clear
End With

End Sub

调试指向以下行作为问题：

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

感谢

Ryan

Answer 1

VBA中的Integer是一个16位有符号数字(最大值为32767)，因此会发生溢出。无论excel的版本如何，您至少有65535行，如果您使用XLSX文件格式，则可能会有更多行。您需要将rowNumber更改为Long

您还必须围绕空白工作表场景进行编码。当你拨打这条电话时：

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1

如果工作表为空，.End(xlDown)将返回工作表中可能的最后一行，在Excel 2010(和Excel 2007)的情况下为1048576。一旦您将rowNumber更改为Long，您将不再得到溢出错误，但您将遇到以下行的问题：

.Rows(rowNumber & ":" & .Rows.Count).Clear

这是因为您试图选择一个不存在的范围(行1048577)(因此类型不匹配)。您需要添加一行代码来处理此场景。请先检查空白工作表，或检查行>1048576。

最简单的方法是添加一行来检查：

If rowNumber <= 1048576 Then
.Rows(rowNumber & ":" & .Rows.Count).Clear
End If

Answer 2

将rowNumber更改为Long，然后添加1&或使用CLng来消除"类型不匹配"错误。

rowNumber = CLng(.Cells(2, 1).End(xlDown).End(xlDown).Row + 1)

或

rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1&

Answer 3

你能告诉我们你的数据在A列中是什么样子吗？您也可以尝试此代码，在尝试获取行号之前测试行数

Dim cel As Range, rng As Range
If Application.WorksheetFunction.CountA(Columns(1)) <= 1 Then
MsgBox "No lines"
Exit Sub
End If
Set rng = Range("A2", Cells(Rows.Count, 1).End(xlUp))
For Each cel In rng
If Len(cel.Value) = 0 Then
If Len(cel.Offset(, 2).Value) = 0 Then cel.EntireRow.Delete
End If
Next cel
Dim rowNumber As Long
With Sheets("Initiatives")
If Application.WorksheetFunction.CountA(.Columns(1)) <= 1 Then
MsgBox "No lines in sheet Initiative"
Exit Sub
End If

If Len(.Cells(2, 1)) = 0 Then
rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1
Else: rowNumber = .Cells(2, 1).End(xlDown).Row + 1
End If
.Rows(rowNumber & ":" & .Rows.Count).Clear
End With