Deleting several cells at once causes a VBA error

[nem]

Hello group,

here is the code. This is how it works,

when a cell in the range G12:G511 is manually modified, some valors
(the result from a substraction and the current date and time) are
written in contiguous cells, when a valor is deleted from any cell in
that range, the other two valors are deleted automatically. The code
works fine until you try to delete 2 or more cells in the range, at
once. If you try, an error window will pop asking for the finishing of
the macro or to debug it.

Hopping someone can help me with this.
Thank you!


Private Sub Worksheet_Change(ByVal Target As Range)
With Application
If .Intersect(Target, Me.Range("G12:G511")) Is Nothing Then
Exit Sub

If Target.Offset(, -4) = 0 Then
.EnableEvents = False
Target.Offset(, -4) = Now
.EnableEvents = True
End If

If Len(Target.Offset(0, 0)) = 0 Then
.EnableEvents = False
Target.Offset(, -4) = ""
.EnableEvents = True
End If

If Target.Offset(0, 0) <> 0 Then
Me.Cells(4, 7).Value = (Me.Cells(4, 4).Value -
Target.Offset(1, -5).Value)
End If

If Target.Offset(0, 0) = 0 Then
Me.Cells(4, 7).Value = (Me.Cells(4, 4).Value -
Target.Offset(, -5).Value)
End If

End With
End Sub

 

[paul.robinson]

Hi
your code refers to the value of the target
e.g. If Target.Offset(, -4) = 0

but you are trying to delete two cells at once so that
ITarget.Offset(, -4) is two cells and does not have one value.
I have not tested it, but maybe you could try

If Target.Cells(1,1).Offset(, -4) = 0

which is now only one cell.
alternatively you could do a test like
if Target.count>1 then
MsgBox "Please delete one cell only!"
Exit sub
end if

regards
Paul

 

[nem]

Thank you, didn't know that, I will try.

Also, I'd like to know what's the command in VBA to get the row number
from a cell.

 

[paul.robinson]

Hi
Cell.Row

will give you the absolute row number

Range.Row

will give you the row number of the first cell in range
eg Range("G12:G511").row is 12

Range("G12:G511").Cells(3,1).Row will give you the absolute row number
of Cells(3,1) in Range
eg Range("G12:G511").Cells(3,1).Row is 14

If you want the row number relative to the first row in the Range use,
for example,
Range("G12:G511").Cells(3,1).Row -Range("G12:G511").Row + 1

which is 14 - 12 + 1 = 3

Highlight Row in the VBE and hit F1 to see the type etc of Row

regards
Paul