CODEWITHSUNDEEP
excelmacrosvba
p0tta
p0tta

Reputation: 1651

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

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

Upvotes: 0

Views: 737

Answers (2)

NikT
NikT

Reputation: 1990

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.

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Related Questions