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

 

[Fluff]

Try changing Rng.Cells.Count to Rng.Cells.CountLarge

 

[Anthony47]

Try changing Rng.Cells.Count to Rng.Cells.CountLarge

I think that the original If Rng.Cells.Count > 1 Then is useless, as creating Rng would fail in case there is no any cell to be included in the range; that's why I suggested switching to If Not Rng Is Nothing Then (in a more articulated sequence)...

 

[Fluff]

The OP's will give an overflow error if row 2 is the only visible row (and row 1 is empty), which countlarge will overcome.

If the range was not created you would not get an overflow error.
That said, I suspect that what the OP actually needs is Rng.Columns(1).CountLarge

 

[spongebob]

Hello,

Not sure where you would like me to use the Rng.Columns(1).CountLarge, I didn't see the string Rng.Columns used anywhere?

Thanks!

 

[Alex Blakenburg]

Try Fluff's post #11 suggestion first.

 

[Fluff]

Try changing this line If Rng.Cells.Count > 1 Then to If Rng.Columns(1).CountLarge > 1 Then

 

[spongebob]

Hello,

Thanks, I tried that and it did go through the process without errors, however, it also didn't populate data from the tabs that it reads from, and then place them on tabs.
That may be what you wanted to see.
Let me know...

 

[Fluff]

Does your wsSource sheet have a header row & if so what row is it on?

 

[spongebob]

There are no header rows.

 

[Fluff]

In that case you cannot reliably use autofilter.