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
jackdash

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
not so gray matter

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
Thread Forum
Help!! Excel worksheet look empty after Office 2010 uninstall
Hello Big problem for me I install Office 2013 about 2 months ago. Prior, I was using Office 2010 after Office 2007 etc. Today, I saw the both version was install on My PC. Office 2013 and Office 2010. So I close all Excel sheet and proced to uninstall Office 2010 and Office 2010...
Microsoft Office
Is it safe to delete an almost empty partition?
I have two disk drives on my computer, Disk 0 and Disk 1 (see attached screenshot). Disk 0 has two partitions, System Reserved and E:. Disk 1 is given over to the C: drive. E: only has Recycle Bin and System Volume Information on it (see attached screenshot). I want to delete the E:...
General Discussion
How can I find and delete empty folders on my PC?
Is there any program to find and delete empty folders in Windows 7. I'm asking cos when a program is uninstalled it's deleting the files within the folder instead of that folder. I just want to get rid of those rubbish 0 byte folders. :cry:
Performance & Maintenance
24 empty folders on here ? Can i delete? Please help
C:\Users\Jonathan\AppData\Local Only has this on the named folders. {0D23A1D9-E1F0-49EA-82DB-38849844F009} {1E5D2A8F-7605-4921-B111-43225FEDF31A} but its 24 folders and its empty can I erased.
General Discussion
Windows will not let me delete some empty folders...
Citing that I do not have the correct privileges to do so, Windows is telling me that it/I cannot delete some file folders that I do not have a need for. I have tried running Windows Explorer as Administrator, but this does not help. How do I elevate myself to have the proprer priveleges to...
General Discussion
Can't Delete Non-Empty Folders
Ever since reformatting my C drive and reinstalling Windows 7 from the recovery partition on my hard drive, I've been having an intermittent problem. When I try to delete a folder that is not empty, Windows throws up an error message saying I need permission. However, I am the only user, I am an...
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 03:22.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App