Hi,

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

--
leonida
-----------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55145

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

Hi,

Thanks already for your help! Although I think I have to explain what
really want the macro to do.
I have a worksheet with a lot of data (text) in colomn B. This data i
split in pieces with above every piece a kind of heading. I have
modules in the VBA Editor to hide and unhide the split data so you wil
see only the headings or you will see everything. This code is fo
module1:

Sub SubCathegorieenWeergeven1()

Application.ScreenUpdating = False
ActiveSheet.Rows("14:24").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Shapes("Text Box 1").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Range("J14").Select

End Sub

and for module2:

Sub SubCathegorieenVerbergen1()

Application.ScreenUpdating = False
ActiveSheet.Rows("14:24").Select
If ActiveSheet.Range("F14") < "" Then
ActiveSheet.Range("J14").Select
Exit Sub
Else
ActiveSheet.Rows("14:24").Select
Selection.EntireRow.Hidden = True
End If
ActiveSheet.Shapes("Text Box 2").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Range("J13").Select

End Sub

I have a total of 14 times module1 and 14 times module2. So all th
data in the code change.
The problems a
1) when rows 14 to 24 are selected in module2 I want to check if ther
are numbers in cells F14 to F24. If so, then the macro should onl
select cell J14, else the rows 14 to 24 can be hidden. I only don't ge
this macro to check if there are numbers in cells F14 to F24.
2) when a row is inserted in the worksheet, the code in the macro wil
not change the rows. So if I insert a row between rows 10 and 11, the
the selected rows will still be 14 to 24 instead of 14 to 25. If I hav
some rows, say 45 to 67, as another piece of data with a heading in ro
44, then it also will not change.

I hope it is clear now what I mean and I hope you can give me an
advise for these problems to be solved

--
leonida
-----------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55145

Assume rows 14:24 is the second group of text entries in column B, then you
can run this and I think it will do what you want. It will process the
second group regardless of which row it occurs in. It expects a "block" of
text values in column B has no empty cells embedded within the block (in
column B).

Sub ProcessGroup2()
Dim rw As Long
rw = 2
Hide_or_Unhide rw
End Sub

Sub Hide_or_Unhide(rw As Long)
Dim rng As Range, i As Long
Dim ar As Range
Set rng = Columns(2).SpecialCells( _
xlConstants, xlTextValues)
i = 0
For Each ar In rng.Areas
i = i + 1
If i = rw Then
If Cells(ar(1).Row, "F") < "" and _
ar.EntireRow.Hidden = False Then
Cells(ar(1).Row, "J").Select
Else
ar.EntireRow.Hidden = Not _
ar.EntireRow.Hidden
End If
Exit Sub
End If
Next
End Sub

See if you can adapt that to what you are doing. You may be able to
eliminate a lot of your code. If rows are hidden, it should unhide them and
if not hidden it should hide them

--
Regards,
Tom Ogilvy


"leonidas" wrote:


Hi,

Thanks already for your help! Although I think I have to explain what I
really want the macro to do.
I have a worksheet with a lot of data (text) in colomn B. This data is
split in pieces with above every piece a kind of heading. I have 2
modules in the VBA Editor to hide and unhide the split data so you will
see only the headings or you will see everything. This code is for
module1:

Sub SubCathegorieenWeergeven1()

Application.ScreenUpdating = False
ActiveSheet.Rows("14:24").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Shapes("Text Box 1").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Range("J14").Select

End Sub

and for module2:

Sub SubCathegorieenVerbergen1()

Application.ScreenUpdating = False
ActiveSheet.Rows("14:24").Select
If ActiveSheet.Range("F14") < "" Then
ActiveSheet.Range("J14").Select
Exit Sub
Else
ActiveSheet.Rows("14:24").Select
Selection.EntireRow.Hidden = True
End If
ActiveSheet.Shapes("Text Box 2").Select
Selection.ShapeRange.ZOrder msoSendToBack
ActiveSheet.Range("J13").Select

End Sub

I have a total of 14 times module1 and 14 times module2. So all the
data in the code change.
The problems a
1) when rows 14 to 24 are selected in module2 I want to check if there
are numbers in cells F14 to F24. If so, then the macro should only
select cell J14, else the rows 14 to 24 can be hidden. I only don't get
this macro to check if there are numbers in cells F14 to F24.
2) when a row is inserted in the worksheet, the code in the macro will
not change the rows. So if I insert a row between rows 10 and 11, then
the selected rows will still be 14 to 24 instead of 14 to 25. If I have
some rows, say 45 to 67, as another piece of data with a heading in row
44, then it also will not change.

I hope it is clear now what I mean and I hope you can give me any
advise for these problems to be solved.


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

Hi Tom,

Thank you very much for you help! I have only one last question.
I have entered your code in the VBA Editor and assigned the macro t
the a textbox which has the function of a heading. The hide and unhid
function works fine, but your code only checks the first cell in colum
F of the selected range of rows. So if the range is rows 14:24 it onl
checks if cell F14 is empty and it should also check cells F15:F24. I
that possible? Thanks in advance

--
leonida
-----------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...fo&userid=3537
View this thread: http://www.excelforum.com/showthread.php?threadid=55145

Dim rng As Range

Set rng = Range("H1").End(xlDown)
Set rng = Range(rng, rng.End(xlDown))


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"leonidas" wrote in
message ...

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