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.


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.

Check out the following:

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,

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.

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

Excel 2007: Eliminate Blank Rows in a Spreadsheet | Microsoft Excel | Tech-Recipes
My System SpecsSystem Spec

 Delete empty cells in worksheet

Thread Tools

Similar help and support threads
Thread Forum
Empty Recycle Bin don't delete all
I was going to backup by drive when I saw that I have files in the recycle bin(I usually use shift+delete when I want to delete something and I use the recycle bin as a temporary folder from which I'll restore the files later). So I check the files and I saw that I don't need any of then I used...
General Discussion
excel formula not shown if the cells are empty
i have excel file, with the following formulas Cell G2 formula =SUM(D2:F2) Cell G3 formula =SUM(G2, D3:F3) Cell G4 formula =SUM(G3, D4:F4) Cell G5 formula =SUM(G4, D5:F5) i want the result of G5 (and the rest of G cells) cells to be invisible unless there is some value in D5 to F5 cells ...
Microsoft Office
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
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
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 10:26.
Twitter Facebook Google+ Seven Forums iOS App Seven Forums Android App