Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
deleting the blank rows from the sheet | Excel Worksheet Functions | |||
Deleting all but one blank row | Excel Discussion (Misc queries) | |||
Deleting Blank Rows | Excel Programming | |||
Deleting Blank Rows | Excel Programming | |||
Deleting every sheet in workbook after Worksheets(4) | Excel Programming |