VBA auto sort multicolumn table when data is changed or deleted in protected sheet mode RRS feed

  • Question

  • I use the code to automatically sort the table by attribute "Name" from column B.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error Resume Next
        If Not Intersect(Target, Range("A:Z")) Is Nothing Then
            Range("B5").Sort Key1:=Range("B6"), _
            Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, _
        End If
    End Sub

    The first problem with this code is that when I delete a record after the first line remains an empty cell.
    The second problem is how to make the code work while sheet is password-protected.
    Protected password can be found in sheet PASS.

    This is the WeTransfer link to an Excel file:

    Wednesday, April 24, 2019 8:20 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Emi Zhang

    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, April 25, 2019 4:53 AM
  • Thanks for your help and sorry for the mistake.
    Thursday, April 25, 2019 5:04 AM