Delete sheets with reference to named range

[tiredofit]

I want to delete worksheets according to what's specified on a worksheet.

In Sheet1, I have a named range called b, which contains two values, Sheet1 and Sheet2.

Option Explicit

Sub Start()

    Dim MyCls As Class1
    Set MyCls = New Class1
    
    Set MyCls.Cols = Sheet1.Range("b")
    
    Call MyCls.DeleteWorksheets

End Sub

This is in Class1:

Option Explicit
    Private pCols As Range
    
Public Property Get Cols() As Range
    
    Set Cols = pCols
    
End Property
Public Property Set Cols(ByVal C As Range)
    
    Set pCols = C
    
End Property
    
Sub DeleteWorksheets()
    Application.EnableEvents = False
    
    Dim Rng As Range
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
               
        With Sheet1(Cols)
        
            Set Rng = .Find(What:=ws.Name, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            
            If Rng Is Nothing Then
                
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
            
        End With
       
    Next ws
    
    Application.EnableEvents = True
End Sub

The code fails here:

With Sheet1(Cols)

with a message:

Run-time error '438': Object doesn't support this property or method

What is wrong?

Thanks

EDIT SORTED:

With Cols

 

[tiredofit]

Following on from this, I want to have two named ranges, like this:

Option Explicit
Sub Start()
    Dim MyCls As Class1
    Set MyCls = New Class1
    
    Set MyCls.Cols = Union(Sheet3.Range("a"), Sheet3.Range("b"))
    
    Call MyCls.DeleteWorksheets
End Sub

but it fails here:

Set Rng = .Find(What:=ws.Name, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

with this message:

Run-time error '13': Type mismatch

What's wrong?

 

[gallen]

Where is the 'With' statement? I'm guessing the troubled code is this:

.Cells(.Cells.Count)

 

[tiredofit]

Where is the 'With' statement? I'm guessing the troubled code is this:

.Cells(.Cells.Count)

Here:

        With Cols
        
            Set Rng = .Find(What:=ws.Name, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

 

[gallen]

Shouldn't that be:

With MyCls.Cols

?

 

[tiredofit]

No, it works in my first example with just

 With Cols

Adding

With MyCls.Cols

won't compile.

 

[gallen]

Sorry. My fault. Should have seen it was within the class:

*Edited, didn't see named ranges

 

[My Aswer Is This]

Why would not something this simple work?

[B]​[/B]Sub Delete_Sheets()
Application.DisplayAlerts = False
Dim c As Range
    
    For Each c In Range("b")
    Sheets(c.Value).Delete
    Next
Application.DisplayAlerts = True
End Sub

 

[tiredofit]

Why would not something this simple work?

[B]​[/B]Sub Delete_Sheets()
Application.DisplayAlerts = False
Dim c As Range
    
    For Each c In Range("b")
    Sheets(c.Value).Delete
    Next
Application.DisplayAlerts = True
End Sub

My first example, with only a single named range, works.

I'm trying to extend it to two named ranges.

 

[My Aswer Is This]

For 2 named range you would do it this way:

Sub Delete_Sheets()
Application.DisplayAlerts = False
Dim c As Range
Dim Del As Variant
Del = Array("b", "Mom")
   
    For i = 0 To 1
        For Each c In Range(Del(i))
            Sheets(c.Value).Delete
        Next
    Next
Application.DisplayAlerts = True
End Sub