CODEWITHSUNDEEP
excelvbarange
Firefox333
Firefox333

Reputation: 93

VBA Excel - overflow error on range select

I got the following code, now when I filter my results with the following and it returns no results I get an overflow error. But I don't see why. I figured it would be the fact I try to select a bad range. So I tried checking if the first cell with a value is empty or not. But that doesn't seem to help at the moment.

If Range("A2") <> "" Then
    iCtr = Range("A2", Range("A2").End(xlDown)).Count + 1
    ActiveSheet.Range("$A$1:$AB" & iCtr).AutoFilter Field:=8, Criteria1:="=1100" _
    , Operator:=xlOr, Criteria2:="=1110"
End If

So anyone has any idea how to solve this or what the issue is?

Upvotes: 2

Views: 4245

Answers (2)

bobyuan
bobyuan

Reputation: 370

From information above, it is highly suspect iCtr as Integer that caused the problem. The maximum Integer is 32767, so it cannot hold large number. You can define it as Long to avoid the overflow problem.

Dim iCtr As Long

As a Long, the maximum value will goes up to 2147483647.

Upvotes: 2

hnk
hnk

Reputation: 2214

Define 

Dim iCtr AS Long

as that would safely let it accept range RowCounts of any size.

Upvotes: 4

Related Questions