Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting several cells at once causes a VBA error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting several cells at once causes a VBA error
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.count1 then MsgBox "Please delete one cell only!" Exit sub end if regards Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting several cells at once causes a VBA error
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting several cells at once causes a VBA error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Deleting Columns | Excel Discussion (Misc queries) | |||
Deleting Worksheet Error | Excel Programming | |||
Error when deleting sheets | Excel Programming | |||
error runtime 9: deleting a worksheet | Excel Programming | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |