dcsimg
Results 1 to 2 of 2

Thread: Dynamic range for hiding rows

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2018
    Posts
    40

    Dynamic range for hiding rows

    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:

    Code:
        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:

    Code:
    Sub WWork_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
    Sub WWork_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
    Sub WOptions_HideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1276:1298").EntireRow.Hidden = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 112").Visible = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 234").Visible = False
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WOptions_UnhideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1276:1298").EntireRow.Hidden = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 112").Visible = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 234").Visible = True
    
    ActiveWindow.ScrollRow = 1276
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WMTRL_HideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1299:1350").EntireRow.Hidden = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 113").Visible = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 235").Visible = False
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WMTRL_UnhideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1299:1350").EntireRow.Hidden = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 113").Visible = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 235").Visible = True
    
    ActiveWindow.ScrollRow = 1299
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WParts_HideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1351:1402").EntireRow.Hidden = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 118").Visible = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 236").Visible = False
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WParts_UnhideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1351:1402").EntireRow.Hidden = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 118").Visible = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 236").Visible = True
    
    ActiveWindow.ScrollRow = 1351
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WSub_HideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1403:1461").EntireRow.Hidden = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 121").Visible = True
    ActiveSheet.Shapes("Rectangle: Rounded Corners 237").Visible = False
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Sub WSub_UnhideMe()
    
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets("Price calculation").Unprotect Password:="123"
    Range("1403:1461").EntireRow.Hidden = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 121").Visible = False
    ActiveSheet.Shapes("Rectangle: Rounded Corners 237").Visible = True
    
    ActiveWindow.ScrollRow = 1403
    ThisWorkbook.Sheets("Price calculation").Protect Password:="123"
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by mrwad; Apr 18th, 2019 at 06:51 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,438

    Re: Dynamic range for hiding rows

    Then +23 instead of 1276:1298 etc.?
    you can try like

    Code:
    Rows("1276").resize(23)
    you could also make all the ranges to named ranges using the button caption for the name, when you add the button
    alternatively /additionally you could use the buttons as toggles, then only require the number of buttons ( and code)
    there could also be the option to add all the buttons to a collection, so that only a single class event code would be required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width