hide and unhide rows
 

Hi,

I have the following VBA code but it is not working the way I woul
like it to.

Sub ProcessGroup5()

Dim rw As Long
rw = 5
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
Cells(ar(0).Row, "J").Select
End If
Exit Sub
End If
Next

End Sub

problems:
1) The hide and unhide function works fine, but the code only check
the first cell in column F of the selected range of rows. So if th
range is for exemple rows 14:24 it only checks if cell F14 is empty o
not empty and it should also check cells F15:F24.
2) When the selected range of rows, say 14:24 are unhidden, the cod
selects cell J13 but this should be J14. When one of the cells F14:F2
is not empty the code should always select cell J14. When the selecte
range of rows are hidden again (because cells F14:F24 are empty), th
code should select cell J13.

Can someone help me with these problems? 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=55178

hide and unhide rows
 

Why does it check i = rw, and why exit sub when matched? Are you trying to
cater for multiple areas as well as the range in that area?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"leonidas" wrote in
message ...

Hi,

I have the following VBA code but it is not working the way I would
like it to.

Sub ProcessGroup5()

Dim rw As Long
rw = 5
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
Cells(ar(0).Row, "J").Select
End If
Exit Sub
End If
Next

End Sub

problems:
1) The hide and unhide function works fine, but the code only checks
the first cell in column F of the selected range of rows. So if the
range is for exemple rows 14:24 it only checks if cell F14 is empty or
not empty and it should also check cells F15:F24.
2) When the selected range of rows, say 14:24 are unhidden, the code
selects cell J13 but this should be J14. When one of the cells F14:F24
is not empty the code should always select cell J14. When the selected
range of rows are hidden again (because cells F14:F24 are empty), the
code should select cell J13.

Can someone help me with these problems? Thanks in advance.


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

hide and unhide rows
 

if range("a1:a10")="check value" then
end if
You can give any range withing the double quotes


--
anusha2525
------------------------------------------------------------------------
anusha2525's Profile: http://www.excelforum.com/member.php...o&userid=35408
View this thread: http://www.excelforum.com/showthread...hreadid=551788

hide and unhide rows
 

if range("a1:a10")="check value" then
end if
You can give any range withing the double quotes


--
anusha2525
------------------------------------------------------------------------
anusha2525's Profile: http://www.excelforum.com/member.php...o&userid=35408
View this thread: http://www.excelforum.com/showthread...hreadid=551788

hide and unhide rows
 

Hi Bob,

check my previous post:

http://www.excelforum.com/showthread...=1#post1621039

Yes, there are more areas.


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

hide and unhide rows
 

But if you're checking the .value, you'll want to limit it to a single cell.

anusha2525 wrote:

if range("a1:a10")="check value" then
end if
You can give any range withing the double quotes

--
anusha2525
------------------------------------------------------------------------
anusha2525's Profile: http://www.excelforum.com/member.php...o&userid=35408
View this thread: http://www.excelforum.com/showthread...hreadid=551788

--

Dave Peterson