Xu Hướng 2/2023 # Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản # Top 6 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản # Top 6 View

Bạn đang xem bài viết Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung 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.

Unhide all sheet trong Excel là gì?

Unhide all sheet trong Excel chính là việc người dùng bỏ ẩn toàn bộ các sheet trong Excel. Điều này có nghĩa là khi người dùng đã ẩn đi các sheet trong Excel. Tuy nhiên, đến một lúc nào đó bạn cần hiển thị lại những sheet này.

Hướng dẫn bỏ ẩn toàn bộ các sheet trong Excel

Chúng ta sẽ bỏ qua cách bỏ ẩn cho từng sheet trong Excel một. Bởi vì, cách làm này sẽ ngốn rất nhiều thời gian của bạn. Chúng tôi sẽ mách bạn cách unhide all sheet trong Excel một cách thông minh hơn. Đó là:

Bước 1: Khởi động Excel

Bước 2: Bỏ ẩn toàn bộ sheet trong Excel

Trong giao diện cửa sổ soạn thảo của Excel, bạn nhấn tổ hợp phím Alt + F11. Sau đó, trên thanh công cụ bạn sẽ nhìn thấy mục Insert và hãy kích vào đó. Cuối cùng, trong Insert bạn sẽ chọn vào Module.

Bước 3: Code ẩn toàn bộ sheet trong Excel

” Sub Unhide_AllSheet() ‘Bỏ ẩn tất cả các Sheet

Dim ws As Worksheet On Error Resume Next ‘Bỏ qua các lỗi có thể xảy ra ‘Bỏ ẩn các sheet For Each ws In Sheets ws.Visible=True Next ‘Vô hiệu hóa bất kỳ lỗi nào xảy ra On Error Goto 0

Bước 4: Chạy code

Sau khi bạn copy và paste xong đoạn code trên vào cửa sổ soạn thảo. Lúc này, bạn sẽ tiến hành chạy đoạn code xem đã chính xác chưa bằng 2 cách. Đó là:

Cách 1: Bạn sẽ nhấn F5 để chạy code.

Cách 2: Bạn nhấn vào nút tam giác có màu xanh trên menu của cửa sổ soạn thảo.

Nếu code chính xác, toàn bộ các sheet trong Excel đã được hiển thị lên. Như vậy là bạn đã thành công với cách unhide all sheet trong excel rồi đó.

Hướng dẫn ẩn toàn bộ các sheet trong Excel

Cũng giống với việc bạn unhide all sheet trong Excel. Đối với hành động ẩn đi toàn bộ sheet bạn cũng không nên thực hiện riêng rẽ. Thay vào đó, bạn hãy áp dụng cách mà chúng tôi hướng dẫn như sau:

Đầu tiên, bạn sẽ kích chuột vào 1 sheet nào đó trong Excel. Sau đó, bạn sẽ kích vào phím Ctrl trên bàn phím máy tính. Tại đây, bạn tiếp tục chọn ra những sheet cần ẩn hoặc toàn bộ các sheet.

Sau khi chọn xong các sheet cần ẩn bạn sẽ kích chuột phải rồi chọn hide.

Như vậy, rất nhanh chóng toàn bộ các sheet bạn chọn đã bị ẩn đi. Đối với cách ẩn toàn bộ các sheet trong Excel này đơn giản hơn rất nhiều. Nhất là khi bạn đi ẩn từng sheet một trong Excel vừa lâu lại mất thời gian.

How To Unhide Sheets In Excel: Show Multiple Or All Sheets At Once

How to unhide sheets in Excel

If you want to see just one or two hidden sheets, here’s how you can quickly unhide them:

Note. Excel’s Unhide option only allows you to select one sheet at a time. To unhide multiple sheets, you will have to repeat the above steps for each worksheet individually or you can unhide all sheets in one go by using the below macros.

How to unhide sheets in Excel with VBA

In situations when you have multiple hidden worksheets, unhiding them one-by-one might be very time consuming, especially if you’d like to unhide all the sheets in your workbook. Fortunately, you can automate the process with one of the following macros.

How to unhide all sheets in Excel

