VB code to hie and unhide rows or column, to be used for command button

  • Thread starter Thread starter AA Arens
  • Start date Start date
A

AA Arens

Anybody know the VB code added to a (toggle) command button that hides
and unhides a selection of rows or columns?

Bart
 
Bart

To hide:

selection.entirerow.hidden = true
selection.entirecolumn.hidden = true

To toggle:

selection.entirerow.hidden = not selection.entirerow.hidden
selection.entirecolumn.hidden = not selection.entirecolumn.hidden


I think you'll need two buttons, one to hide rows and one for columns

Regards

Trevor
 
Bart

To hide:

selection.entirerow.hidden = true
selection.entirecolumn.hidden = true

To toggle:

selection.entirerow.hidden = not selection.entirerow.hidden
selection.entirecolumn.hidden = not selection.entirecolumn.hidden
Click to expand...
Thank you Trevor.

Just after I dropped this question, I thought, could I put a fixed
selection of rows to be hided or unhided.
Example row 2-5, 20-24 etc.

Thank you.

Bart
 
hi.
yes. and you can also make it boolen in a way.
cb1 = alias for commandbutton1. just change button name. saves a lot of
typing.
Private Sub cb1_Click()
If Rows("20:24").Hidden = True Then
Rows("20:24").Hidden = False
cb1.BackColor = RGB(0, 0, 255) 'Change button color
cb1.Caption = "Hide" 'change button caption
Else
If Rows("20:24").Hidden = False Then
Rows("20:24").Hidden = True
cb1.BackColor = RGB(245, 30, 5)
cb1.Caption = "UnHide"
End If
End If
End Sub

regards
FSt1
 
selection of rows to be hided or unhided.
This idea toggles the rows Hidden / Not Hidden

Sub Demo()
With Range("2:5,20:24").EntireRow
.Hidden = Not .Hidden
End With
End Sub
 
Greaat solution, the effect is very nice.

I used:

With
Range("4:34,36:64,66:96,98:127,129:159,161:190,192:222,224:254,256:285,287:317,319:348,350:380").EntireRow
.Hidden = Not .Hidden
End With

But how to implement the button color and capture change of FSt1?

Bart
 
Hi. Just throwing out another idea.

Sub Demo2()
With Range("4:380").EntireRow
.Hidden = Not .Hidden
End With
'But these are always visible...
Range("A35,A65,A97,A128,A160,A191,A223,A255,A286,A318,A349"). _
EntireRow.Hidden = False
End Sub

Not sure about your color question though.
 
hi,
references to the button have to be outside of the with clause because the
with range clasue references a range. to reference the button, start another
with clause. and you will need an if statement to test if the rows are hidden
or not.
Private Sub CB5_Click()
If Rows("21:22").EntireRow.Hidden = True Then
With CB5
.BackColor = RGB(0, 0, 255)
.Caption = "Hide"
.ForeColor = RGB(245, 245, 6)
End With
Else
With CB5
.BackColor = RGB(244, 7, 6)
.Caption = "UnHide"
.ForeColor = RGB(1, 1, 1)
End With
End If
With Range("21:22").EntireRow
.Hidden = Not .Hidden
End With
End Sub
 
Back
Top