溢出错误6与以下Excel 2010 VBA [英] Overflow Error 6 with the following Excel 2010 VBA

问题描述

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

The following code will format my template correctly the way I want. However, in the event the template is empty and a user hits the prep upload button on the sheet, I will receive an Overflow Error 6. Is there any way to remove what is causing this error?

    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

调试点后续ng线作为问题：

Debug points to the following line as the issue:

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

谢谢

Ryan

推荐答案

您正在获得溢出，因为VBA中的 Integer 是一个16位签名号（最大值32767）。不管excel的版本如何，您至少拥有65535行，如果您使用的是XLSX文件格式，则可能更多。您需要将 rowNumber 更改为 Long

You are getting an overflow because Integer in VBA is a 16 bit signed number (max value of 32767). Regardless of the version of excel, you have a minimum of 65535 rows and most likely more if you are using the XLSX file format. You need to change rowNumber to a Long

您还必须在空白工作表scenerio周围进行编码。当你调用这一行：

And you also have to code around the blank worksheet scenerio. When you call this line:

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

，工作表为空， .End（xlDown） / code>将返回工作表中的最后一行，在Excel 2010（和Excel 2007）的情况下为1048576）。将 rowNumber 更改为 Long 您将不会再收到溢出错误，但您将遇到此行的问题：

and the worksheet is blank, .End(xlDown) will return the last possible row in the worksheet, which in the case of Excel 2010 (and Excel 2007) is 1048576. Once you change rowNumber to a Long you will no longer get the overflow error, but you will run into a problem with this line:

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

这是因为您尝试选择不存在的范围（行1​​048577）（因此类型不匹配）。您需要添加一行代码来解决这种情况。最初检查一个空白的工作表，或检查行> 1048576。

This is because you are trying to select a range (row 1048577) that does not exist (hence the type mismatch). You need to add a line of code to work around this scenario. Either check initially for a blank worksheet, or check for row > 1048576.

最简单的事情是添加一行来检查：

The simplest thing to do is to just add a line to check this:

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

这篇关于溢出错误6与以下Excel 2010 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！