This small macro makes all hidden sheets in an active workbook visible at once, without disturbing you with any notifications.

Sub Unhide_All_Sheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub

Show all hidden sheets and display their count

Like the above one, this macro also displays all hidden sheets in a workbook. The difference is that upon completion, it shows a dialog box informing the user how many sheets have been unhidden:

Sub Unhide_All_Sheets_Count() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets" End If End Sub

Unhide multiple sheets that you select

If you’d rather not unhide all worksheets at once, but only those that the user explicitly agrees to make visible, then have the macro ask about each hidden sheet individually, like this:

Sub Unhide_Selected_Sheets() Dim wks As Worksheet Dim MsgResult As VbMsgBoxResult For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetHidden Then MsgResult = MsgBox("Unhide sheet " & chúng tôi & "?", vbYesNo, "Unhiding worksheets") If MsgResult = vbYes Then wks.Visible = xlSheetVisible End If Next End Sub

Unhide worksheets with a specific word in the sheet name

In situations when you only want to unhide sheets containing certain text in the their names, add an IF statement to the macro that will check the name of each hidden worksheet and unhide only those sheets that contain the text you specify.

In this example, we unhide sheets with the word ” report” in the name. The macro will display sheets such as Report, Report 1, July report, and the like.

To unhide worksheets whose names contain some other word, replace ” report” in the following code with your own text.

Sub Unhide_Sheets_Contain() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets with the specified name have been found.", vbOKOnly, "Unhiding worksheets" End If End Sub

How to use the macros to unhide sheets in Excel

To use the macros in your worksheet, you can either copy/paste the code in the Visual Basic Editor or download the workbook with the macros and run them from there.

How to insert the macro in your workbook

You can add any of the above macros to your workbook in this way:

Open the workbook with hidden sheets.

Press Alt + F11 to open the Visual Basic Editor.

Paste the code in the Code window.

Press F5 to run the macro.

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Download the workbook with the macros

Alternatively, you can download our sample workbook to unhide sheets in Excel that contains all of the macros discussed in this tutorial:

Unhide_All_Sheets – unhide all worksheets in an active workbook momentarily and silently.

Unhide_All_Sheets_Count­ – show all hidden sheets along with their count.

Unhide_Selected_Sheets – display hidden sheets you choose to unhide.

Unhide_Sheets_Contain – unhide worksheets whose names contain a specific word or text.

To run the macros in your Excel, you do the following:

Open the downloaded workbook and enable the macros if prompted.

Open your own workbook in which you want to see hidden sheets.

For example, to unhide all sheets in your Excel file and display the hidden sheets count, you run this macro:

How to show hidden sheets in Excel by creating a custom view

So, what we are going to do now is create the Show All Sheets custom view. Here’s how:

That’s it! All hidden sheets will be shown immediately.

Note. This method does not show very hidden sheets. The only way to view such sheets is to unhide them with VBA.

Cannot unhide sheets in Excel – problems and solutions

If you are unable to unhide certain sheets in your Excel, the following troubleshooting tips may shed some light why.

1. The workbook is protected

2. Worksheets are very hidden

If your worksheets are hidden by VBA code that makes them very hidden (assigns the xlSheetVeryHidden property), such worksheets cannot be displayed by using the Unhide command. To unhide very hidden sheets, you need to change the property from xlSheetVeryHidden to xlSheetVisible from within the Visual Basic Editor or run this VBA code.

3. There are no hidden sheets in the workbook

This is how you unhide sheets in Excel. If you are curious to know how to hide or unhide other objects such as rows, columns or formulas, you will find full details in the below articles. I thank you for reading and hope to see you on our blog next week!

Macros to unhide worksheets in Excel

You may also be interested in

Unhiding All Worksheets Within An Excel Workbook

Although you can quickly hide as many worksheets within a workbook as you like, we’re still limited to unhiding individual worksheets one at a time – unless you’re aware of Excel’s Custom Views feature. Fortunately, there is another way to avoid the agony of manually unhiding worksheets one at a time.

In this article I’ll show you how to use a single line of programming code to unhide the worksheets. Programming code in Excel is often referred to as macros. In this case we’re not creating a permanent macro, but rather typing a line of code to run on demand.

