Using offset In vba

[hajiali]

Range(Replace("B#:D#,M#:W#,AH#,AK#:FE#", "#", Target.Row)).ClearContents
      ActiveWorkbook.Worksheets("EMPLOYEE LIST").AutoFilter.Sort.SortFields.Clear
      ActiveWorkbook.Worksheets("EMPLOYEE LIST").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A1:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("EMPLOYEE LIST").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

the above code is part of a double click on range"A2:A1000") when I double click on cell A16 then Range("B16:D16,M16:W16,AH16,AK16:FE16") values are deleted. I want to add the function to also include offset (1 , 0) of BJ#:FE#, 1 row down from BJ#:FE# so using the above if A16 is double clicked I would like range ("B16:D16,M16:W16,AH16,AK16:FE16",BJ17:FE17) delete value

if A30 is double clicked

then range would be ("B30:D30,M30:W30,AH30,AK30:FE30",BJ31:FE31).ClearContents

any help is greatly appreciated. Thanks

 

[jasonb75]

Try changing the first line to

Intersect(Target.EntireRow.Resize(2),Range("B:D,M:W,AH:AH,AK:FE")).ClearContents

 

[Fluff]

My take on this

Range(Replace(Replace("B#:D#,M#:W#,AH#,AK#:FE#,BJ¬:FE¬", "#", Target.Row), "¬", Target.Offset(1).Row)).ClearContents