|21 Apr 2010||#1|
| || |
Delete empty cells in worksheet
I am a newbie to Excel and have created a small calculation worksheet.
It only occupies a small section of the worksheet and I would like to know how to only show that part and not showing the empty cells.
I am using Microsoft Office 2007.
|My System Specs|
|21 Apr 2010||#2|
| || |
Hi, I am looking for a solution to hide columns based upon their content.
I know this is logically possible but I am struggling with the "How" and really do hope you can help me.
Background Info: I have a database which holds data from which I am building reports for users. This database has an Excel Add-In which allows me to create reports (views) in Excel to distribute to users. The Add-In has a switch which automatically suppresses any rows which has zero's in the cells across the columns.
Problem: The report I am building uses 219 columns. Most of these columns contain zero's. I would like to build a macro to hide these columns so that the users only see those columns with data in them.
Thank you in advance for your help.
Here is a macro that will hide zero-filled or empty columns:
' This macro hides empty or all-zero columns in the active worksheet
' It ignores columns outside the used range (UsedRange)
Dim Col As Range
For Each Col In ActiveSheet.UsedRange.Columns
Col.EntireColumn.Hidden = AllZero(Col)
' This macro unhides all columns on the active worksheet
Columns.Hidden = False
Function AllZero(R As Range) As Boolean
' This function returns TRUE if the range is entirely empty or all zero
Dim C As Range
AllZero = True
For Each C In R.Cells
If C.Value <> 0 Then
AllZero = False
I also included an UnhideColumns macro to enable you to easily unhide the hidden columns. The user-defined function AllZero is a "helper" function used by the HideEmptyColumns macro.
Simply install this code in a standard macro module just as you would any other macro. The macro will always operate on the active worksheet.
Excel 2007: Eliminate Blank Rows in a Spreadsheet | Microsoft Excel | Tech-Recipes
|My System Specs|
|Similar help and support threads for2: Delete empty cells in worksheet|
|accidently delete my file and empty recycle bin. need help||Backup and Restore|
|How can I find and delete empty folders on my PC?||Performance & Maintenance|
|Unable to delete empty registry entry||General Discussion|
|24 empty folders on here ? Can i delete? Please help||General Discussion|
|Windows will not let me delete some empty folders...||General Discussion|
|Unable to Delete Empty Folders; Tried many fixes||Performance & Maintenance|
|Can't Delete Non-Empty Folders||General Discussion|
|Our Sites ||Site Links ||About Us ||Find Us |
© Designer Media Ltd
All times are GMT -5. The time now is 05:06 PM.