Figure 1: There are a variety of ways to hide worksheets in Excel.

Figure 2: Unfortunately, you must unhide worksheets one at a time.

You probably don’t have the time or inclination to unhide more than a couple of worksheets in this fashion, so instead we’ll use a bit of programming code to instantly display all worksheets at once:

As illustrated in Figure 3, press Alt-F11 on your keyboard to display Excel’s Visual Basic Editor. Mac users should press Fn-Alt-F11. Although it looks like a separate program, it’s a hidden aspect of Excel that most users haven’t seen before.

Select Immediate Window from the View menu, or press Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).

At this point the Immediate window will appear on-screen. This is a special area where any programming code you type will be executed immediately, hence the name.

Type the following line of programming code into the Immediate window exactly as written below, and press Enter.

For Each s In Sheets: s.Visible = True: Next

The downside of the Immediate Window is you don’t get any direct feedback if your programming code worked, other than seeing that all of your worksheets are now visible within the workbook. Error prompts will appear if you press Enter when the line of code is either incomplete or contains typographical errors.

You may also encounter an error if the workbook is protected by way of the Protect Workbook command on Excel’s Review menu.

You can safely exit the Visual Basic Editor once you’ve run the line of code.

Figure 3: A single line of code in the Immediate Window will unhide all worksheets in the workbook.

The aforementioned line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you want a little insight as to what the macro is doing:

For Each sets up a loop.

s is a variable that serves as a temporary placeholder for a worksheet to be acted on.

Sheets is a collection of all worksheets within the workbook. This actually includes other types of sheets as well, meaning Chart Sheets and Macro Worksheets. We could be more specific and use the Worksheets collection instead, but Sheets results in less typing.

Each worksheet has a Visible property, and in this case we’re setting it to True. The setting gets set to False when you hide a worksheet.

Next simply instructs Excel to skip to the next worksheet in succession, until all have been processed.

If you were to store this within a formal macro, the code might take this form:

For each s in Sheets

s.Visible

Next

The Immediate Window only allows us to execute a single line of code at a time, so the colons allow us to string three lines of code together into a single line that can be executed.

Unhide All Rows / Columns

This tutorial will demonstrate how to unhide all rows and / or columns in an Excel worksheet using VBA.

Unhide All Rows

To unhide all rows in an Excel sheet, we will set the Hidden Property of all of the rows to FALSE.

We can access all rows by using the EntireRow Property of the Cells Object:

1

Cells

.

EntireRow

.

Hidden

=

False

or by using the EntireRow Property of the Rows Object:

1

Rows

.

EntireRow

.

Hidden

=

False

Unhide All Columns

Similarily, we can unhide all columns in an Excel sheet, by adjusting the Hidden Property of all the Columns.

You can access all of the columns by using the EntireColumn Property of the Cells Object:

1

Cells

.

EntireColumn

.

Hidden

=

False

or by using the EntireColumn Property of the Columns Object:

1

Columns

.

EntireColumn

.

Hidden

=

False

Hide All Rows or Columns

Of course, to hide all rows or columns, just set the Hidden Property to TRUE:

1

Columns

.

EntireColumn

.

Hidden

=

True

Macro to Unhide All Rows and Columns

Use this macro to unhide all rows and columns in a worksheet:

1

2

3

4

Sub

Unhide_All_Rows_Columns

(

)

    

Columns

.

EntireColumn

.

Hidden

=

False

    

Rows

.

EntireRow

.

Hidden

=

False

End

Sub

Macro to Unhide All Rows and Columns on all Sheets

This macro will unhide all rows and columns in all sheets in an Excel workbook:

1

2

3

4

5

6

7

8

Sub

Unhide_All_Rows_Columns_in_Workbook

(

)

    

Dim

ws

As

Worksheet

    

    

For

Each

ws

In

Worksheets

        

Columns

.

EntireColumn

.

Hidden

=

False

        

Rows

.

EntireRow

.

Hidden

=

False

    

Next

ws

End

Sub

Cập nhật thông tin chi tiết về Thủ Thuật Unhide All Sheet Trong Excel Siêu Đơn Giản trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!