Hide and unhide rows for multiple areas

[leonidas]

Hi,

I have the following situation in excel:
I have a worksheet with a lot of data (text) in colomn B. This data is
split in pieces with above and beneath every piece an empty cell. I use
textboxes to assign macros to hide and unhide the pieces of data. The
VBA code of one of these macros is:

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.

 

[Guest]

Leonidas,

This should work for you.

Sub Hide_or_Unhide(rw As Long)

Dim rng As Range, i As Long, j As Long
Dim ar As Range
Set rng = Columns(2).SpecialCells(xlConstants, xlTextValues)
i = 0
j = 0
For Each ar In rng.Areas
i = i + 1
If i = rw Then
j = WorksheetFunction.CountIf(ar.Offset(0, 4), "*")
If j > 0 And ar.EntireRow.Hidden = False Then
Cells(ar.Row, "J").Select
Else
ar.EntireRow.Hidden = Not ar.EntireRow.Hidden
If ar.EntireRow.Hidden = True Then
Cells(ar.Row - 1, "J").Select
Else: Cells(ar.Row, "J").Select
End If
End If
Exit Sub
End If
Next

End Sub

 

[leonidas]

Hi,

Thank crazybass2 for your help. There is only one last problem, and
that's why your code doesn't work. Column F is never really empty,
there is a formula in it and there is only an outcome when there is a
number in column J.
So I tried to change your code to the following code(change is bold):

Sub Hide_or_Unhide(rw As Long)

Dim rng As Range, i As Long, j As Long
Dim ar As Range
Set rng = Columns(2).SpecialCells(xlConstants, xlTextValues)
i = 0
j = 0
For Each ar In rng.Areas
i = i + 1
If i = rw Then
j = WorksheetFunction.CountIf(ar.Offset(0, *8*), "*")
If j > 0 And ar.EntireRow.Hidden = False Then
Cells(ar.Row, "J").Select
Else
ar.EntireRow.Hidden = Not ar.EntireRow.Hidden
If ar.EntireRow.Hidden = True Then
Cells(ar.Row - 1, "J").Select
Else: Cells(ar.Row, "J").Select
End If
End If
Exit Sub
End If
Next

End Sub

But this doesn't work. When I put a number in column J the code still
hides the rows. Can you or someone else help me with this problem?

 

[Guest]

It would be helpful to know the formula in column F.

Mike

 

[leonidas]

Hi,

The formula's in column F are:

row 14: =IF(P14<>"";P14/L$229;"")
row 15: =IF(P15<>"";P15/L$229;"")

and so on.
Hope you can help me now

 

[Guest]

Ah, found the problem. Column J is numeric, not text. Changing the "j="
line should do the trick.

j = WorksheetFunction.CountIf(ar.Offset(0, 8), ">0") +
WorksheetFunction.CountIf(ar.Offset(0, 8), "<0")

If the numbers in column J are always positive then you can just use

j = WorksheetFunction.CountIf(ar.Offset(0, 8), ">0")

Glad to help.

Mike

 

[leonidas]

Hi,

Thank you very much for your help Mike and everybody else who already
helped me with this code! I really appreciate it!