I'm looking to create a routine that opens up external worksheet an
finds a record based on criteria i have determined and then copy th
found range.

So far i have been able to create a routine that finds the value i wan
and then uses offset to point at the first cell in the range i want t
copy. see below

Set Dcell = sh1.Cells.find(sCode)

Dcell.Offset(4, 0).Select

I know want to be able to create a range that is based on how man
records are beneath dcell, e.g. dcell may contain data for 1997 and th
cell below it data for 1998 and so. Is there away to dynamically loo
down the range until a blank cell is found, thus declaring that as th
end of the range?

Do i need to use offset and countA OR resize Or a combination of both?

so far ive been trying following to no evail, thanks

cell1.Offset(1,0).Resize(1,5).copy _
destination:=cell.offset(0,1) -- not dynamic

Set dynRng = offset(dcell,0,0,CountA(dcell&:&dcell)

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53158