CODEWITHSUNDEEP
excelvba
skkakkar
skkakkar

Reputation: 2828

Copying Filtered Range getting overflow error

Failure counts of failed products counted by COUNTIF Function. Results of counts > 0 are auto filtered. Filtered results to be pasted to sheet3. Limited range being copied for test of program logic. Getting Run time error 6 Overflow on the code line: numbElements = targetRng.End(xlDown).Row Related information appended below. Can someone point out the mistake.

Sheet before auto filter
sheet after auto filter 

 Sub test()

    Dim targetRng As Range
    Dim i As Integer

    Set targetRng = Sheets(3).Range("a1")
    Range("A1", Range("A999").End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=targetRng, Unique:=True

    Dim numbElements As Integer
    numbElements = targetRng.End(xlDown).Row
    Dim arr() As String

    ReDim arr(1 To numbElements) As String

    For i = 1 To numbElements
        arr(i) = targetRng.Offset(i - 1, 0).Value
    Next i

End Sub

Upvotes: 0

Views: 63

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

Maximum Integer value in VBA is 32767 (2^15-1). Assigning row number to Integer may cause overflow.

You should change your numbElements and i variable definitions to Long. Maximum value of Long is 2147483647 (2^31-1).

Upvotes: 2

Related Questions