USING IRONXL

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.

How to Unhide All Rows in Excel, Figure 1: Excel Hide & Unhide 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.

How to Unhide All Rows in Excel, Figure 2: Hide in the context menu 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 Unhiding Rows Using Ribbon Button

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

How to Unhide All Rows in Excel, Figure 3: Unhiding rows using the ribbon Unhiding rows using the ribbon

3.2 Unhiding Rows Using the Right-Click Menu

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

How to Unhide All Rows in Excel, Figure 4: Unhide rows Unhide rows

3.3 Unhiding 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;

// Load an existing Excel workbook
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");

// Select the worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Hide the second row
ws.GetRow(1).Hidden = true;

// Save changes to the Excel file
wb.SaveAs("sample1.xlsx");
using IronXL;

// Load an existing Excel workbook
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");

// Select the worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Hide the second row
ws.GetRow(1).Hidden = true;

// Save changes to the Excel file
wb.SaveAs("sample1.xlsx");
Imports IronXL

' Load an existing Excel workbook
Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")

' Select the worksheet
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

' Hide the second row
ws.GetRow(1).Hidden = True

' Save changes to the Excel file
wb.SaveAs("sample1.xlsx")
$vbLabelText   $csharpLabel

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 Unhiding Rows in IronXL

The following code can unhide multiple rows in Excel:

using IronXL;

// Load an existing Excel workbook
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");

// Select the worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Unhide the second row
ws.GetRow(1).Hidden = false;

// Save changes to the Excel file
wb.SaveAs("sample1.xlsx");
using IronXL;

// Load an existing Excel workbook
WorkBook wb = WorkBook.LoadExcel("sample1.xlsx");

// Select the worksheet
WorkSheet ws = wb.GetWorkSheet("Sheet1");

// Unhide the second row
ws.GetRow(1).Hidden = false;

// Save changes to the Excel file
wb.SaveAs("sample1.xlsx");
Imports IronXL

' Load an existing Excel workbook
Private wb As WorkBook = WorkBook.LoadExcel("sample1.xlsx")

' Select the worksheet
Private ws As WorkSheet = wb.GetWorkSheet("Sheet1")

' Unhide the second row
ws.GetRow(1).Hidden = False

' Save changes to the Excel file
wb.SaveAs("sample1.xlsx")
$vbLabelText   $csharpLabel

To unhide specific rows, follow the aforementioned steps 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.

Frequently Asked Questions

How can I unhide all rows in a spreadsheet using the Ribbon button?

To unhide all rows using the Ribbon button, click the Format button in the Cells group under the Home tab, then choose Unhide Rows from the Hide & Unhide menu under Visibility. Alternatively, this can be automated using IronXL for programmatic control.

What is the keyboard shortcut to unhide rows in a spreadsheet?

The keyboard shortcut to unhide all hidden rows in a spreadsheet is Ctrl + Shift + 9. For automated solutions, IronXL can be used.

Can I unhide rows using the right-click menu in a spreadsheet?

Yes, you can unhide rows by right-clicking the chosen rows and selecting the 'Unhide' option from the context menu. IronXL provides similar functionality programmatically.

How do I hide rows in a spreadsheet using the Ribbon button?

To hide rows using the Ribbon button, click the Format button in the Cells group under the Home tab, then choose Hide Rows from the Hide & Unhide menu under Visibility. This can also be accomplished using IronXL for enhanced flexibility.

What capabilities does a specific .NET library offer for handling spreadsheets?

IronXL is a .NET software library that allows for reading, editing, and generating Excel spreadsheet files. It supports various spreadsheet formats, provides a user-friendly C# API, and does not require installing Microsoft Excel.

How can I programmatically hide a row in a spreadsheet?

To hide a row using IronXL, load the Excel workbook with WorkBook.LoadExcel, select the worksheet with GetWorkSheet, and set the Hidden property of the row to true using GetRow.

How do I programmatically unhide a row in a spreadsheet?

To unhide a row using IronXL, load the Excel workbook with WorkBook.LoadExcel, select the worksheet with GetWorkSheet, and set the Hidden property of the row to false using GetRow.

What are some features of a specific .NET library for spreadsheets?

IronXL allows editing, reading, and loading data from XLS, XLSX, CSV, and TSV formats. It supports data export, range handling, sorting, and styling of cells, among other features.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
< PREVIOUS
How to Export CSV and Excel in Razor
NEXT >
How to Export to Excel in ASP.NET Core