Cập nhật nội dung chi tiết về How To Hide And Unhide Worksheets In Excel mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
What to Know
This article explains how to hide and unhide worksheets using the contextual menu and the ribbon in Excel for Microsoft 365, Excel 2019, 2016, 2013, and 2010.
Data Use in Hidden Worksheets
By default, all open Excel workbooks display worksheet tabs on the taskbar at the bottom of the screen, but you can hide or display them as needed. At least one worksheet must be visible at all times.
Hiding worksheets doesn’t mean you’re deleting them, and you can still reference them in formulas and charts located on other worksheets or other workbooks.
Hide Worksheets Using the Contextual Menu
If the Hide option is inactive or grayed out, most likely, the current workbook has only one worksheet. Excel deactivates the Hide option for single-sheet workbooks because there must always be at least one visible sheet.
How to Hide a Single Worksheet
How to Hide Multiple Worksheets
Press and hold down the Ctrl key on the keyboard.
Hide Worksheets Using the Ribbon
Excel has no keyboard shortcut for hiding worksheets, but you can use the ribbon bar to accomplish the same task.
Select one or more worksheet tabs at the bottom of an Excel file.
Select Format in the Cells group.
Select Hide Sheet.
Unhide Worksheets Using the Contextual Menu
You can unhide tabs using the contextual menu, just as you can hide them.
Unhide Worksheets Using the Ribbon
As with hiding worksheets, Excel has no keyboard shortcut for unhiding a sheet, but you can still use the ribbon.
Select one or more worksheet tabs at the bottom of the Excel file.
Select Format.
Select Unhide Sheet.
How To Unhide All Worksheets &Amp; Sheets In Excel?
How to unhide all worksheets & sheets in Excel?
This article provides several methods to unhide all hidden sheets & worksheets in Excel step by step.
Unhide all hidden worksheets one by one in Excel
Unhide all hidden worksheets by VBA code
Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
Unhide all very hidden worksheets by Kutools for Excel
Unhide all hidden worksheets one by one in Excel
We can apply the Unhide Sheet feature to unhide a hidden worksheet at a time in Excel. Please do as follows:
3. Then the selected hidden sheet is displayed. Repeat above Step 2 to show all unhide worksheets one by one.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Unhide all hidden worksheets by VBA code
The following short VBA code also can help you display all of the hidden sheets at the same time.
1. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
Sub UnhideAllSheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub3. Press the F5 key to run this macro. And the hidden sheets will be displayed at once.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Then all hidden sheets are shown at once. See screenshot:
Unhide all hidden worksheets by Toggle Hidden Worksheets Visibility feature
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Unhide all very hidden worksheets by Kutools for Excel
Sometimes above methods cannot unhide the hidden worksheets. That’s because these worksheets are very hidden. In this situation, you can apply the Hide/Unhide Workbooks and Sheets feature of Kutools for Excel to quickly unhide them.
Kutools for Excel – Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!
Now all hidden worksheets including the very hidden ones are displaying in bulk. Please close the dialog as you need.
Hide/Unhide Workbooks and Sheets hide or unhide multiple opening workbooks and their worksheets in bulk, including the very hidden ones. Have a Free Trial!
Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days.Download and Free Trial Now!
Related articles
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Delete Or Hide Objects/Controls On A Worksheet
Delete or Hide Objects/Controls on a worksheet
Shapes collection
Members of the Shapes collection are:
1. ActiveX controls (Control Toolbox) or a linked or embedded OLE objects2. Controls from the Forms toolbar3. Controls from the Drawing toolbar4. Pictures, charts, ………………
You see that all objects/controls are a member of the Shapes collection.
Below you find examples to delete or hide the members of this collection.
Tip: if you only want to hide all shapes for a moment then you can use the toggle shortcut Ctrl 6 (This is for the whole workbook)
Manual delete shapes
Note: for Activex(control toolbox) controls you must be in “Design Mode” in Excel 97-2003. Use the first button on the Control toolbox toolbar to toggle this mode.
With VBA code
Delete all shapes
Use this macro to delete all shapes on the worksheet, working in all Excel versions(also in 2007).
Sub Shapes1() 'Delete all Objects except Comments On Error Resume Next ActiveSheet.DrawingObjects.Visible = True ActiveSheet.DrawingObjects.Delete On Error GoTo 0 End Sub Sub Comments() ActiveSheet.Cells.ClearComments End Sub Sub NotUseThisMacro() 'Delete every shape in the Shapes collection Dim myshape As Shape For Each myshape In ActiveSheet.Shapes myshape.Delete Next myshape End SubDelete only specific shapes
What if you only want to delete control toolbox controls, Pictures or forms controls.You can loop through the collection and check the Type of the control.
12 = ActiveX control (control toolbox) or a linked or embedded OLE object.13 = Picture 8 = Forms controls
For Type 8 we use another macro to avoid the problem of losing AutoFilter and Data Validation dropdowns on your chúng tôi the example in this section “Delete only Forms controls”
Sub Shapes2() 'Loop through the Shapes collection and use the Type number of the control Dim myshape As Shape For Each myshape In ActiveSheet.Shapes ' ActiveX control (control toolbox) or a linked or embedded OLE object If chúng tôi = 12 Then myshape.Delete ' You can also use myshape.Visible = False Next myshape End SubIf you want to know all the Type numbers of all controls on your worksheet you can run this macro to add a new worksheet with the names and Type numbers of all objects on your worksheet.You can find the number then that you must use in the code to delete the objects you want.
Sub ListAllObjectsActiveSheet() Dim NewSheet As Worksheet Dim MySheet As Worksheet Dim myshape As Shape Dim I As Long Set MySheet = ActiveSheet Set NewSheet = Worksheets.Add With NewSheet .Range("A1").Value = "Name" .Range("B1").Value = "Visible(-1) or Not Visible(0)" .Range("C1").Value = "Shape type" I = 2 For Each myshape In MySheet.Shapes .Cells(I, 1).Value = myshape.Name .Cells(I, 2).Value = myshape.Visible .Cells(I, 3).Value = myshape.Type I = I + 1 Next myshape .Range("A1:C1").Font.Bold = True .Columns.AutoFit .Range("A1:C" & Rows.Count).Sort Key1:=Range("C1"), _ Order1:=xlAscending, Header:=xlYes End With End SubDelete only Forms controls
This example avoid the problem of losing AutoFilter and Data Validation dropdowns on your worksheet when you use Type 8.
Sub Shapes4() 'Dave Peterson and Bob Phillips 'Example only for the Forms controls Dim shp As Shape Dim testStr As String For Each shp In ActiveSheet.Shapes If chúng tôi = 8 Then If shp.FormControlType = 2 Then testStr = "" On Error Resume Next testStr = shp.TopLeftCell.Address On Error GoTo 0 Else shp.Delete End If End If Next shp End SubIn the workaround macro above we use FormControlType = 2 in the loop (xlDropDown). AutoFilter and Data Validation dropdowns do not have TopLeftCell.Address and the macro will not delete this DropDowns.
Other FormControl constants are:(only for the Forms controls)
xlButtonControl = 0xlCheckBox = 1xlDropDown = 2xlEditBox = 3 xlGroupBox = 4xlLabel = 5xlListBox = 6xlOptionButton = 7 xlScrollBar = 8xlSpinner = 9
Delete or Hide one shapeBecause all objects/controls are a member of the shapes collection we can use this to delete or hide one button, picture or ?
Sub Delete_One_Shape() ActiveSheet.Shapes("YourShapeName").Delete End Sub Sub Hide_One_Shape() ActiveSheet.Shapes("YourShapeName").Visible = False End SubSpecific examples for Activex(control toolbox) or Forms controls
For most things the macros in the first section of this page are Ok but if you only want to delete Forms buttons or ActiveX buttons then look here for a few examples.
ActiveX controls (Control Toolbox) or linked or embedded OLE objects
Sub OLEObjects1() 'Hide all ActiveX controls(Control Toolbox)or linked or embedded OLE objects On Error Resume Next ActiveSheet.OLEObjects.Visible = False On Error GoTo 0 End Sub Sub OLEObjects2() 'Delete all ActiveX controls(Control Toolbox)or linked or embedded OLE objects On Error Resume Next ActiveSheet.OLEObjects.Visible = True ActiveSheet.OLEObjects.Delete On Error GoTo 0 End Sub Sub OLEObjects3() 'Delete/hide only all CommandButtons from the Control Toolbox Dim obj As OLEObject For Each obj In ActiveSheet.OLEObjects If TypeOf obj.Object Is MSForms.CommandButton Then obj.Delete ' or obj.Visible = False if you want to hide them End If Next End SubOthers are :
MSForms.CheckBox MSForms.TextBox MSForms.OptionButtonMSForms.ListBoxMSForms.ComboBox MSForms.ToggleButtonMSForms.SpinButton MSForms.ScrollBar MSForms.LabelMSForms.Image
Sub OLEObjects4() 'Hide one ActiveX control(Control Toolbox)or a linked or embedded OLE object ActiveSheet.OLEObjects("CommandButton1").Visible = False End Sub Sub OLEObjects5() 'Delete one ActiveX control(Control Toolbox)or a linked or embedded OLE object ActiveSheet.OLEObjects("CommandButton1").Delete End SubBecause Control Toolbox controls are also a member of the Shapes collection you can also use this :
Sub OLEObjects6() 'Hide one Control Toolbox button or Control ActiveSheet.Shapes("CommandButton1").Visible = False End Sub Sub OLEObjects7() 'Delete one Control Toolbox button or Control ActiveSheet.Shapes("CommandButton1").Delete End SubTo clear textboxes or uncheck checkboxes you can use code like this :
Sub TestMe() Dim obj As OLEObject For Each obj In ActiveSheet.OLEObjects If TypeOf obj.Object Is MSForms.TextBox Then chúng tôi = "" End If If TypeOf obj.Object Is MSForms.CheckBox Then obj.Object.Value = False End If Next End SubForms controls
Sub Forms1() 'Delete All Forms buttons ActiveSheet.Buttons.Delete End Sub Sub Forms2() 'Hide All Forms buttons ActiveSheet.Buttons.Visible = False End Sub Sub Forms3() 'Delete one Forms button ActiveSheet.Buttons("Button 1").Delete End Sub Sub Forms4() 'Hide one Forms button ActiveSheet.Buttons("Button 1").Visible = False End SubInstead of Buttons you can also use
OptionButtonsCheckBoxes DropDowns
Because Forms controls are also a member of the Shapes collection you can also use this
Sub Forms5() 'One Forms button or Control ActiveSheet.Shapes("Button 1").Delete End Sub Sub Forms6() 'One Forms button or Control ActiveSheet.Shapes("Button 1").Visible = False End SubHow To Delete Hidden Worksheets In Excel?
How to delete hidden worksheets in Excel?
Perhaps you have a workbook contains many hidden worksheets which aren’t useful, and they take up so many bits, now you want to delete all of the trashy hidden worksheets. How could you quickly delete them?
Delete hidden worksheets with VBA code Delete hidden worksheets with Inspect Document function Delete hidden worksheets with Kutools for Excel
Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Delete hidden worksheets with VBA code
The following VBA code will delete all hidden worksheets within a workbook.
1. Open the workbook you want to delete the hidden worksheet.
Delete hidden worksheets with Inspect Document function
With the Inspect Document function of Excel, you can delete the hidden worksheets quickly and easily.
Delete hidden worksheets with Kutools for Excel
If you want to delete the hidden worksheets quickly, you can use Kutools for Excel.
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.
After installing Kutools for Excel, you can do as follows:
2. Then a prompt box pops out to remind you if you really want to delete all hidden worksheets. See screenshot:
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Bạn đang đọc nội dung bài viết How To Hide And Unhide Worksheets In Excel trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!