How to Unhide All Rows in Excel

1.0 Introduction

Hiding rows in Excel is beneficial for focusing on essential information, concealing sensitive data, and managing large documents. Unhiding all rows and columns is crucial for modifying sheets and understanding dependencies in inherited spreadsheets. This article will cover both options using the IronXL library for productivity and programmatic control.

2.0 Hiding Rows in Excel

Rows can be hidden in Excel using the ribbon button, the right-click menu, or a keyboard shortcut, as with most basic actions in Excel.

To hide rows, start by selecting the rows you want to hide:

• Click on a row's heading to select it.
• Drag the mouse across the row headings to select multiple adjacent rows. Alternatively, select the last row while holding down the Shift key after selecting the first row.
• Hold down the Ctrl key while clicking on the headings of additional rows you want to select when choosing non-contiguous rows by selecting the first row's heading first.

After selecting the rows, choose one of the following options:

2.1 Hiding Rows Using Ribbon Button

It is also possible to hide rows in Excel using the Ribbon button located at the top of the Excel sheet.

Step 1: Click the Format button in the Cells group under the Home tab.

Step 2: Choose Hide Rows from the Hide & Unhide menu under Visibility.

Excel Hide & Unhide

As another option, you can select Home > Format > Row Height and enter 0 in the field for Row Height. In either case, the selected rows will immediately be hidden from view.

2.2 Hiding Rows Using the Right-Click Menu

If you don't want to search for the Hide command on the ribbon, you can access it from the context menu by right-clicking on the column or row header and choosing the Hide option.

Hide in the context menu

2.3 Hiding Rows Using Keyboard Shortcut

A keyboard shortcut can also be used to hide rows in an Excel document by pressing Ctrl + 9 to hide all rows.

3.0 Unhiding Rows in Excel

Hidden rows can be unhidden using the ribbon button, right-click menu, or keyboard shortcut, as with most basic actions in Excel.

3.1 Hide rows using Ribbon Button

Hide rows by using the Ribbon button available at the top of the Excel sheet.

Step 1: Click the Format button in the Cells group under the Home tab.

Step 2: Choose UnHide Rows from the Hide & Unhide menu under Visibility.

Hiding rows using the ribbon

3.2 UnHide rows using the right-click menu

Unhide rows using an option from the context menu by right-clicking the chosen rows and choosing "Unhide".

Unhide rows

3.3 Hide using a Keyboard Shortcut

Keyboard shortcuts can also be used to unhide all hidden rows in an Excel document by pressing Ctrl + Shift + 9, which helps to make hidden rows visible again in the spreadsheet.

4.0 Hiding Rows Using IronXL

Microsoft Excel documents can be read and edited in C# using the IronXL .NET Framework. The standalone .NET software library for Excel can read a variety of spreadsheet formats, and there is no need to install Microsoft Excel or Interop.

The user-friendly C# API IronXL makes it easy to read, modify, and generate Excel spreadsheet files in the .NET environment. IronXL provides full support for .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure. One of the best Excel spreadsheet libraries for C# is IronXL, which is part of the .NET Framework and .NET Core.

4.1 IronXL Features

• It is possible to edit, read, and load data from XLS, XLSX, CSV, and TSV.
• Data export and storage in the formats CSV, TSV, JSON, XLS, and XLSX.
• Ranges are simple to use. Syntax for WorkSheet["A1:B10"]. Ranges can logically be combined.
• Columns, rows, and ranges can all be sorted.
• Cell styles, such as font, size, border, alignment, lock, freeze cells, hide/unhide rows, and number formats.

4.2 Hiding Rows in IronXL

IronXL can be used to hide row numbers in Excel with just a few lines of code. The below code is an example to hide a row in Excel.

using IronXL;

WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetRow(1).Hidden = true;

wb.SaveAs("sample1.xlsx");

using IronXL;

WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetRow(1).Hidden = true;

wb.SaveAs("sample1.xlsx");

Imports IronXL

Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

ws.GetRow(1).Hidden = True

wb.SaveAs("sample1.xlsx")

The example above loads an existing Excel document using the WorkBook.LoadExcel method by providing the path and file name. Once the Excel document is loaded in the WorkBook object, a specific Excel worksheet can be specified using the WorkBook method GetWorkSheet and pass the sheet name as a parameter. This creates a WorkSheet object, which provides functionality for the entire worksheet.

After getting the WorkSheet object, it is possible to select a row using the method GetRow by passing the row index as a parameter. This allows us to use the boolean value from the Hidden property and set it to true, which can hide and unhide rows in an Excel document. The above code is an example that hides the second row of the Excel document.

4.3 UnHide Rows in IronXL

The following code can unhide multiple rows in Excel:

using IronXL;

WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetRow(1).Hidden = false;
wb.SaveAs("sample1.xlsx");

using IronXL;

WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");

ws.GetRow(1).Hidden = false;
wb.SaveAs("sample1.xlsx");

Imports IronXL

Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

ws.GetRow(1).Hidden = False
wb.SaveAs("sample1.xlsx")

To unhide specific rows, the aforementioned steps should be followed and modify the instructions by selecting the row through the method GetRow, either by passing the row index as a parameter or by specifying the row. Then set the boolean value of Hidden to false.

Conclusion

The IronXL library is a development tool that provides all the advanced features required for a complex Excel application. It has an excellent feature that offers both developers and users a free trial, making it easy to evaluate its effectiveness. IronXL is one of the fastest libraries available, and developers can easily learn how to create an Excel document with just a few lines of code. With the library's assistance, It is possible to perform a wide range of Excel functions. To learn more about IronXL, please visit the tutorial page for more examples or refer to this example on how to read an Excel file.