Overflow Error 6 with the following Excel 2010 VBA

Question

The following code will correctly format my template the way I want. However, if the template is empty and the user clicks the preload button on the sheet, I will get an overflow error 6. Is there a way to remove the cause of 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 

Debugging points to the following line:

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

thanks

Ryan

Answer 1

You get overflow because Integer in VBA is a 16-bit subscription number (maximum value 32767). Regardless of the excel version, you have a minimum of 65535 lines and most likely more if you use the XLSX file format. You need to change rowNumber to Long

And you also need to copy around an empty sceneio sheet. When you call this line:

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

and the worksheet is empty, .End(xlDown) will return the last possible row in the worksheet, which in the case of Excel 2010 (and Excel 2007) is 1048576. After changing rowNumber to Long you will no longer receive an overflow error, but you will encounter a problem with this line:

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

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

The easiest way is to simply add a line to check this:

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

Answer 2

Change rowNumber to Long , then add 1& or use CLng to get rid of the mismatch type error.

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

or

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

Answer 3

Can you show us what your data looks like in column A? You can also try this code, which checks the number of lines before trying to get the line number

 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