Ruby & Excel: Hiding and Unhiding Columns, Rows, and Worksheets

Occasionally, when working with Excel, you may have a need to hide certain columns or rows in a worksheet. As an example, perhaps your worksheet lists revenue for each of 12 months, but your intended recipient only wants to see the columns showing year-to-date totals. Whatever the reason, you'd like to simply hide certain columns (or rows) rather than delete them completely. And since you're here, you'd probably prefer to do that with Ruby code...

Let's set the scene by assuming that your code is working with an open workbook and you want to hide columns in the currently selected worksheet:

require 'win32ole'
xl = WIN32OLE.connect('Excel.Application')
wb = xl.ActiveWorkbook
ws = xl.ActiveSheet

Hiding Columns

To hide one or more columns, obtain a reference to the column range(s) and set the Hidden property to true:

ws.Columns('A').Hidden = true
ws.Columns('D:K').Hidden = true

Unhiding (displaying) columns works the same way; except, of course, you set the Hidden property to false:

ws.Columns('A').Hidden = false
ws.Columns('D:K').Hidden = false

The following code ensures that all columns in a worksheet are visible:

ws.Columns.Hidden = false

Hiding Rows

Rows have a Hidden property, too, so you can do this to hide rows...

ws.Rows('3').Hidden = true
ws.Rows('7:11').Hidden = true

...and to unhide them:

ws.Rows('3').Hidden = false
ws.Rows('7:11').Hidden = false

Hiding Worksheets

But what if you want to hide an entire worksheet? The Worksheet object doesn't have a Hidden property. Instead, you'll use the Visible property. So to hide the 'Great Amish Scientists' worksheet in the active workbook (wb), we'd use this code:

wb.Worksheets('Great Amish Scientists').Visible = false

And to unhide it, we'd toggle the Visible property back to true:

wb.Worksheets('Great Amish Scientists').Visible = true

The following code ensures that all worksheets in a workbook are visible:

wb.Worksheets.each do |ws|
    ws.Visible = true
end

And that, folks, is pretty much all there is to it. Let me know if you have any questions or comments, and thanks for stopping by!