Deleting a blank sheet in a workbook
 

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

Deleting a blank sheet in a workbook
 

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!

Deleting a blank sheet in a workbook
 

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

Deleting a blank sheet in a workbook
 

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!