Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
I would like to get help to improve the macro below so that if Sheet2 or
Sheet3 or both, have no contents in their worksheets they will get deleted. On the other hand if Sheet2 or Sheet3, or both have contents they will stay. Here is the code I have right now. Thank you for your help. Sub Deletesheetswithoutcontents() Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
(Untested) This should do what you want...
Sub DeleteEmptySheets2or3() Application.DisplayAlerts = False If Sheets("Sheet2").Cells.Find("*") Is Nothing Then Sheets("Sheet2").Delete If Sheets("Sheet3").Cells.Find("*") Is Nothing Then Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub -- Rick (MVP - Excel) "Sal" wrote in message ... I would like to get help to improve the macro below so that if Sheet2 or Sheet3 or both, have no contents in their worksheets they will get deleted. On the other hand if Sheet2 or Sheet3, or both have contents they will stay. Here is the code I have right now. Thank you for your help. Sub Deletesheetswithoutcontents() Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
Helo Sal,
I like Rick's method of testing for data also but I would also test for the existance of the worksheets otherwise if the code is run again after the sheets are deleted then it will error out so have posted another option including a different method of testing for existance of data. The Else with msgbox if sheet contains data is optional. Note that the space and underscore at the end of a line is a line break in an otherwise single line of code. Sub DeleteSheetsWithoutContents() Dim ws As Worksheet Application.DisplayAlerts = False On Error Resume Next Set ws = Sheets("Sheet2") On Error GoTo 0 If Not ws Is Nothing Then 'Sheet exists If WorksheetFunction. _ CountA(ws.UsedRange) = 0 Then ws.Delete Else MsgBox ws.Name & " contains data" End If End If Set ws = Nothing On Error Resume Next Set ws = Sheets("Sheet3") On Error GoTo 0 If Not ws Is Nothing Then 'Sheet exists If WorksheetFunction. _ CountA(ws.UsedRange) = 0 Then ws.Delete Else MsgBox ws.Name & " contains data" End If End If Application.DisplayAlerts = True End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
Sub testshts()
Application.DisplayAlerts = False On Error Resume Next For i = Worksheets.Count To 2 Step -1 If Sheets(i).Cells.Find(What:="*", After:=[A1], _ SearchDirection:=xlPrevious).Address = " " Then 'MsgBox Sheets(i).Name Sheets(i).Delete End If Next i Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Sal" wrote in message ... I would like to get help to improve the macro below so that if Sheet2 or Sheet3 or both, have no contents in their worksheets they will get deleted. On the other hand if Sheet2 or Sheet3, or both have contents they will stay. Here is the code I have right now. Thank you for your help. Sub Deletesheetswithoutcontents() Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
Thank you Sir. This is very helpful.
"Rick Rothstein" wrote: (Untested) This should do what you want... Sub DeleteEmptySheets2or3() Application.DisplayAlerts = False If Sheets("Sheet2").Cells.Find("*") Is Nothing Then Sheets("Sheet2").Delete If Sheets("Sheet3").Cells.Find("*") Is Nothing Then Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub -- Rick (MVP - Excel) "Sal" wrote in message ... I would like to get help to improve the macro below so that if Sheet2 or Sheet3 or both, have no contents in their worksheets they will get deleted. On the other hand if Sheet2 or Sheet3, or both have contents they will stay. Here is the code I have right now. Thank you for your help. Sub Deletesheetswithoutcontents() Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
Cool code. It works well. I am grateful for your help. Thank you.
"Don Guillett" wrote: Sub testshts() Application.DisplayAlerts = False On Error Resume Next For i = Worksheets.Count To 2 Step -1 If Sheets(i).Cells.Find(What:="*", After:=[A1], _ SearchDirection:=xlPrevious).Address = " " Then 'MsgBox Sheets(i).Name Sheets(i).Delete End If Next i Application.DisplayAlerts = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Sal" wrote in message ... I would like to get help to improve the macro below so that if Sheet2 or Sheet3 or both, have no contents in their worksheets they will get deleted. On the other hand if Sheet2 or Sheet3, or both have contents they will stay. Here is the code I have right now. Thank you for your help. Sub Deletesheetswithoutcontents() Application.DisplayAlerts = False Sheets("Sheet2").Delete Sheets("Sheet3").Delete Application.DisplayAlerts = True End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Sheets Without Contents
You make some interesting points that are good to think about. I appreciate
your assistance. Thank you friend. The macro works very nicely. "OssieMac" wrote: Helo Sal, I like Rick's method of testing for data also but I would also test for the existance of the worksheets otherwise if the code is run again after the sheets are deleted then it will error out so have posted another option including a different method of testing for existance of data. The Else with msgbox if sheet contains data is optional. Note that the space and underscore at the end of a line is a line break in an otherwise single line of code. Sub DeleteSheetsWithoutContents() Dim ws As Worksheet Application.DisplayAlerts = False On Error Resume Next Set ws = Sheets("Sheet2") On Error GoTo 0 If Not ws Is Nothing Then 'Sheet exists If WorksheetFunction. _ CountA(ws.UsedRange) = 0 Then ws.Delete Else MsgBox ws.Name & " contains data" End If End If Set ws = Nothing On Error Resume Next Set ws = Sheets("Sheet3") On Error GoTo 0 If Not ws Is Nothing Then 'Sheet exists If WorksheetFunction. _ CountA(ws.UsedRange) = 0 Then ws.Delete Else MsgBox ws.Name & " contains data" End If End If Application.DisplayAlerts = True End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming |