Delete Worksheet if 'text' is found within it

[leeksleeks]

Hi,

I would like a macro to delete my worksheet called 'Scorecard' if the word 'abandoned' is found within it. The current macro imports web data but if it imports one with the word 'abandoned' on it I would like the whole worksheet (Scorecard) to be deleted.

Let me know if you need anymore information.

Cheers

 

[JoeMo]

Hi,

I would like a macro to delete my worksheet called 'Scorecard' if the word 'abandoned' is found within it. The current macro imports web data but if it imports one with the word 'abandoned' on it I would like the whole worksheet (Scorecard) to be deleted.

Let me know if you need anymore information.

Cheers

Assuming there is at least one sheet besides the Scorecard sheet in your workbook:

Sub DeleteIfAbandoned()
Const fWhat As String = "abandoned"
Dim fR As Range
If ActiveWorkbook.Sheets.Count = 1 Then Exit Sub
With ActiveWorkbook.Sheets("Scorecard").UsedRange
    Set fR = .Find(fWhat, .Cells(1, 1), xlFormulas, xlPart)
    If Not fR Is Nothing Then
        Application.DisplayAlerts = False
        Sheets("Scorecard").Delete
        Application.DisplayAlerts = True
        Exit Sub
    End If
End With
End Sub

 

[Rick Rothstein]

Assuming there is at least one sheet besides the Scorecard sheet in your workbook:

Sub DeleteIfAbandoned()
Const fWhat As String = "abandoned"
Dim fR As Range
If ActiveWorkbook.Sheets.Count = 1 Then Exit Sub
With ActiveWorkbook.Sheets("Scorecard").UsedRange
    Set fR = .Find(fWhat, .Cells(1, 1), xlFormulas, xlPart)
    If Not fR Is Nothing Then
        Application.DisplayAlerts = False
        Sheets("Scorecard").Delete
        Application.DisplayAlerts = True
        Exit Sub
    End If
End With
End Sub

You can simplify your code slightly by using the worksheet function's COUNTIF function instead of the range's Find function (I'm guessing the total amount of data on a scorecard would be small enough that any time difference between your method and mine would be negligible)...

Sub DeleteIfAbandoned()
  Const fWhat As String = "abandoned"
  If ActiveWorkbook.Sheets.Count = 1 Then Exit Sub
  With ActiveWorkbook.Sheets("Scorecard").UsedRange
    If Application.CountIf(.Cells, fWhat) Then
      Application.DisplayAlerts = False
      Sheets("Scorecard").Delete
      Application.DisplayAlerts = True
      Exit Sub
    End If
  End With
End Sub

 

[JoeMo]

You can simplify your code slightly by using the worksheet function's COUNTIF function instead of the range's Find function (I'm guessing the total amount of data on a scorecard would be small enough that any time difference between your method and mine would be negligible)...

Sub DeleteIfAbandoned()
  Const fWhat As String = "abandoned"
  If ActiveWorkbook.Sheets.Count = 1 Then Exit Sub
  With ActiveWorkbook.Sheets("Scorecard").UsedRange
    If Application.CountIf(.Cells, fWhat) Then
      Application.DisplayAlerts = False
      Sheets("Scorecard").Delete
      Application.DisplayAlerts = True
      Exit Sub
    End If
  End With
End Sub

As it turns out, I had just replied to another post that needed the Range.Find Method and it was easiest (meaning less typing) to take the existing code and modify it. I use the worksheet function Countif frequently in VBA and find it to be vary fast so I agree your approach would produce comparable results.

 

[leeksleeks]

That works perfectly. Thank you very much for your help. You guys are amazing!!!