Error Message: Run-time 13, Type Mismatch

[slabs]

I have a macro than manages a dynamic drop down list from a input table. When I delete one input value at a time, no error message. But when I delete a range of inputs at once the attached error message pops up. By clicking "End" one can continue to work, but I do not want to see the message. Do I need to add more code for this occurrence?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

' $D$30 is the Company Dropdown
' $E$30 is the Product Dropdown
' $F$30 is the Range Dropdown

If Intersect(Target, Range("$D$30:$G$64")) Is Nothing Then Exit Sub

If Target.Value <> "" Then

Select Case Left(Target.Address, 2)

Case "$D" ' The company dropdown is changed
Worksheets("Dropdown").Range("$D$1") = Target.Value ' Set the filter on the company pivot table
Worksheets("Dropdown").Range("$G$1") = Target.Value
Worksheets("Dropdown").Range("$J$1") = Target.Value

Case "$E" ' The product dropdown is changed
Worksheets("Dropdown").Range("$G$2") = Target.Value ' Set the filter on the product pivot table
Worksheets("Dropdown").Range("$J$2") = Target.Value

Case "$F" ' The range dropdown is changed
Worksheets("Dropdown").Range("$J$3") = Target.Value ' Set the filter on the range pivot table

Case "$G"
If Cells(Target.Row, "R") <> "" Then
MsgBox Cells(Target.Row, "R").Value
End If

End Select

End If

End Sub

[DBY]

Hello
You can try adding and extra statement to your opening IF:

Please Login or Register  to view this content.

This should prevent the error when the Target has more than 1 cell selected.

DBY

[AlphaFrog]

This will loop through the selected cells within $D$30:$G$64 (not tested)

Please Login or Register  to view this content.

[slabs]

Thanks!! Tried first solution from DBY and it works