CODEWITHSUNDEEP
excelexcel-2010vba
anand
anand

Reputation: 493

Hide/Unhide groups of cells in excel with Button

I have a group of cells B32 till R32 length and B32 to B51 breadth. I want to keep this block hidden at the start when the sheet is opened. I have named this block as 'Analysis'.

There is a button in the sheet. When the button is pressed, I want to unhide that block. I am new to Excel Vba. I would like to know the syntax/code for doing this operation.

Thanks in advance.

Ananda

Upvotes: 0

Views: 15938

Answers (1)

user2140173
user2140173

Reputation:

You cant just hide an area like MattCrum has mentioned.
You have 3 choices as far as I am concerned

Now, just make sure you have something(data - not empty cells) in the Range 32:51 and your main sheet is either called Sheet1 or change Sheet1 in the code to suit your worksheets name

1) in VBE ( Visual Basic Editor ) double click ThisWorkbook in the project explorer and paste this code

Private Sub Workbook_Open()
    ThisWorkbook.Sheets("Sheet1").Rows(32 & ":" & 51).hidden = True
End Sub

Right click on the folder Modules and Insert a new Module, then paste this code

Sub unhide()
    ThisWorkbook.Sheets("Sheet1").Rows(32 & ":" & 51).hidden = False
End Sub

Now, add a button on the spreadsheet, right click and assign macro called unhide to it.

Save changes and save your workbook as *.xlsm file

Notice when you open the workbook now, rows 32 to 51 are hidden. Clicking the button will unhide them.

2) You can change the font color to white to "hide" the contents.

Follow step 1, and replace

ThisWorkbook.Sheets("Sheet1").Rows(32 & ":" & 51).hidden = True

with this 

ThisWorkbook.Sheets("Sheet1").Range("B32:R51").Font.Color = RGB(255, 255, 255)

and the code in the Module ( the unhide subroutine )with 

ThisWorkbook.Sheets("Sheet1").Range("B32:R51").Font.Color = RGB(0, 0, 0)

Now, everything works similar to step 1 except your are "hiding"(changing) the font color instead of hiding rows. Not a great approach, but if it works for you then cool

3) Follow step 1 and replace the code under ThisWorkbook with

Option Explicit

Private Sub Workbook_Open()

    Application.ScreenUpdating = False

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "hiddenSheet"
    Set hs = ThisWorkbook.Sheets(Worksheets.Count)
    hs.Visible = xlSheetHidden

    ws.Range("B32:R51").Select
    Selection.Copy

    With hs
        .Activate
        .Range("B32").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
    End With

    ws.Activate
    ws.Rows(32 & ":" & 51).Delete

    Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call deleteHidden(Worksheets.Count)
End Sub

And the code in the Module1 with

Option Explicit
Public ws As Worksheet, hs As Worksheet

Sub unhide()
    With hs
        .Activate
        .Rows("32:51").Select
        Selection.Copy
    End With
    With ws
        .Activate
        .Rows("32:32").Select
        Selection.Insert Shift:=xlDown
    End With
End Sub

Sub deleteHidden(num&)
    Application.DisplayAlerts = False
    Worksheets(num).Delete
    Application.DisplayAlerts = True
    Set hs = Nothing
End Sub

Upvotes: 2

Related Questions