Deleting a blank sheet in a workbook

**wolfdogg** · 07-15-2005, 01:04 PM

Hi all. I've got a VB program that is eporting SQL data to a workbook. It loops to create a new sheet for every employee. It's working well... but I still have some format issues to overcome. I'm ok with VB, but the Excel syntax is all new to me as of this project.

As my code creates a new sheet first before checking for the next employee, it finishes by adding a new sheet to the very end of the workbook. I tried using: ActiveWindow.SelectedSheets.Delete

Unfortunately, the program is jumping back to the first sheet before deleting. This doesn't work well anyway, because finishes the code out by prompting the user is s/he wants to delete before deleting.

Is there a way to search the active workbook and delete any sheets than contain no data?

Thx!

Edit: Argh.. I think I got it. The above statement did work.. it deletes without the prompt when the sheet is empty, I now see. I just had to place it before the code to delete my first 2 blank pages that Excel automatically places in a workbook:

Sub DeleteFirst2Sheets()
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
ActiveWindow.SelectedSheets.Delete
End Sub

Thx anyway!

anonymousA · 07-15-2005, 02:05 PM

Hi,

you can make a loop on all worksheets and use the CountA function

with Application
.DisplayAlerts = False
For Each sh In ActiveWorkbook.Worksheets
If .WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
Next
..DisplayAlerts = False
End with

Be sure that at least one of the sheets contains data, for otherwise VBA
will return an error message , cos Excel can't afford the workbook to
have no sheets at all.

Regards

wolfdogg a écrit :
> Hi all. I've got a VB program that is eporting SQL data to a workbook.
> It loops to create a new sheet for every employee. It's working well...
> but I still have some format issues to overcome. I'm ok with VB, but
> the Excel syntax is all new to me as of this project.
>
> As my code creates a new sheet first before checking for the next
> employee, it finishes by adding a new sheet to the very end of the
> workbook. I tried using: *ActiveWindow.SelectedSheets.Delete*
>
> Unfortunately, the program is jumping back to the first sheet before
> deleting. This doesn't work well anyway, because finishes the code out
> by prompting the user is s/he wants to delete before deleting.
>
> Is there a way to search the active workbook and delete any sheets than
> contain no data?
>
> Thx!
>
>

JMB · 07-16-2005, 01:05 AM

I think you meant to turn alerts back on the end <g>

..DisplayAlerts = True
End with

"anonymousA" wrote:

> Hi,
>
> you can make a loop on all worksheets and use the CountA function
>
> with Application
> .DisplayAlerts = False
> For Each sh In ActiveWorkbook.Worksheets
> If .WorksheetFunction.CountA(sh.Cells) = 0 Then sh.Delete
> Next
> ..DisplayAlerts = False
> End with
>
> Be sure that at least one of the sheets contains data, for otherwise VBA
> will return an error message , cos Excel can't afford the workbook to
> have no sheets at all.
>
> Regards
>
> wolfdogg a Ã©crit :
> > Hi all. I've got a VB program that is eporting SQL data to a workbook.
> > It loops to create a new sheet for every employee. It's working well...
> > but I still have some format issues to overcome. I'm ok with VB, but
> > the Excel syntax is all new to me as of this project.
> >
> > As my code creates a new sheet first before checking for the next
> > employee, it finishes by adding a new sheet to the very end of the
> > workbook. I tried using: *ActiveWindow.SelectedSheets.Delete*
> >
> > Unfortunately, the program is jumping back to the first sheet before
> > deleting. This doesn't work well anyway, because finishes the code out
> > by prompting the user is s/he wants to delete before deleting.
> >
> > Is there a way to search the active workbook and delete any sheets than
> > contain no data?
> >
> > Thx!
> >
> >
>

Roy · 07-15-2005, 02:05 PM

Wolfdogg,

Couldn't you alter your loop to create the new sheet after verifying it is
actually needed. It would look better from an asthetic standpoint to not add
an uneeded sheet and then delete it later. It sounds like a "simple"
re-arrangement of exisiting code to me.

Roy

"wolfdogg" wrote:

>
> Hi all. I've got a VB program that is eporting SQL data to a workbook.
> It loops to create a new sheet for every employee. It's working well...
> but I still have some format issues to overcome. I'm ok with VB, but
> the Excel syntax is all new to me as of this project.
>
> As my code creates a new sheet first before checking for the next
> employee, it finishes by adding a new sheet to the very end of the
> workbook. I tried using: *ActiveWindow.SelectedSheets.Delete*
>
> Unfortunately, the program is jumping back to the first sheet before
> deleting. This doesn't work well anyway, because finishes the code out
> by prompting the user is s/he wants to delete before deleting.
>
> Is there a way to search the active workbook and delete any sheets than
> contain no data?
>
> Thx!
>
>
> --
> wolfdogg
> ------------------------------------------------------------------------
> wolfdogg's Profile: http://www.excelforum.com/member.php...o&userid=25269
> View this thread: http://www.excelforum.com/showthread...hreadid=387569
>
>