Runtime error 6 overflow

Question

Hello,

I've created a macro which searches the whole table and filters the results. It works fine on tables with less that 30, 000 lines however anything over 30 000 I receive the run-time 6 overflow error. I've "Dim colCounter As Long" however this still hasn't fixed the problem. Could you please let edit the below code, and advise me as to what it should be?

the issue debug is on the following line:

main.Range("A" & (CInt(colRowsToShow.Item(colCounter)))).EntireRow.Hidden = False

Much Appreciated!

****************

Sub Search_Click()

Dim objListObj As ListObject

Dim objQryTbl As QueryTable

Dim tblRange As Range

Dim searchterm As String

Dim c As Range

Dim colRowsToShow As Collection

Dim colCounter As Long

Set objListObj = main.ListObjects(1)

Set tblRange = objListObj.QueryTable.ResultRange

searchterm = Trim(main.Range("keyword"))

If Len(searchterm) > 500 Then

    MsgBox "Only 500 characters are allowed in the search term for this demo"

    Exit Sub

End If

'If MsgBox("This needs to clear any auto-filter applied on any colum in order to work." & vbCrLf & _

'"Click YES if this is OK, click NO to cancel.", vbYesNo) = vbNo Then

    'Exit Sub

'End If

tblRange.AutoFilter

tblRange.AutoFilter

tblRange.EntireRow.Hidden = False

If searchterm = "" Then

    Exit Sub

End If

Set tblRange = tblRange.Offset(1, 0).Resize(tblRange.Rows.Count - 1)

Set colRowsToShow = New Collection

'

With tblRange

    Set c = .Find(searchterm, LookIn:=xlValues, MatchCase:=False, LookAt:=xlPart)

    If Not c Is Nothing Then

        firstAddress = c.Address

        Do

            colRowsToShow.Add c.Row

            Set c = .FindNext(c)

        Loop While Not c Is Nothing And c.Address <> firstAddress

    End If

End With

tblRange.EntireRow.Hidden = True

If colRowsToShow.Count > 0 Then

    For colCounter = 1 To colRowsToShow.Count

        Debug.Print colRowsToShow.Item(colCounter)

        main.Range("A" & (CInt(colRowsToShow.Item(colCounter)))).EntireRow.Hidden = False

    Next

End If

'tblRange.Find

End Sub

********

Answer 1

CInt forces the value of colRowsToShow.Item(colCounter) to an Integer (-32768 to +32767). Simply omit CInt, it is not needed:

        main.Range("A" & colRowsToShow.Item(colCounter)).EntireRow.Hidden = False