Hide and unhide rows for multiple areas

  • Thread starter Thread starter leonidas
  • Start date Start date
L

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.
 
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
 
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?
 
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 :-)
 
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
 
Hi,

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