Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Hide/Unhide cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Hide/Unhide cells
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 --- Regards, Norman "Thulasiram" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Hide/Unhide cells
Thanks a lot Norman. Really appreciate it. I replaced 0 with 1 to
unhide the rows/columns. Thanks again, Thulasiram Norman Jones wrote: 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 --- Regards, Norman "Thulasiram" wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Hide/Unhide cells
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 '<<============= --- Regards, Norman "Thulasiram" wrote in message ups.com... Thanks a lot Norman. Really appreciate it. I replaced 0 with 1 to unhide the rows/columns. Thanks again, Thulasiram |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question on Hide/Unhide cells
Thanks a lot again Norman. You solved my problem completely.
Best, Thulasiram Norman Jones wrote: 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 '<<============= --- Regards, Norman "Thulasiram" wrote in message ups.com... Thanks a lot Norman. Really appreciate it. I replaced 0 with 1 to unhide the rows/columns. Thanks again, Thulasiram |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide/Unhide Cells | Excel Worksheet Functions | |||
Hide and unhide shaded cells | New Users to Excel | |||
Hide/Unhide cells using VBA programming | Excel Programming | |||
Switching views - Hide / Unhide preselected cells (Repost) | Excel Discussion (Misc queries) | |||
hide/unhide cells | Excel Worksheet Functions |