Issue with VB Script within Excel - Run-time error 6: Overflow

[spongebob]

Hello All,

Running a nice script written a long time ago by MrExcel team, but suddenly hitting a speed bump.
My guess is there may be too many rows within a range causing the issue.
It's a complex script so its difficult to explain and data has too much sensitive information to share.

My question will be a general type of question which can maybe give me some insight.
I do understand some basic coding (linux) so this is not my area of expertise or experience... any ideas would be appreciated.

There highlighted row in the code is this one:

If Rng.Cells.Count > 1 Then

A snipit of that code is:

Private Sub PopulateNow(wsSource As Worksheet, wsTarget As Worksheet, SearchString As String, Marker As String, RowNumber As Long, Optional FormulaIndicator As String)
Dim Rng As Range
Dim rw As Long, GapRow As Long, OrigGapRow As Long
If FormulaIndicator = "T" Then
    GapRow = 2
    OrigGapRow = 2
Else
    GapRow = 3
    OrigGapRow = 3
End If
If wsTarget.Cells(wsTarget.Range(Marker).Row + GapRow, 1).Value <> "" Then GapRow = wsTarget.Range("A" & wsTarget.Range(Marker).Row + GapRow).End(xlDown).Row + 1 - wsTarget.Range(Marker).Row
wsSource.UsedRange.AutoFilter
wsSource.UsedRange.AutoFilter Field:=9, Criteria1:="=" & SearchString
Set Rng = wsSource.Range("A2", wsSource.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
If Rng.Cells.Count > 1 Then
    wsTarget.Activate
    wsTarget.Rows("" & wsTarget.Range(Marker).Row + GapRow & ":" & wsTarget.Range(Marker).Row + GapRow + Rng.Cells.Count - 1).Select
    Selection.EntireRow.Insert shift:=xlDown
    wsSource.Range("A2", wsSource.Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
    wsTarget.Activate
    Range(Marker).Offset(GapRow, 0).PasteSpecial xlPasteValuesAndNumberFormats

 

[bobsan42]

This error would normally mean that the count returns a number bigger than the limits of the Long type.
So you would get this error if you try wsTarget.cells.count. but counting the all rows shouldn't be a problem.
try to include this row just before the problematic one:

debug.print Rng.address

Then check the immediate window for the output to confirm that you have a legitimate range object (e.g. $A$2:$A$10, although maybe a bit more convoluted with a filtered range)

 

[Anthony47]

Another option to test:

If Application.Intersect(Rng, wsSource.UsedRange).Cells.Count > 1 Then

 

[spongebob]

This error would normally mean that the count returns a number bigger than the limits of the Long type.
So you would get this error if you try wsTarget.cells.count. but counting the all rows shouldn't be a problem.
try to include this row just before the problematic one:

debug.print Rng.address

Then check the immediate window for the output to confirm that you have a legitimate range object (e.g. $A$2:$A$10, although maybe a bit more convoluted with a filtered range)

Thanks for the response!
So I added that to the line of the error, and unfortunately no additional information was shown.

 

[spongebob]

Another option to test:

If Application.Intersect(Rng, wsSource.UsedRange).Cells.Count > 1 Then

Sorry for note being very VB aware... where would I put this?
The other thing, from my rookieness here w/VB is we are testing for criteria, but what would the then do?

Thanks!!!

 

[spongebob]

Extra Note:
We have between 4 tabs, 150K lines of data.
Not sure if that helps.
We basically read 1 of 4 tabs, and place the appropriate data matching criteria to 40+ tabs, then read each tab and do the same.
One Tab has just over 100K lines, another 52K and the other 2 less than 2K.

 

[Anthony47]

The suggested instruction should have been used instead of the original If Rng.Cells.Count > 1 Then but I realize that it hardly would habe been useful.

However...
I guess that that instruction is used to be sure that there is at least one visible cell; in this case we should be aware that if no cell was returned, then the previous Set Rng = Etc Etc whould have failed with an error.

So my "penultimate" suggestion is replacing your block

Set Rng = wsSource.Range("A2", wsSource.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
If Rng.Cells.Count > 1 Then
    wsTarget.Activate
'etc etc

with this one

On Error Resume Next
Set Rng = wsSource.Range("A2", wsSource.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then
    wsTarget.Activate
'etc etc

As far as the debug.print, it returned the range address in the "Immediate window" of your vba

 

[spongebob]

While this helped me get an idea of where it crash, it seems like really the only way to troubleshoot this, is having the full sheet and code.
Since I basically have giant lists of data, if this was a table instead, would this type of quantity or records issue be improved or is it time to not do this type of thing in excel because of the volume of records?

Thanks for everyone trying to help me!!!

 

[Anthony47]

The initial problem was an Overflow error: did you overcome it or not yet?

If you have a different problem you should fully explain it, I don't understand what has to be troubleshoot

 

[bobsan42]

The amount of data excel can handle is only limitted by your system's resources and the workseet size.
And of course the performance will degrade with more data.
But this should not result in such errors.