rdoulaghsingh
Board Regular
- Joined
- Feb 14, 2021
- Messages
- 105
- Office Version
- 365
- Platform
- Windows
I have a macro which currently deletes unused worksheets from a workbook. There is also a Table of Contents worksheet with the name and references of all the associated worksheets in Column A. What I'm trying to do is delete rows by value from the "Table of Contents" worksheet if the worksheet with the related name does not exist anymore. I found the following code which deletes sheets that are not in a list - which is kinda the opposite of what I need. I'm trying to see if there is an easy way of accomplishing this or perhaps reengineering the attached code to remove entire rows from the "Table of Contents" by value if the worksheet doesn't exist anymore. Any help would be greatly appreciated. Thanks!
VBA Code:
Sub Deletenotinlist()
'Updateby Extendoffice
Dim i As Long
Dim cnt As Long
Dim xWb, actWs As Worksheet
Set actWs = ThisWorkbook.ActiveSheet
cnt = 0
Application.DisplayAlerts = False
For i = Sheets.Count To 1 Step -1
If Not ThisWorkbook.Sheets(i) Is actWs Then
xWb = Application.Match(Sheets(i).Name, actWs.Range("A2:A6"), 0)
If IsError(xWb) Then
ThisWorkbook.Sheets(i).Delete
cnt = cnt + 1
End If
End If
Next
Application.DisplayAlerts = True
If cnt = 0 Then
MsgBox "Not find the sheets to be seleted", vbInformation, "Kutools for Excel"
Else
MsgBox "Have deleted" & cnt & "worksheets"
End If
End Sub