finding a blank sheet

Does anybody have an idea of the best way to look in a workbook an
delete any sheet that is completely blank?  Therefore only keepin
worksheets that have data in them.

Thanks for your help,

Message posted from

9/14/2004 1:24:16 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies

Similar Articles

[PageSpeed] 25


I don't profess to have written this but have found the folowing cod
which would appear to do what you want

Sub Delete_EmptySheets()
Dim sh As Worksheet 
For Each sh In ThisWorkbook.Worksheets
If Application.WorksheetFunction.CountA(sh.Cells) = 0 Then
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End If
End Sub



Message posted from

9/14/2004 1:37:43 PM

This VBA macro does the trick:

Sub RemoveEmptyWS()
Dim ws As Object

Application.DisplayAlerts = False

For Each ws In Worksheets
If ws.Cells.SpecialCells(xlCellTypeLastCell).Address = "$A$1
End If
Next ws

Application.DisplayAlerts = True

End Sub

You can remove (or comment) the "Application.DisplayAlerts = ...
lines, if you want Excel to confirm the sheet deletions.

- Asse

Message posted from

9/14/2004 1:52:46 PM
That was it.  Thanks for your help.


Message posted from

9/14/2004 2:01:31 PM
Thanks for both your ideas.  Both your posts are similar and work but is
there any advantage of defining an object over a worksheet?


Message posted from

9/14/2004 2:21:46 PM
One of the big advantages of using the correct type when you define an object
variable is the intellisense help that you get from the VBE.

If you do
dim ws as worksheet

and later (while you're coding), type ws. (W-S-(dot)), you'll see all of the
properties and methods that apply to a worksheet.  You can choose from this list
to save typing time (and more importantly, typing mistakes!).

Be a little careful with Jazzer's version--especially if you have a worksheet
with just an entry in A1.

"cparsons <" wrote:
> Thanks for both your ideas.  Both your posts are similar and work but is
> there any advantage of defining an object over a worksheet?
> Thanks,
> Craig
> ---
> Message posted from


Dave Peterson
ec35720 (10082)
9/14/2004 10:25:25 PM
Dave Peterson wrote:
> *Be a little careful with Jazzer's version--especially if you have 
> worksheet with just an entry in A1.
> Dave Peterson
> *

Yeah. I notised the problem after I left my from my computer yesterday
If something is in cell A1 and nothing else on your worksheet, th
worksheet will be deleted.

- Asse

Message posted from

9/15/2004 10:41:32 AM

Similar Artilces:

Compare Sheets #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but just does nothing. However if I type shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Excel creating 'backup' version of my sheet...
Hi, a user noticed that one of his sheets is creating backup-copies by itself... - He deletes the backup version - opens the original sheet - change something - saves the original sheet - closes the original sheet tadaaaaa.... now a new file with 'name backup.xls' is created... He told me that he noticed this behaviour when he was 'protecting' his sheet by a password. Now we moved to Office2007 and he wants to get rid of this behaviour. Any suggestions? (btw, no add-ins are installed!) FOUND, Seems there is an extra checkbox, hidden in the tools options in 'Save...

Blank paper cheques
We are looking at purchasing blank cheque paper stock and having the bank encoding and cheque number added when we print, but I am not sure how to deal with post dated cheques. Post dated cheques beyond the current fiscal year are currently hand printed and entered into GP's as manual cheques each month. How can we print these in GP's. Thanks ...

how do I set up spread sheet for demographical data to get graph.
I need to set up a spread sheet to be able to get age, sex,service usage numbers, site utilisation, presenting issue e.g., drug and alcohol, region, percentage of use base on population e.g, 80 staff DoHi, Do you want to show these results graphically, or in the form of some sort of output table? Dave url: How are you intending to get the data or enter the data into this sheet? How many worksheets or tables will you need? What is the final display options: Examples, Reports, Charts, Pivot = table? And lastly who are the end users, and how do they...

Finding Values With more than 2 decimal Places
Hello, I am using sumif to verify some numbers. These should all be dollar values and thus should not contain and values past 2 decimal places however when I use the sumif it returns values with long decimals? I have been unable to find where these decimals exist visually and i tried this formula to help me identify them =IF(L18-(ROUND(L18,2))=0,0,"Help") ^ hopefully this would let me know if a decimal past 2 places is present in a value but I still am unable to find them? any help would be apreciated Thanks Chuck -- clane -------------------------------------------...

Timestamp a sheet #3
That works good. Thanks alot -- eluehman ----------------------------------------------------------------------- eluehmann's Profile: View this thread: Your welcome, thanks for the feedback -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "elueh...

Advance Find Order Product
Using Advance Find, is there a way to search Orders that have both Order Product A AND Order Product B? I can easily find Orders that have either Order Product A or Order Product B, but can't seem to make it look for both. Thank you, Adam Three Day Bump. Hi, Don't have CRM in front of me, but try this: Create advanced find with 2 lines using the same field. Select both lines, group as 'AND'. Should work. Good luck, Bertil "AdamEdmonds" wrote: > Three Day Bump. Ok. I tried that and got no results back even though I saw at least two that fit my query. ...

Function to find largest number from a series of numbers?
Is there a function to go across several columns of numbers and pick out the largest one? If not, how would I write that function? thanks to all Rick =MAX(A1:Z1) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "rs" <> wrote in message news:2p-dnW3P0f...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

Cell.Find in VBA
Hi, I have the following VBA Macro: Set FoundCell = .Cells.Find(What:="199", _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) I don't how to write a VBA macro that would cells.find all 5-digit numbers. Here's an example: Column A 199 199 75781 75899 199 80012 Thanks, Hi I think you have to loop through all cells and check the length/value of each cell -- Regards Frank Kabel Frankfurt, Germany "Jeff" <> schrieb im Newsbeitrag news:D3CEA329-2A...

find a copy of a standard formal report lay out/template
looking for an example of a formal report template Hello pppe productivity placement program example wrote: > looking for an example of a formal report template You can find templates offered by Microsoft at the following URL: namely: HTH Robert -- /"\ ASCII Ribbon Campaign | MSFT | \ / | MVP | Scientific Reports X Against HTML | for | with Word? / \ in e-mail & news | Word | ...

Find the depth of XML
I have following XML: How can I find the depth of XML? in other word max level of the XML <Root> ----- level 0 <Name>----- level 1 <FirstName>name</FirstName> ---- level 2 <LasName>lname</LastName> <MiddleName>mName<MiddleName> </Name> <Information> <Info1> <Info2> --- level 3 <Info3>asdasd</Info> -- level 4 </Info2> </Info1> </Information> </Root> Raed Sawalha wrote: > I have following XML:...

how to find the last modified user?
Is it possible in CRM to determine who last modified/altered a particular record (of any entity)? Esp, who last modified the details of a particular contact? Thanx There is an example in the SDK of building an "Auditing" module for the CRM admin which would give views of who changed which records, etc. Matt Wittemann "Simon" wrote: > Is it possible in CRM to determine who last modified/altered a particular > record (of any entity)? > Esp, who last mod...

Collating entries from multiple sheets
Dear Experts, I have 28 worksheets (drug classes) each with a column O in which drug names will appear if they meet certain criteria. I would like to list these drugs, from the 28 worksheets on one page, as a summary. I want excel to look down column O, for about 100 rows, and collect any occurrences of drug names. In 100 rows drug names may appear 20 times (they will all be unique), the other cells being blank. Can I collect these occurrences on one sheet for summary purposes? regards Martina This should give you some ideas: Also: ...

Blank Email Still
Outlook Versions 2000 and 2002 all updates Exchange Server 2000 no service packs Windows 2000 Server all updates Windows XP clients all updates I am still having blank bodies of email sent. The E-mail is also blank in the sent box as well as blank at the receiver.. Seems random. Here is whet I have done. Disabled Signatures Disabled Norton Anti-virus 2003 Pro I will apply SP3 to Exchange tonight. Any ideas? Thanks B Hi, Bob try recreating your email profile and test the issue Good luck! Yvon "Bob" wrote: > Outlook Versions 2000 and 2002 all updates > Exchange Serv...

CONCATENATE problem with blank cells
I need to merge columns A and B, but some cells are blank, so I can't copy the formula down the 3rd stops at the 1st blank a1 Frank Smith a2 blank a3 blank a4 Bill Jones b2 blank b2 sally b3 billie b4 blank HELP? -- roger_home ------------------------------------------------------------------------ roger_home's Profile: View this thread: Hi Roger, You could Concatenate the Cells in Columns "A" & "B" Using the Follow...

Add comments to your excel sheet using SpotNote
SpotNote 3.1 is a commenting tool that allows the user to post electronic notes directly to the documents they are editing. Cut out secondary documentation and emails to describe what you changed in a document for a team member or co-worker. SpotNote also Merges IM with E-Sticky Notes BCI announces the addition of SpotLAN to its highly rated electronic note software, SpotNote´┐Ż 3.1. SpotLAN allows the business user to send custom sticky notes instantly across their corporate LAN environment to another computer's desktop. SpotLAN solves the problem of moving from your desk or making a p...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Automate unprotecting of blank cells
I have a spreadsheet that has several cells with values entered int it. I want others to add some values, but I don't want them to change cell that already have values. I know that I can use goto, special, constants and protect thos cells. Can someone recommend a macro or method to review each cell within specified range and unprotect it if it is blank? Any suggestions would be appreciated. Jim Palme -- Jim Palme ----------------------------------------------------------------------- Jim Palmer's Profile: View t...

Combining multiple spread sheets into one
I have individual week spread sheets needing to be combined into one form for year report. This is weekly contributor reporting that needs to be an individual reporting for year end report. Is there a simple way to accomplish this? Hi flaschman Try my add-in Or see the links to the VBA code examples -- Regards Ron de Bruin "flaschman" <> wrote in message >I have individual week spread...

editing multiple sheets at once
I was cruising along editing multiple sheets at the same time (by selecting them all - aka grouping) and everything was working fine and then suddenly it stopped. All that happens now is that despite several sheets being selected the only sheet that gets any editing done is the one in view. Any suggestions? What type of editing are you attempting? Not all features are available with groupd sheets. You cannot group edit if one if the sheets is protected. Do you get any error message? Some print setup features are not possible. You cannot sort or filter on grouped sheets. Gord Dibb...

How do I set the focus for a field after new (blank) record ?
Hi When I click the "new (blank) record" icon at the bottom of a form, the cursor disappears. How can I make the cursor go to the first field on the form like it does when I first go to the form. Thanks in advance Mark Thornblad Use the form's Current event to run a macro that contains the GoToControl action, and use that to set the focus to the desired field. -- Ken Snell <MS ACCESS MVP> <> wrote in message > Hi > > When I click the "new (bla...

How do I print 2 copies of a Publisher document on an A4 sheet
Can anyone help please? Using Publisher Xp forming part of Office Xp. Setup your printer for A4, in page layout select custom, landscape, type 14.8cm width, 20.997cm height. The dialogue should say "Prints two copies per sheet." -- Mary Sauer MSFT MVP news:// "Wombat" <> wrote in message > Can anyone help please? Using Publisher Xp forming part of Office Xp. ...

Labels leaving a blank line where addr2 is missing
Some of my addresses have 1 address line and some have 2. In order to include all of them in the same label, I'm getting a blank line when address 2 is missing. Why won't it float if there is a blank line? -- Brenda First, check that the Section that contains your Address fields is set to Grow and Shrink. To do this, click inside that section or on the grey bar just above it but ensure that you are not selecting any controls. In the Properties box next to Can Grow and Can Shrink (like Alice In Wonderland) click Yes. 2. Click in the left margin so that you are selecting all your tex...

how can I email the results and aviod getting an "Update the sheet?" message?
I have a worksheet containing several sheets many of which are "linked" by formulae embedded in certain cells. Many of the sheets contain a table showing the results. These tables are formatted to have colours etc and to generally "look nice" I often have to email the data tables from only one or two of those sheets but it seems tha twhen the recipients get them and open them, they get a message telling them that the sheets are linked to another source and asking them " do you want to update the cells" (or wording similar to that) Apart from simply cutting and p...