Delete adjacent cell

Hi,

please see the .xls attached.

I have 4 coloumns each with a named range list.

Titles in Row 1. Contents in Row 2.

I want it so that whichever of the cells you select then DELETE OR CHANGE then all following cells become empty. If you change C2, then D2 becomes empty. If you change A2, then B2, C2 and D2 become empty.

I've added the following macro to your sheet change event code ...

Private Sub Worksheet_Change(ByVal Target As Range)

Const sNAMERANGE = "A2:D2"

Dim rngCellLoop As Range

If Not Intersect(Range(sNAMERANGE), Target) Is Nothing Then

  Application.EnableEvents = False
  
  For Each rngCellLoop In Range(sNAMERANGE).Cells
    If rngCellLoop.Column > Target.Column Then
      rngCellLoop.Value = ""
    End If
  Next rngCellLoop
  
  Application.EnableEvents = True
  
End If
  
End Sub

The updated workbook is attached.

That's perfect! Last question, what would I need to change here if I had more than one row? Ie, if I had rows 2,3,4,5 filled in?

Do you want only the cells on the same row as the deleted/updated cell to be cleared?

If so you could change the code to:

Private Sub Worksheet_Change(ByVal Target As Range)

Const sNAMERANGE = "A2:D5"

Dim rngCellLoop As Range

If Not Intersect(Range(sNAMERANGE), Target) Is Nothing Then

  Application.EnableEvents = False
  
  For Each rngCellLoop In Intersect(Range(sNAMERANGE),Rows(Target.Row)).Cells
    If rngCellLoop.Column > Target.Column Then
      rngCellLoop.Value = ""
    End If
  Next rngCellLoop
  
  Application.EnableEvents = True
  
End If
  
End Sub

pl see attached file.For sheet1 worksheetchange code added which meets your requirement.
Regards.

Originally Posted by kvsrinivasamurthy

code added which meets your requirement.

Not wishing to be rude, but you perhaps need to re-read the requirements.

Hi kvsrinivasamurthy,

can you put the code in the thread please? I can't download documents currently.