Macro to hide and unhide multiple rows at a time

[tlc53]

Hi there,
I have a table of 40 lines in which I manually hide/unhide depending on the amount of data needing to go in (40 lines max).
I would like to have two macro buttons so that I can hide/unhide 5 rows at a time. So 1-5 would always show, add +5 line button and rows 1-10 would show, add +5 button again and 1-15 would show. Then should I want to take 5 lines away, -5 button would go back to showing 1-10. However, if the cells contain data, they should not be able to be hidden.
C37:C76 are the entry cells.
Bit of a big ask but fingers crossed.
Thank you!

 

[footoo]

Try this :

Sub ShowFive()
Dim rng As Range
On Error Resume Next
Set rng = [C42:C76].SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
    [C42:C46].EntireRow.Hidden = False
ElseIf rng.Count = 35 Then
    Exit Sub
Else: rng(rng.Count + 1, 1).Resize(5).EntireRow.Hidden = False
End If
End Sub


Sub HideFive()
Dim r%
For r = 76 To 42 Step -5
    If WorksheetFunction.CountA(Range(Cells(r - 4, "C"), Cells(r, "C"))) <> 0 Then
        Exit For
    Else
        Rows(r - 4 & ":" & r).Hidden = True
    End If
Next
End Sub

 

[JoeMo]

Here's a quick, untested forms control button code for the +5. If it works for you, you can adapt it for the -5 button. Put the button in rows 1-5 and se**** properties to not change size or move with cells.

Sub Button1_Click()
'+5 button
Dim N As Variant
N = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Count
Application.ScreenUpdating = False
If Not IsError(N) And N < Rows.Count - 5 Then
    Rows("6:" & Rows.Count).Hidden = True
    Rows("6:" & N + 5).Hidden = False
End If
Application.ScreenUpdating = True
End Sub

 

[tlc53]

Absolutely brilliant!! Works a treat!
I'm so happy. Thank you!!

 

[JoeMo]

Absolutely brilliant!! Works a treat!
I'm so happy. Thank you!!

You had two responses. Care to share which one you are referring to? Or is it both?

 

[tlc53]

When I posted my gratitude, I had only seen the answer from footoo at the time.
However, yours works too, thanks!!
Although your code was shorter, I ended up going with footoo because I could understand the code easier and adapt it with the changing cell references.

Thanks again!