Delete sheet if found.

[pavin]

Hi all,

I'm using the following code to delete a sheet if found. Its throwing an error, when it is found and deleted. Need to understand how to recalculate the sheets count in a loop as such. Please help

Sht_Cnt = ActiveWorkbook.Sheets.Count
For i = 1 To Sht_Cnt
If Sheets(i).Name = "Dummy" Then
Application.DisplayAlerts = False
Sheets(i).Delete
Application.DisplayAlerts = True
End If
Next i

 

[VoG]

Try

Sht_Cnt = ActiveWorkbook.Sheets.Count
For i = Sht_Cnt To 1 Step -1
    If Sheets(i).Name = "Dummy" Then
        Application.DisplayAlerts = False
        Sheets(i).Delete
        Application.DisplayAlerts = True
        Exit For
    End If
Next i

 

[pavin]

Wow!!..its working fine VoG. Many Thanks for you reply.

 

[GTO]

Hi all,

Not well tested, but I think you could also 'roughshod' it.

Option Explicit
    
Function DelSheets(ParamArray ShNames() As Variant)
Dim i As Long
    
    On Error Resume Next
    Application.DisplayAlerts = False
    For i = LBound(ShNames) To UBound(ShNames)
        ThisWorkbook.Worksheets(ShNames(i)).Delete
    Next
    Application.DisplayAlerts = True
End Function
    
Sub exa1()
    Call DelSheets("Sheet1", "Sheet2")
End Sub

 

[John Davis]

Hi all,

I'm using the following code to delete a sheet if found. Its throwing an error, when it is found and deleted. Need to understand how to recalculate the sheets count in a loop as such. Please help

Sht_Cnt = ActiveWorkbook.Sheets.Count
For i = 1 To Sht_Cnt
If Sheets(i).Name = "Dummy" Then
Application.DisplayAlerts = False
Sheets(i).Delete
Application.DisplayAlerts = True
End If
Next i

FWIW without the loop

Sub pavin()
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Dummy").Delete
Application.DisplayAlerts = True
End Sub

 

[Ingolf]

Hi all,

Not well tested, but I think you could also 'roughshod' it.

Option Explicit
    
Function DelSheets(ParamArray ShNames() As Variant)
Dim i As Long
    
    On Error Resume Next
    Application.DisplayAlerts = False
    For i = LBound(ShNames) To UBound(ShNames)
        ThisWorkbook.Worksheets(ShNames(i)).Delete
    Next
    Application.DisplayAlerts = True
End Function
    
Sub exa1()
    Call DelSheets("Sheet1", "Sheet2")
End Sub

Hello,

GTO please tell me how I can change your code to take sheet name from cell A5 to A15.
Thank you.

 

[Ingolf]

Anyone?

 

[GTO]

Hello,

GTO please tell me how I can change your code to take sheet name from cell A5 to A15.
Thank you.

For like:

Excel Workbook
	A
4	Header
5	Sheet19
6	Sheet20
7	Sheet21
8	Sheet22
9	Sheet 23
10	Sheet24
11	Sheet25
12	Sheet26
13	Sheet27
14	Sheet28
15	Sheet29
Sheet2

In a Standard Module:

Option Explicit
    
Function DelSheets(ShNames() As Variant)
Dim i As Long
    
    On Error Resume Next
    Application.DisplayAlerts = False
    For i = LBound(ShNames) To UBound(ShNames)
        ThisWorkbook.Worksheets(ShNames(i)).Delete
    Next
    On Error GoTo 0
    Application.DisplayAlerts = True
End Function
    
Sub exa1()
Dim ary()
    
    '// Please note that I used the CodeName of Sheet2 to qualify the Range //
    ary = Application.Transpose(Sheet2.Range("A5:A15").Value)
    Call DelSheets(ary)
End Sub

The above of course does not check success, so presuming that our inadvertant space in 'Sheet 23' would leave 'Sheet23' un-deleted.

Hope that helps,

Mark

 

[Ingolf]

Hello, GTO It give an error and highlight Sheet2 and sub exa1()

Sub exa1()
Dim ary()

'// Please note that I used the CodeName of Sheet2 to qualify the Range //
ary = Application.Transpose(Sheet2.Range("A5:A15").Value)
Call DelSheets(ary)
End Sub

Can you help me?

 

[GTO]

'// Please note that I used the CodeName of Sheet2 to qualify the Range //

Hi there,

You can use the correct codename by selecting the sheet, right-click and choose view code. See how the properties window lists two name properties for the sheet? The top one is the codename for the sheet. Use that.