debug message pops up when I select a range of cells and delete
 

Hello,
When i select a range of cells and hit delete, a that debug message appears.
The range in question is controlled by the following sytax in VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and delete, but
how can I prevent the debug message from appearing.

debug message pops up when I select a range of cells and delete
 

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Brian" wrote in message
...
Hello,
When i select a range of cells and hit delete, a that debug
message appears.
The range in question is controlled by the following sytax in
VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and
delete, but
how can I prevent the debug message from appearing.

debug message pops up when I select a range of cells and delet
 

run time error 13
type mismatch

"Chip Pearson" wrote:

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Brian" wrote in message
...
Hello,
When i select a range of cells and hit delete, a that debug
message appears.
The range in question is controlled by the following sytax in
VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and
delete, but
how can I prevent the debug message from appearing.

debug message pops up when I select a range of cells and delet
 

I suspect an error in a cell.

As an experiment, I entered =1/0 into a cell to give me #DIV/0
Then tried MsgBox Len(Selection)
This gave me Run-time Error 13

You can check for an error:
iserror(selection)

Keep in mind your Target may span many cells, not just one.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian" wrote in message
...
run time error 13
type mismatch

"Chip Pearson" wrote:

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Brian" wrote in message
...
Hello,
When i select a range of cells and hit delete, a that debug
message appears.
The range in question is controlled by the following sytax in
VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and
delete, but
how can I prevent the debug message from appearing.

debug message pops up when I select a range of cells and delet
 

OP doesnt check that Target.Count =1

if the target contains multiple cells:
Len(target) will produce runtime error.
whereas len(target.cells(1)) would not.

normally i'd include
if Target.Count < 1 then exit sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rob van Gelder wrote :

I suspect an error in a cell.

As an experiment, I entered =1/0 into a cell to give me #DIV/0
Then tried MsgBox Len(Selection)
This gave me Run-time Error 13

You can check for an error:
iserror(selection)

Keep in mind your Target may span many cells, not just one.

debug message pops up when I select a range of cells and delete
 

This line:
If Len(Target) 0 Then
is the same as:
If Len(Target.value) 0 Then

But with multiple cells, target doesn't have a value.

So what should happen when you clean up a multicell range?

One easy solution is to add a check for multiple cells at the top:

if target.cells.count 1 then exit sub

or you could loop through the cells that are in that area that you're concerned
with:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("H10:H12")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each myCell In Intersect(Target, myRng).Cells
If IsError(myCell.Value) Then
'do nothing??
ElseIf IsNumeric(myCell.Value) = False Then
If Len(myCell.Value) 2 Then
myCell.Offset(0, -2).Value = ""
End If
End If
Next myCell

Application.EnableEvents = True

End Sub

Personally, I like checking the intersection to see if the change is happening
in a range. I find it easier to read than checking the .row and .column.



Brian wrote:

Hello,
When i select a range of cells and hit delete, a that debug message appears.
The range in question is controlled by the following sytax in VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and delete, but
how can I prevent the debug message from appearing.

--

Dave Peterson

debug message pops up when I select a range of cells and delet
 

len(Range("B1:B2"))

produces type mismatch

(len with a multi-cell argument).

If you want to process multiple cells

for each cell in Target
If cell.Row < 10 Or cell.Row 13 Then Exit Sub
Application.EnableEvents = False
If cell.Column = 8 Then
If IsNumeric(cell) = False Then
If Len(cell) 0 Then
cell.Offset(0, -2).ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Rob van Gelder" wrote in message
...
I suspect an error in a cell.

As an experiment, I entered =1/0 into a cell to give me #DIV/0
Then tried MsgBox Len(Selection)
This gave me Run-time Error 13

You can check for an error:
iserror(selection)

Keep in mind your Target may span many cells, not just one.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Brian" wrote in message
...
run time error 13
type mismatch

"Chip Pearson" wrote:

What does the message say?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Brian" wrote in message
...
Hello,
When i select a range of cells and hit delete, a that debug
message appears.
The range in question is controlled by the following sytax in
VB:

If target.Row < 10 Or target.Row 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and
delete, but
how can I prevent the debug message from appearing.