Option Button that shows/hide certain cells

**DoubLeA** · 05-17-2012, 12:37 PM

Hey-

I need to create 2 option buttons one that shows cells F3 and G3 all the way down and then another one that hides F3 and G3 and down?

Coding suggestions?

**Pulsus** · 05-17-2012, 03:04 PM

DoubleA,

Considering that is not possible to Hide a cells range, i just make the trick code above that paint the text in white, so the text become "invisible", and the other button that paint the text in black.

Hope that can help:

Sub Hide()

ActiveSheet.Range("F3:G3", ActiveSheet.Range("F3:G3").End(xlDown)).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With

ActiveSheet.Range("A2").Select

End Sub

Sub UnHide()

ActiveSheet.Range("F3:G3", ActiveSheet.Range("F3:G3").End(xlDown)).Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With

ActiveSheet.Range("A2").Select

End Sub

**Pichingualas** · 05-17-2012, 03:16 PM

Like this:

Sub Hide()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Rng As String

Set Ws1 = ActiveSheet
Set Ws2 = Sheets("Hidden Sheet")

Rng = Ws1.Range("F3:G3", Ws1.Range("F3:G3").End(xlDown)).Address

Ws2.Range(Rng).Value = Ws1.Range(Rng).Value

Ws1.Range(Rng).ClearContents
End Sub

Sub UnHide()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Rng As String

Set Ws1 = ActiveSheet
Set Ws2 = Sheets("Hidden Sheet")

Rng = Ws2.Range("F3:G3", Ws2.Range("F3:G3").End(xlDown)).Address

Ws1.Range(Rng).Value = Ws2.Range(Rng).Value

Ws2.Range(Rng).ClearContents
End Sub

Mind you, you would need to add a sheet with the name "Hidden Sheet" and hide it.

**Pichingualas** · 05-17-2012, 03:09 PM

Another way would be to move the contents of the cells to a hidden sheet and then moving them back with the other button.

**Pichingualas** · 05-17-2012, 03:31 PM

Small change in case you have empty cells in the middle:

Sub Hide()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Rng As String
Dim n As Long

Set Ws1 = ActiveSheet
Set Ws2 = Sheets("Hidden Sheet")
n = Ws1.Range("F" & Ws1.Rows.Count).End(xlUp).Row
If n < Ws1.Range("G" & Ws1.Rows.Count).End(xlUp).Row Then
    n = Ws1.Range("G" & Ws1.Rows.Count).End(xlUp).Row
End If

Rng = Ws1.Range("F3:G3", Ws1.Range("G" & n)).Address

Ws2.Range(Rng).Value = Ws1.Range(Rng).Value

Ws1.Range(Rng).ClearContents
End Sub

Sub UnHide()
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Rng As String
Dim n As Long

Set Ws1 = ActiveSheet
Set Ws2 = Sheets("Hidden Sheet")
n = Ws2.Range("F" & Ws2.Rows.Count).End(xlUp).Row
If n < Ws2.Range("G" & Ws2.Rows.Count).End(xlUp).Row Then
    n = Ws2.Range("G" & Ws2.Rows.Count).End(xlUp).Row
End If

Rng = Ws2.Range("F3:G3", Ws2.Range("G" & n)).Address

Ws1.Range(Rng).Value = Ws2.Range(Rng).Value

Ws2.Range(Rng).ClearContents
End Sub