Delete range when delete a row

[hajiali]

Hello everyone,

is there a way to delete a range in a row when row is being deleted.

ex: if user right click on row to delete say row 15 or use the delete row to only delete range("A15:K15") delete with shift up.

or for multiple

if user select and right click on row 15-20 to only delete range("A15:K20") delete with shift up.

any help or suggestions is greatly appreciate

 

[Snakehips]

@hajiali Can you have a dedicated button or shortcut to run like the below when wishing to do just the partial delete?

Sub DelFK()
'Works with rows of selection whether full rows or single cells
FRw = Selection.Row
LRw = FRw + Selection.Rows.Count - 1
 
  Range("F" & FRw & ":K" & LRw).Delete Shift:=xlUp
    
End Sub

 

[jolivanes]

You mentioned "Select" so select a Cell or a Range in Column A and run this.

Sub Or_So()
    Selection.Resize(, 11).Delete Shift:=xlUp
End Sub

 

[hajiali]

@hajiali Can you have a dedicated button or shortcut to run like the below when wishing to do just the partial delete?

Sub DelFK()
'Works with rows of selection whether full rows or single cells
FRw = Selection.Row
LRw = FRw + Selection.Rows.Count - 1
 
  Range("F" & FRw & ":K" & LRw).Delete Shift:=xlUp
   
End Sub

I do have a code with Keyboard short cut, however, not everyone is using it and are just used deleting the old way. So I want a way to either have it auto run the macro when right click or using delete from the menu or change the way it delete an row.

 

[Alex Blakenburg]

In your previous thread, you are using a table and sheet protection. Is that the case here ?
If you are using protection you could block the deletion of a row.
If you are using a table what is the name of the table ?
PS: you only talk about deletion but insertion would have the same issue.

 

[hajiali]

It

In your previous thread, you are using a table and sheet protection. Is that the case here ?
If you are using protection you could block the deletion of a row.
If you are using a table what is the name of the table ?
PS: you only talk about deletion but insertion would have the same issue.

not using a table or sheet protection in this sheet.

I figured if I can get deletion I would be able to figure out Insertion.

 

[Alex Blakenburg]

In a copy of your workbook put this in the code module of the sheet you are trying to manage the delete and insert on.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastRow As Long, prevLastRow As Long
    Dim mvmtRow As Long, TargetRow As Long
    
    Application.EnableEvents = False
    
    If Target.Address = Target.EntireRow.Address Then
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
        TargetRow = Target.Row
        Application.Undo
        prevLastRow = Range("A" & Rows.Count).End(xlUp).Row
        
        mvmtRow = lastRow - prevLastRow
        If mvmtRow > 0 Then
            ' if row insert happened
            Range("A" & TargetRow & ":K" & TargetRow).Resize(mvmtRow).Insert Shift:=xlDown
        ElseIf mvmtRow < 0 Then
            Range("A" & (TargetRow - 1) & ":K" & TargetRow).Resize(Abs(mvmtRow)).Delete Shift:=xlUp
        Else
            ' Do nothing for now
        End If
    End If
    Application.EnableEvents = True

End Sub