CODEWITHSUNDEEP
excelvba
AKIRA
AKIRA

Reputation: 71

Delete row equal to worksheet name

I am planning to add a Delete button in each of my worksheets to allow users to delete 1 sheet at a time. But, before deleting the active worksheet (code below) I would like to, in another worksheet (Objects Stats), delete the entire row that contains the same name as the worksheet to be deleted.

Other worksheet name: Objects Stats, ‘column headers up to row 3

Worksheet name is located in column B

Sub DeleteActiveSheet()

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

End Sub

So, what needs to happen is:

  1. run delete active worksheet code
  2. code finds the row within the Object Stats worksheet with the name of the worksheet to be deleted in column B
  3. code deletes the entire row
  4. finally, deletes the active worksheet

Upvotes: 0

Views: 69

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

Try this code:

Option Explicit

Sub DeleteActiveSheet()
    Const OBJ_STATS_NAME = "Object Stats", HEADER_ROW = 3
    Dim wsToDelete As Worksheet, OSWorksheet As Worksheet, row As Variant
    
    Set wsToDelete = ActiveSheet
    'check if ActiveSheet not the "Object Stats" and is in ThisWorkbook
    If wsToDelete.Name <> OBJ_STATS_NAME And wsToDelete.Parent Is ThisWorkbook Then
        Set OSWorksheet = ThisWorkbook.Worksheets(OBJ_STATS_NAME)
        row = Application.Match(wsToDelete.Name, OSWorksheet.Columns("B"), 0)
        If IsNumeric(row) Then
            If row > HEADER_ROW Then
                If MsgBox("You want to delete '" & wsToDelete.Name & "' from '" & ThisWorkbook.Name & "'?", _
                    vbExclamation + vbYesNo + vbDefaultButton2) = vbYes Then
                    OSWorksheet.Rows(row).Delete
                    Debug.Print "Deleted row # " & row & " from '" & OBJ_STATS_NAME & "' with value '" & wsToDelete.Name & "'"
                    Application.DisplayAlerts = False
                    wsToDelete.Delete
                    Application.DisplayAlerts = True
                End If
            End If
        Else
            MsgBox "You can't delete this Worksheet because it name not in '" & OBJ_STATS_NAME & "' list", vbCritical + vbOKOnly
        End If
    Else
        MsgBox "You can't delete this Worksheet by 'Sub DeleteActiveSheet()':" & vbLf & _
            "ActiveSheet is the 'Object Stats' or is not in ThisWorkbook", vbCritical + vbOKOnly
    End If
End Sub

Please note that deleting a sheet is an irreversible operation (especially after saving a workbook), so be careful and make backups of the original data. In this procedure, I have included a number of checks to avoid accidentally deleting sheets.

Upvotes: 1

Related Questions