Delete value of range of row double clicked and range of next row

[hajiali]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim resp As VbMsgBoxResult
 
  If Not Intersect(Target, Columns("B")) Is Nothing Then
    Cancel = True
    resp = MsgBox(Prompt:="THIS WILL REMOVE EMPLOYEE FROM THE ROSTER. DO YOU WISH TO CONTINUE?", Buttons:=vbYesNoCancel)
    If resp = vbYes Then
      Range(Replace("B#:D#,F#,H#:EB#", "#", Target.Row)).ClearContents
       ActiveWorkbook.Worksheets("DATS").AutoFilter.Sort.SortFields.Clear
       ActiveWorkbook.Worksheets("DATS").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A1:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DATS").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
End If
End If
End Sub

I use the above code when double clicking it deletes the value of the range of that row. I want to be able to add to the range the row after the double click on from AG-EB.

ex: if i double click B3 the values in range B3-D3, F3, H3-EB3, AG4-EB4 will clear contents. adding the range in Green

 

[Fluff]

Add this line

Intersect(Target.Offset(1).EntireRow, Range("AG:EB")).ClearContents

 

[hajiali]

Thanks Fluff. much appreciate.

 

[Fluff]

You're welcome & thanks for the feedback