Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.



Windows 7: Delete empty cells in worksheet

21 Apr 2010   #1

Windows 7 Home Premium
 
 
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.

Thanks
jack

My System SpecsSystem Spec
.

21 Apr 2010   #2

W7 Ult. x64 | OS X
 
 

Quote   Quote: Originally Posted by jackdash View Post
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.

Thanks
jack
Check out the following:

Quote:
Question
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.

Kind regards,
Reshma

Answer
Hi Reshma,

Here is a macro that will hide zero-filled or empty columns:

Sub HideEmptyColumns()

' 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)
Next Col

End Sub

Sub UnhideColumns()

' This macro unhides all columns on the active worksheet

Columns.Hidden = False

End Sub

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
Exit For
End If
Next C

End Function

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.

Keep Excelling.

Damon
How to: Remove all blank rows at once | asap utilities / excel blog

Excel 2007: Eliminate Blank Rows in a Spreadsheet | Microsoft Excel | Tech-Recipes

http://www.techsupportforum.com/micr...lls-excel.html
My System SpecsSystem Spec
Reply

 Delete empty cells in worksheet





Thread Tools



Similar help and support threads for2: Delete empty cells in worksheet
Thread Forum
accidently delete my file and empty recycle bin. need help Backup and Restore
Solved How can I find and delete empty folders on my PC? Performance & Maintenance
Solved Unable to delete empty registry entry General Discussion
24 empty folders on here ? Can i delete? Please help General Discussion
Solved 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

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 01:16 AM.
Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App
  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33