vba 使用按钮隐藏/取消隐藏excel中的单元格组

Hide/Unhide groups of cells in excel with Button

提问by anand

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'.

我有一组单元格 B32 到 R32 长度和 B32 到 B51 宽度。我想在打开工作表时在开始时隐藏此块。我将此块命名为“分析”。

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.

工作表中有一个按钮。当按下按钮时，我想取消隐藏该块。我是 Excel Vba 的新手。我想知道执行此操作的语法/代码。

Thanks in advance.

提前致谢。

Ananda

阿难

回答by

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

你不能像 MattCrum 提到的那样隐藏一个区域。
就我而言，您有 3 个选择

---

---

现在，只要确保你有什么（数据-不空单元格），在Range 32:51Range 32:51和你的主片要么叫Sheet1Sheet1或更改Sheet1Sheet1的代码，以满足您的工作表名称





1）VBE ( Visual Basic Editor )VBE ( Visual Basic Editor )双击ThisWorkbookThisWorkbook在project explorerproject explorer此代码并粘贴

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

Right click on the folder Modulesand Inserta new Module, then paste this code

右键单击文件夹Modules和Insert一个新的Module，然后粘贴此代码

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 unhideto it.

现在，在电子表格上添加一个按钮，右键单击并分配对其调用的宏unhide。

Save changes and save your workbook as *.xlsm file

保存更改并将您的工作簿另存为 *.xlsm 文件

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

请注意，当您现在打开工作簿时，第 32 到 51 行被隐藏。单击该按钮将取消隐藏它们。

---

---

2）您可以将内容的字体颜色更改为白色"hide""hide"。

Follow step 1, and replace

按照步骤 1，并更换

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 unhidesubroutine )with

和Module（unhide子程序）中的代码与

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

现在，除了您是"hiding"（更改）字体颜色而不是隐藏行之外，一切都与步骤 1 类似。不是一个好方法，但如果它对你有用，那么很酷

---

---

3）按照步骤1和更换下的代码ThisWorkbookThisWorkbook与

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 Module1with

而在代码Module1与

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