Run time error 13 on deleting multiple rows in Excel using VBA

Question

I have a macro which is as follows:

Dim oval

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = Null Then Exit Sub

Application.EnableEvents = False
oval = Target.Value
MsgBox Target.Count

Application.EnableEvents = True
End Sub

When I run the following macro and when I select multiple rows and then delete it, it throws the error below. It only happens for multiple rows. I also tried to add a condition which says If Target.Count > 1 Then Exit Sub and that didn't seem to fix it as well.

Microsoft Visual Basic

Run-time error '13':

Type mismatch

Try replacing Null with vbNullString

Moacir
– Moacir

2016-12-29 17:18:27 +00:00
Commented Dec 29, 2016 at 17:18 — Moacir
– Moacir, Commented Dec 29, 2016 at 17:18
Tried it. Doesn't work.

p0tta
– p0tta

2016-12-29 17:22:05 +00:00
Commented Dec 29, 2016 at 17:22 — p0tta
– p0tta, Commented Dec 29, 2016 at 17:22

NikT · Accepted Answer · 2016-12-29 17:33:38Z

2

Target.Value is an array when you select multiple cells, so you can't check it's "value" To check the value of the first item you could do something like (for testing):

Debug.Print Target.Value()(1,1)

You can check this with IsArray, and if you want to just end if it's an array then

If IsArray(Target.Value) Then Exit Sub

It looks like you want the count though, so maybe something like:

Dim oval

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then
    MsgBox Target.Count
Else
    If Target.Value = vbNullString Then Exit Sub
    oval = Target.Value
    MsgBox Target.Count
End If
End Sub

although I'm sure you want to do something with oval ... I'll leave that up to you though.

edited Dec 29, 2016 at 17:33

answered Dec 29, 2016 at 17:24

NikT

2,0704 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Porcupine911 Over a year ago

Not sure what the intent of his code is but simply If Target.Count > 1... might do the trick, just a little bit more simply.

Shai Rado · Accepted Answer · 2016-12-29 17:33:15Z

1

Change your code from Worksheet_SelectionChange event to Worksheet_Change event.

Also, multiple cells will be only once you delete multiple cells, so there's no need for the second If Target.Value = "" Then Exit Sub. Unless you don't want oval to have an "empty" value.

Code

Dim oval

Public Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

' this line might be redundant
'If Target.Value = "" Then Exit Sub

Application.EnableEvents = False    
oval = Target.Value    

Application.EnableEvents = True

End Sub

answered Dec 29, 2016 at 17:33

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

1 Comment

p0tta Over a year ago

I had to use both the SelectionChange and the Change events but this was a good solution as well. Thanks.

Collectives™ on Stack Overflow

Run time error 13 on deleting multiple rows in Excel using VBA

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related