using .resize or an offset

[cereldine]

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)

 

[cereldine]

prob should have mentioned that the find function means that dcell could
be any column in worksheet. Am i right in thinking that if i wanted to
use an offset then i would need to declare which column to read down
e.g OFFSET($A$,0,0,COUNTA($A:$A ets

 

[Dave Peterson]

I'd just look under the dCell. If it's empty, you're done. If that cell isn't
empty, you can essentially hit the End key followed by the down arrow to find
that last filled cell in that area.

Option Explicit
Sub testme()

Dim dCell As Range
Dim dCellBot As Range

Set dCell = ActiveSheet.Range("C3") 'or the results of your find

If dCell Is Nothing Then
'what happens if it isn't found?
ElseIf IsEmpty(dCell.Offset(1, 0).Value) Then
Set dCellBot = dCell
Else
Set dCellBot = dCell.End(xlDown)
End If

If dCell Is Nothing Then
'do nothing
Else
ActiveSheet.Range(dCell, dCellBot).Copy _
Destination:=whereeveryouwanttogo
End If

End Sub