try this

lr=cells(rows.count,1).END(XLUP).ROW-1
..Offset(1).Resize(lr).SpecialCells(xlCellTypeVisi ble).copy


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jenny" wrote in message
...
I am tryng to copy and paste after filter using the below macro. However,
it
prompt me an error stating (total no of rows: 30K, after filtered: 15K):
"Microsoft Office Excel cannot create or use the data range reference
because it is too complex...."
Even when I put in the "On error resume next", my range data selection is
blank and I copy and paste empty data.
*********My macro***********
If .Cells(1, Colcount) = "KEYFIG" Then
.Cells(1, 1).AutoFilter Field:=Colcount, Criteria1:="OPT RDP"
With .AutoFilter.Range
On Error Resume Next
Set Filterrg = .Offset(1, 0).Resize(.Rows.Count - 1,
1).SpecialCells(xlCellTypeVisible)
Filterrg.Copy
End With
End If
***************************