Private Sub - Target Range to Exit Sub if Delete is Pressed

[sxhall]

Hi all,

I have written a short bit of VBA that runs fine for me but when a cell is selected and delete is pressed the code runs but on the cells and rows above it (Offset is used) not to the right as written. What I am trying to do is to get the sub to recognise when delete is pressed in the target range and to then just exit the sub!

Here's what I have so far but cannot get the if 'If Application.OnKey(Key:="{DEL}") Then' arguement to work.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Target.Worksheet.Range("$D$3:$D$65536")) Is Nothing Then
    
        If Application.OnKey(Key:="{DEL}") Then
    
        Exit Sub
        
    Else
    
    ActiveCell.Offset(-1, 1).Select
    
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(INDEX('SKU Data'!R2C[-3]:R33368C[-3],MATCH(RC[-1],'SKU Data'!R2C[-4]:R33368C[-4],0))),"""",INDEX('SKU Data'!R2C[-3]:R33368C[-3],MATCH(RC[-1],'SKU Data'!R2C[-4]:R33368C[-4],0)))"
    
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(SUMIF('SKU Data'!R1C[-5]:R29999C[-5],RC[-2],'SKU Data'!R1C[-5]:R29999C[-5])=0,""Not Known"",VLOOKUP(RC[-2],'SKU Data'!C[-5]:C[-2],4,FALSE)))"
    
    ActiveCell.Resize(, 2).Copy
    
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False

    Application.CutCopyMode = False
    
    ActiveCell.Offset(1, -2).Select

    End If

End If

End Sub

Thanks for looking and for any help in advance

Steven

 

[Joe4]

What I am trying to do is to get the sub to recognise when delete is pressed in the target range and to then just exit the sub!

Delete an entry clears out the cell. So try going about it differently, specifically checking for the empty string, like this:

If Target = "" Then

 

[sxhall]

Delete an entry clears out the cell. So try going about it differently, specifically checking for the empty string, like this:

If Target = "" Then

Thanks Joe4

Added in the line you gave me and worked a treat.

Just for anyone else who may happen upon this I have added in my amended code below.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Target.Worksheet.Range("$D$3:$D$65536")) Is Nothing Then
    
        If Target = "" Then
        
            ActiveCell.Offset(0, 1).Resize(, 2).ClearContents
            
        Else
    
            ActiveCell.Offset(-1, 1).Select
            
            ActiveCell.FormulaR1C1 = _
                "=IF(ISERROR(INDEX('SKU Data'!R2C[-3]:R33368C[-3],MATCH(RC[-1],'SKU Data'!R2C[-4]:R33368C[-4],0))),"""",INDEX('SKU Data'!R2C[-3]:R33368C[-3],MATCH(RC[-1],'SKU Data'!R2C[-4]:R33368C[-4],0)))"
            
            ActiveCell.Offset(0, 1).Select
            ActiveCell.FormulaR1C1 = _
                "=IF(RC[-2]="""","""",IF(SUMIF('SKU Data'!R1C[-5]:R29999C[-5],RC[-2],'SKU Data'!R1C[-5]:R29999C[-5])=0,""Not Known"",VLOOKUP(RC[-2],'SKU Data'!C[-5]:C[-2],4,FALSE)))"
            
            ActiveCell.Offset(0, -1).Select
            ActiveCell.Resize(, 2).Copy
            
                ActiveSheet.Select
                Selection.PasteSpecial Paste:=xlPasteValues, _
                Operation:=xlNone, _
                SkipBlanks:=False, _
                Transpose:=False
        
            Application.CutCopyMode = False
            
            ActiveCell.Offset(1, -1).Select

        End If

    End If

End Sub

If the user deletes the cells contents it will now also delete other cells linked to the information on the same row.

Thanks again

Steven