I don't have any problems when i run your code. I tested on Excel 2003
and Ubound(a,1) is 8287 in testing sheet.

Keiji

ub wrote:
Hi
I am getting subscript out of range error in my code.

But if I change the For statement
from
For i = 1 to ubound(a)
to
For i = 1 to 8000
it works.
Ubound(a) has 8266 rows

My Code is:

Dim lastrow As Integer
Dim a As Variant
Dim i As Double
Dim k As Double

Dim ca() As Variant
'break


lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

a = Worksheets("Sheet1").Range("a2", "d" & lastrow).Value
ReDim ca(1 To UBound(a, 1), 1 To 4)

k = 1
i = 1

For i = 1 To UBound(a, 1)

If a(i, 3) = "Y" Then

ca(k, 1) = a(i, 3)


k = k + 1

End If

Next i
Worksheets("Sheet4").Range("b3").Resize(UBound(a, 1), 1).Value = ca


*******
Please advise how can I correct this