This is probably not a complete solution but a strategy.

YOu could do something resembling

dim array() as variant
redim array(NumCellsInrange)
lcv=0
for each cell in range
if cell meets criteria then
array(lcv) = cell.value
lcv=lcv+1
endif

next cell
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


" wrote:

I wish to extract data from a range that meets certain critera and
place it on another part of my spreadsheet, I am using a simple filter
and copy and paste, was wondering if I could do it with out activated
the sheet, heres what i am using at the mo

CST = Range("D7")
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"http://10.234.95.121/Complex_Job_Lists/Wlr3/PreCalling.csv"

Selection.AutoFilter

Selection.AutoFilter Field:=2, Criteria1:=CST

Range("A2:l54000").Select
Selection.Copy
Windows("end user calling list ver8.xls").Activate
Sheets("database").Select
Range("B15").Select
ActiveSheet.Paste
Range("B4").Select
Range("A15:A1000").ClearContents
Range("A14").Select