Delete sheet and related cell

[tropis]

Hi,
There is a list of names on sheet 1, in cells A9:A1048576. Each cell consists of one name. Each name has a correspondent sheet in the workbook and the name of this sheet is the same as a value of cell from the list in sheet1. Basically, there is a sheet for each name from the list on sheet 1.

Now. Each of these sheet has an button on it. When button is clicked, active sheet is deleted.


Sub DeleteSheet()
ActiveSheet.Delete
End Sub


So, sheet is gone but a name of this sheet still exists on the list on sheet1.
How to make it, to delete a sheet and at the same time find a cells within A9:A1048576 in sheet1 with a value of "name of sheet to be deleted" and delete an entire row of this cell.

Thanks in advance

 

[Yongle]

- assumes that the sheet with the names is the first sheet in the workbook

Sub DeleteSheet()
    Application.DisplayAlerts = False
    On Error Resume Next
    With ActiveSheet
        Sheets(1).Rows(WorksheetFunction.Match(.Name, Sheets(1).Columns(1), 0)).EntireRow.Delete
        .Delete
    End With
    Application.DisplayAlerts = True
End Sub

[B]Prevent the code failing if sheet name is not matched[/B]
    On Error Resume Next

[B]Suppress message warning that sheet about to be deleted [/B]
       Application.DisplayAlerts = False

 

[tropis]

- assumes that the sheet with the names is the first sheet in the workbook

Correct, sheet with the names is the first one. Names are in column A, cells A9:A1048576.
Code you offered deletes a sheet but it doesn't delete any row in sheet 1 so the name of the sheet still exists on the list.

Maybe I know the reason: names in sheet 1 are all hyperlinks (when name is clicked, it takes you to the sheet with the same name). So on the list with names, names are just displayed in cell, but a value of the cell is ex. =HIPERLINK("#'Adam'!A1";"Adam")

 

[tropis]

Ok, problem solved.
Sheet 1 was protected so I just added a code to unprotect it.
Thank you for help

The new problem is... I have removed:

Application.DisplayAlerts = False

from your code so it asks me for confirmation before deleting a sheet. It is ok but if I choose No, do not delete, the name in sheet 1 is already removed anyway. How to prevent it from happening ??

 

[Yongle]

Easier to control by adding our own message

Sub DeleteSheet()
    Dim msg As String: msg = "Miscrosoft Excel will permanently delete this sheet" & vbCr & "Are you sure you want to continue?"
    If MsgBox(msg, vbOKCancel) <> vbOK Then End
    Application.DisplayAlerts = False
    On Error Resume Next
    With ActiveSheet
        Sheets(1).Rows(WorksheetFunction.Match(.Name, Sheets(1).Columns(1), 0)).EntireRow.Delete
        .Delete
    End With
    Application.DisplayAlerts = True
End Sub