How exactly does Excel "hide" columns?

[tdsg]

Can anyone confirm how Excel (2004 for Mac; Version 11.3.7) actually
hides columns and rows?
1. I suspect, but cannot confirm that it does so by reducing their
width (columns) or their height (rows) to zero.
2. By whatever means it does so, is it possible to access the "Hide"
function by straightforward programming, such as "conditional
formatting" or something similar?

 

[PhilD]

1. I suspect, but cannot confirm that it does so by reducing their
width (columns) or their height (rows) to zero.

That looks about right, in that you can "hide" a row or column by
making it zero height/width. It just doesn't bother to display
whatever you've hidden (but of course it is still there functionally,
so you can hide sensitive formulae if necessary).

As regards the rest of your question, an expert will come along
shortly... (I hope!)

PhilD

 

[JE McGimpsey]

Can anyone confirm how Excel (2004 for Mac; Version 11.3.7) actually
hides columns and rows?
1. I suspect, but cannot confirm that it does so by reducing their
width (columns) or their height (rows) to zero.

In the Excel Document Object Model (DOM), setting a range's Hidden
property (e.g, in VBA: Columns(1).Hidden = True) produces the same
result as setting the range's ColumnWidth property to 0.

By whatever means it does so, is it possible to access the "Hide"
function by straightforward programming, such as "conditional
formatting" or something similar?

Formatting, including conditional formatting, cannot change environment
variables like Height and Width.

It's easy, though to use a Event macro to change those settings. For
instance, if you want to hide a row if the value in column A is zero,
put this in your Worksheet code module (CTRL-click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Application.ScreenUpdating = False
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsNumeric(.Value) Then .EntireRow.Hidden = .Value = 0
End With
Next rCell
Application.ScreenUpdating = True
End Sub