VBA to delete certain sheets based on cell contents with IF formula

[bakarken]

Hi guys

Hoping you can help!

I've got an Excel 2019 spreadsheet with sheets:

Setup
Sheet1
Sheet2
Sheet3

In the Setup sheet, column A contains the names of other sheets in the workbook, and column B may or may not say 'DEL' next to each of these sheet names, based on IF formulas (see example image).

I'd like a VBA where, if column B says 'DEL' next to a certain sheet(s), that those sheet(s) are deleted using a developer button 'Delete sheets'. Preferably once clicked, no warning appears and sheets are just deleted.

I'm happy with adding in the Developer button and assigning a macro to it, just not sure what the Macro needs to say to achieve this.

In the future, I'd like to add more sheets and just list these down column A/B of Setup, so if this VBA could apply to the whole columns A:B please that would be great (rather than just cells A1:B3)

Thanks in advance!!

 

[Peter_SSs]

Give this a try with a copy of your workbook.

Sub Del_Sheets()
  Dim r As Long
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  With Sheets("Setup")
    For r = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
      If .Cells(r, "B").Value = "DEL" Then
        On Error Resume Next
        Sheets(.Cells(r, "A").Value).Delete
        On Error GoTo 0
        .Rows(r).Delete '<-This deletes the row from 'Setup'
      End If
    Next r
  End With
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

True
End Sub

 

[bakarken]

Give this a try with a copy of your workbook.

Sub Del_Sheets()
  Dim r As Long
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  With Sheets("Setup")
    For r = .Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
      If .Cells(r, "B").Value = "DEL" Then
        On Error Resume Next
        Sheets(.Cells(r, "A").Value).Delete
        On Error GoTo 0
        .Rows(r).Delete '<-This deletes the row from 'Setup'
      End If
    Next r
  End With
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

True
End Sub

That's fantastic thanks so much Peter!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.