VBA runtime error '6': Overflow

snooks679 · Apr 21st 2013

[h=1]VBA runtime error '6': overflow [code here][/h]I have 2 worksheets giving me the same error. This is part of the code. What do I need to change here in order to get rid of this error? Thanks in advance!

code 1:

Code

Dim row As Integer
Dim LastRow As Integer
LastRow = Sheets("worksheetname").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row


Range(Cells(3, 1), Cells(LastRow, 41)).ClearFormats
Range(Cells(3, 1), Cells(LastRow, 41)).Locked = False


If LastRow < Cells(LastRow, 1).End(xlDown).row Then
    
    Range(Cells(LastRow + 1, 1), Cells(LastRow, 1).End(xlDown)).Locked = True
    Rows(CStr(LastRow + 1) & ":" & CStr(Cells(LastRow, 1).End(xlDown).row)).Hidden = True


End If

Display More

Code 2:

Code

Private Sub SetValidationAndFormatting()


    LastRow = SetLastRow("worksheetname")


    With Sheets("worksheetname")
    .Range(Cells(3, 1), Cells(LastRow_DRC, 7)).Locked = True
    
    If LastRow_DRC < .Cells(LastRow_DRC, 1).End(xlDown).row Then
    
        .Range(.Cells(LastRow_DRC + 1, 1), .Cells(LastRow_DRC, 1).End(xlDown)).Locked = True
        .Rows(CStr(LastRow_DRC + 1) & ":" & CStr(.Cells(LastRow_DRC, 1).End(xlDown).row)).Hidden = True


    End If

Display More

Code

Private Function SetLastRow(WorksheetName As String) As Integer
    SetLastRow = Sheets("Data Role Coverage").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End Function

MODERATOR EDIT

Welcome to OZ Grid
Please use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.

I’ve added them this time but please comply in the future

HaHoBe · Apr 21st 2013

Re: VBA runtime error '6': Overflow

Hi, snooks679,

instead of using Integer try using Long:

Code

Dim row As Long
Dim LastRow As Long
'...
Private Function SetLastRow(WorksheetName As String) As Long

Ciao,
Holger

snooks679 · Apr 21st 2013

Re: VBA runtime error '6': Overflow

Quote from HaHoBe;661030
Hi, snooks679,

instead of using Integer try using Long:
Code
Dim row As Long
Dim LastRow As Long
'...
Private Function SetLastRow(WorksheetName As String) As Long
Ciao,
Holger

Code

HoIger,

This doesn't seem to work. My original code works for fewer rows but it fails when it has 800 rows.

snooks679 · Apr 21st 2013

Re: VBA runtime error '6': Overflow

It works now! I had missed a couple lines that had:

Code

Dim row as integer, I changed all to:

Code

Dim row as long and seems to work fine now.

Holger thank you for your assistance.

Eagle04 · Aug 25th 2015

Re: VBA runtime error '6': Overflow

I'm having a similar issue to this and was hoping to get some assistance. I am also receiving a run-time error 6 Overflow error and can't identify what's causing this. Below is my code:

Code

Dim SheetLastRow As Long
Dim SheetLastcolumn As Byte
Dim RecentMonth, ii As Long
Dim OldMonth, iii As Long
Dim ExceptionHolder, iiii As Long
Dim Dict As Scripting.Dictionary
Dim RowHolder As Long
Dim wb As String
Dim myArray As Variant




myArray = Array(4, 14, 16) ' Enter column numbers we're interested in evaluating between the two months




ReDim ExceptionHolder(1 To 1, 1 To 3)




wb = ActiveWorkbook.Name




Set Dict = New Scripting.Dictionary




SheetLastRow = Workbooks(wb).Sheets("Full File July").Range("A60000").End(xlUp).Row
SheetLastcolumn = Workbooks(wb).Sheets("Full File July").Range("SZ12").End(xlToLeft).Column
RecentMonth = Workbooks(wb).Sheets("Full File July").Range(Cells(12, 1), Cells(SheetLastRow, SheetLastcolumn)).Value

Display More

The line RecentMonth = Workbooks(wb).Sheets("Full File July").Range(Cells(12, 1), Cells(SheetLastRow, SheetLastcolumn)).Value is what is causing the error. There are only 21 columns in the sheet so I don't think the Byte assignment should be an issue(?) Any insights would be much appreciated!

**pike** · Aug 26th 2015

Re: VBA runtime error '6': Overflow

Hi Eagle04,
Welcome to the forum. can you please start your own thread and reference this thread if relevant

VBA runtime error '6': Overflow

Participate now!

Share

Users Viewing This Thread