Question on Hide/Unhide cells

[Thulasiram]

Hello people,

I would like to know the VBA code that should written in the commnad
button (Hide/Unhide) placed in the Excel worksheet to hide/unhide the
rows/columns that are "entirely empty". i.e, if the row/column is empty
and hide button is clicked, then the empty rows and columns that are
entirely empty have to be hidden.

I would like to hide all empty columns and all empty rows (not
individual cells). Please help me regarding this.

Most importantly, please suggest a good book worth buying that will
deal with basics of VBA and also power programming in VBA. I would like
to know all basic stuff about VBA related to hide/unhide cells,
mulitply matrices, transfer contents in row/column from one worksheet
to another worksheet, diagonalize the matrix, freeze/unfreeze panes,
sum columns/rows and many more.

This might be a question that someone would have asked before. I am new
to this group and I am unable to keep track of the previous posts.

Please help.

Thanks,
Thulasiram

 

[Norman Jones]

Hi Thulasiram,

Try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim col As Range
Dim rw As Range

Set SH = ActiveSheet

For Each col In SH.UsedRange.Columns
col.Hidden = Application.CountA(col) = 0
Next col

For Each rw In SH.UsedRange.Rows
rw.Hidden = Application.CountA(rw) = 0
Next rw

End Sub
'<<=============

The most commonly recommended books are those by John Walkenbach:

http://www.j-walk.com/ss/books/index.htm

See also Debra Dalgleish at:

http://www.contextures.com/xlbooks.html

and

http://www.contextures.com/xlbooks.html

 

[Thulasiram]

Thanks a lot Norman. Really appreciate it. I replaced 0 with 1 to
unhide the rows/columns.

Thanks again,
Thulasiram

 

[Norman Jones]

Hi Thulasiram,

I replaced 0 with 1 to unhide the rows/columns.

That would only work for a parrticular data configuration.

To unhide the empty rows and columns, try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet

Set SH = ActiveSheet

With SH.UsedRange
.Columns.Hidden = False
.Rows.Hidden = False
End With
End Sub
'<<=============

 

[Thulasiram]

Thanks a lot again Norman. You solved my problem completely.

Best,
Thulasiram