VBA code for hide/unhide separate rows

[mare23]

Hi everyone!

Is there any VBA code for hide/unhide separate rows. for example
When I click - button once hide all rows from 23-31 and when I click the - button a second time hide rows 14-22
When I click + button once unhide all rows from 14 -22 and when I click the + button a second time hide rows 23-31


Thx
Mare

 

[GWteB]

Hi Mare23, welcome to Mr.Excel!

The code below does as required. Put it in the module of the worksheet on which the code is supposed to act on.

Option Explicit

Private bToggleMINUS as Boolean
Private bTogglePLUS  as Boolean

Public Sub Btn_MINUS()
    If Not bToggleMINUS Then
        Me.Rows("23:31").Hidden = True
    Else
        Me.Rows("14:22").Hidden = True
    End If
    bToggleMINUS = Not bToggleMINUS
End Sub

Public Sub Btn_PLUS()
    If Not bTogglePLUS Then
        Me.Rows("14:22").Hidden = False
    Else
        Me.Rows("23:31").Hidden = False
    End If
    bTogglePLUS = Not bTogglePLUS
End Sub

 

[mare23]

Hi GWteB, thank you for your help. I'm new here and I don't know VBA so well. I tried to insert this code but it doesn't work. How can I send an excel example?

When I clicked on minus icon the model1 opened, when I enter this code it reports errors

 

[GWteB]

Not sure what you did but I could have explained it better. Here we go ...

1. Right click on the sheet tab upon which your buttons are and click on View Code ...

2. The Visual Basic Editor (VBE) opens with a pane of a module in which the code is to be put.
The name of the current module apears on the title bar of the VBE, eg [Sheet1 (Code)].
To switch between different code modules press Ctrl R to open Project Explorer's pane.
For this moment stay in Sheet1's module and paste the code I posted ...

3. Switch to Excel to your worksheet and right click your button or shape and click Assign Macro ...

4. Choose the desired macro for that particular button and confirm pressing OK. Repeat the latter regarding the other button.

Hope I've put you on the right track. If not, let me know.

 

[mare23]

ars on the title bar of the VBE,

Great, we moved one step. Next problem is when I click MINUS button it hides only "23:31" and second time when I click MINUS button nothing happenes or when I click PLUS button also nothing happens.

 

[GWteB]

It works for me so I've no idea why it doesn't work for you...
Did you perform the steps exactly as described? If so, it should work.

 

[Fluff]

A problem with using public variables for something like this, is they get destroyed when you close the workbook.
Another option

Sub HideRws()
   If Rows("23:31").Hidden Then
      Rows("14:22").Hidden = True
   Else
      Rows("23:31").Hidden = True
   End If
End Sub
Sub UnHideRws()
   If Rows("14:22").Hidden Then
      Rows("14:22").Hidden = False
   Else
      Rows("23:31").Hidden = False
   End If
End Sub

 

[mare23]

Yeeeeaaah!!! That's it! Great... I'm so happy... thank you, thank you, thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[GWteB]

A problem with using public variables for something like this, is they get destroyed when you close the workbook.

Have never realized that, thanks for pointing that out !!