You don't describe the orientation, but assume we are working in a single
column and there will always be more than one filled cell between the two
empty cells.

Sub abc()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("B9").Offset(1, 0)
Set rng1 = Range(rng, rng.End(xlDown))
MsgBox rng1.Address
End Sub

If there could always be only at least one filled cell then


Sub abc()
Dim rng As Range
Dim rng1 As Range
Set rng = Range("B9").Offset(1, 0)
if isempty(rng.offset(1,0)) then
set rng1 = rng
else
Set rng1 = Range(rng, rng.End(xlDown))
end if
MsgBox rng1.Address
End Sub

--
Regards,
Tom Ogilvy

"leonidas" wrote:


Hi,

In Excel I have two empty cells and between them some cells with text
in it. I am looking for a macro that can get the range of the cells
between the empty cells and use this range for another macro. Can
someone help me?


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=551453