Delete rows by value if worksheet with matching name does not exist

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
69
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub rdouhaghsingh()
   Dim Cl As Range, Rng As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End If
   Next Cl
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub
 
Solution

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub rdouhaghsingh()
   Dim Cl As Range, Rng As Range
  
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Evaluate("isref('" & Cl.Value & "'!A1)") Then
         If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
      End If
   Next Cl
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub
That did the trick! Thank you so much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,211
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,136,954
Messages
5,678,754
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top