Dynamic range for hiding rows

Question

I have workbook where I have buttons for hiding and unhiding rows. So when rows are hided one button is also hided and then vise versa. Then I have range that should be hided and unhided while clicking the buttons. Is there any way to make this somehow dynamic?

The problem is that I have like 40 buttons and once I am making some edit to calculator and adding a new row to my worksheet I have to rewrite all the ranges. Ranges are in sequence so they goes like:

Range("1254:1275").EntireRow.Hidden = True
Range("1254:1275").EntireRow.Hidden = False
next one
Range("1276:1298").EntireRow.Hidden = True
Range("1276:1298").EntireRow.Hidden = False
next one
Range("1299:1350").EntireRow.Hidden = True
Range("1299:1350").EntireRow.Hidden = False
etc.

Is it somehow possible to have something like +22 instead of 1254:1275? Then +23 instead of 1276:1298 etc.?

For 40 buttons I have 80 subs (1 for hide and 1 for unhide). Each operation in own sub like so:

HIDE BUTTON:

Sub WorkshopWork_HideMe()

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
Range("1254:1275").EntireRow.Hidden = True
ActiveSheet.Shapes("Rectangle: Rounded Corners 111").Visible = True
ActiveSheet.Shapes("Rectangle: Rounded Corners 233").Visible = False
ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
Application.ScreenUpdating = True

End Sub

UNHIDE BUTTON:

Sub WorkshopwnWork_UnhideMe()

Application.ScreenUpdating = False
ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
Range("1254:1275").EntireRow.Hidden = False
ActiveSheet.Shapes("Rectangle: Rounded Corners 111").Visible = False
ActiveSheet.Shapes("Rectangle: Rounded Corners 233").Visible = True

ActiveWindow.ScrollRow = 1254
ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
Application.ScreenUpdating = True

End Sub

Answer 1

Yes, you could do somenthing like this to make rows index dynamic:

Dim startRow As Integer

startRow = 1200
Range(startRow + 54 & ":" & startRow + 75).EntireRow.Hidden = True
Range(startRow + 54 & ":" & startRow + 75).EntireRow.Hidden = False

You can declare your variable as a Public Constant and them use it in all your functions:

'This goes at the start of a Module, outside any Sub or Function
Public Const startRow As Integer = 1200

'Example Button1
Sub mySubButton1()
    Range(startRow + 54 & ":" & startRow + 75).EntireRow.Hidden = True
    Range(startRow + 54 & ":" & startRow + 75).EntireRow.Hidden = False
End Sub

'Example Button2
Sub mySubButton2()
    Range(startRow + 100 & ":" & startRow + 125).EntireRow.Hidden = True
    Range(startRow + 100 & ":" & startRow + 125).EntireRow.Hidden = False
End Sub

Hope this helps.

Answer 2

Try using Application.Caller.

Sub btnS()

Set wsAuth = ThisWorkbook.Worksheets("Data")
    ColumnNr = wsAuth.Buttons(Application.Caller).TopLeftCell.Column
    RowNr = wsAuth.Buttons(Application.Caller).TopLeftCell.Row
    Range(RowNr + 54 & ":" & RowNr + 75).EntireRow.Hidden = True


End Sub

Answer 3

You could try:

Option Explicit


Sub test()

    Dim arr As Variant, i As Long

    arr = Array(1254, 1276, 1299) '<- Create an array with all row you want to change

    For i = LBound(arr) To UBound(arr) '<- loop array

        Call Module1.Hide(arr(i)) '<- Call Hide sub

    Next i

End Sub

Sub Hide(ByVal Value As Long)

    Dim y As Long

    With ThisWorkbook.Worksheets("Sheet1")

         If Value = 1254 Then '<- Check value

            y = 22

        ElseIf Value = 1276 Then
            y = 23

        End If

        .Range(Value & ":" & Value + y).EntireRow.Hidden = True

    End With

End Sub

Answer 4

I'd create and maintain a named range that moves and shakes with your requirement. You only need to include the cells in the first column given you're toggling the display of the entire row.

From there, you can include that range in your code, you can see it in the Range call within the routine below. If you then insert or delete rows, the shape of the named range automatically adjust.

You can also improve your routine to accept a boolean for show and then deal with it from a proxy routine for hide and show.

Sub WorkshopWork_HideMe()
    ToggleHideShow False
End Sub

Sub WorkshopWork_ShowMe()
    ToggleHideShow True
End Sub

Private Sub ToggleHideShow(ByVal bShow As Boolean)
    Application.ScreenUpdating = False

    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"

    Range("ShowHideRange").EntireRow.Hidden = Not bShow

    ActiveSheet.Shapes("Rectangle: Rounded Corners 111").Visible = bShow
    ActiveSheet.Shapes("Rectangle: Rounded Corners 233").Visible = Not bShow

    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"

    Application.ScreenUpdating = True
End Sub

Something to think about anyway.