Home VBA code to prevent certain cell range from being edited but can still delete rows when needed

Questions

VBA code to prevent certain cell range from being edited but can still delete rows when needed

July 9, 2024

I have a shared excel sheet that is essentially a table where it should not go beyond column X. I found this VBA code to make it so that anything from range Y1:XFD1048576 cannot have any information or data added to it, but now I cannot delete any rows in the table when needed. How do I fix this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range(“Y1:XFD1048576”),Target) Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

I tried finding ways to edit the code but to no avail. Haven’t really been able to find anything for this specific problem.

>Solution :

This might do what you need:

The modified code now checks if the the cells being changed are entire rows (i.e. you’re deleting or adding a row) and if it is, does not perform the undo.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("Y:XFD"), Target) Is Nothing _
      And Not Target.EntireRow.Address = Target.Address Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub