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

 

[gallen]

This line is the line causing the issues:

[COLOR=#333333]After:=.Cells(.Cells.Count)[/COLOR]

I just tested your code. What is the line trying to achieve? Trying to find the worksheet's name but only after a certain cell?

 

[tiredofit]

That line was taken from here:

https://www.rondebruin.nl/win/s9/win006.htm

 

[tiredofit]

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

Thanks, that's what I wanted.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Thanks, that's what I wanted.