Overflow Error 6 with the following Excel 2010 VBA

Question

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

Debug points to the following line as the issue:

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

Thanks

Ryan

It shouldn't be called twice, however even when removed it still produces an overflow. I am not sure how to figure out a way to code for an empty template without changing my entire code. — RCoy1978
– RCoy1978, Commented Sep 27, 2012 at 15:10
I deleted my original reply. It is not entirely clear to me what you are attempting to do with the line: rowNumber = .Cells(2, 1).End(xlDown).End(xlDown).Row + 1 => end(xlDown).row is like doing ctrl+arrow down. Why would you call it twice? I can upload new code if you need it. — Trace
– Trace, Commented Sep 27, 2012 at 15:14
Integer has an upper limit of about 32000. You have more rows than that, so you should use Long instead for rowNumber. — Tim Williams
– Tim Williams, Commented Sep 27, 2012 at 15:14
Kim, the end result of the template is uploaded into Access. If the List box within the cells below populated dated are imported, it will blow up the access database. The line is meant to delete content below the populated data, including the listbox format — RCoy1978
– RCoy1978, Commented Sep 27, 2012 at 15:20

psubsee2003 · Accepted Answer · 2012-09-27 15:56:39Z

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

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

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

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

AMissico · Accepted Answer · 2012-09-27 15:32:11Z

0

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

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

or

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

answered Sep 27, 2012 at 15:32

AMissico

21.8k7 gold badges80 silver badges108 bronze badges

1 Comment

RCoy1978 Over a year ago

Amiss, this didn't produce any results. Debugger still points to the following after the mismatch error: ".Rows(rowNumber & ":" & .Rows.Count).Clear" This is driving me nuts!!

nutsch · Accepted Answer · 2012-09-27 16:10:39Z

Can you show us what your data looks like in column A? You can also try this code, which test for the number of lines before attempting to get the row 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

Collectives™ on Stack Overflow

Overflow Error 6 with the following Excel 2010 VBA

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related