Excel VBA Type Mismatch on Row Delete

Question

I have a "simple" comparison that is generating a "Type mismatch" error when I delete a row in the worksheet (worksheet change event fires).

If Target.Column = gblScheduled And Target.Value = "Yes" Then

I've checked the documentation and the Column property is Integer.
I have a global constant with an Integer data type for that comparison.
I've checked the documentation and the Value property is a Variant.
As you can see, the literal would be a String value for that comparison.

VBA coercion should allow that. But, just in case, I did this:

If Target.Column = gblScheduled And Target.Value = CVar("Yes") Then

But that still resulted in a "Type mismatch". Why?

Do you also check Target.Cells.Count > 1 so that your code wont even try to run if there is more than one cell selected? Target can be more than one cell. — braX
– braX, Commented Nov 6 at 2:18
...and if Target has >1 cell then Target.Value is a 2D array, not a single value. — Tim Williams
– Tim Williams, Commented Nov 6 at 2:57
@braX repeating your point about vbnet vs vba for this question ... why does it say vbnet when the question is tagged vba? — MT1
– MT1, Commented Nov 6 at 8:22

braX · Accepted Answer · 2025-11-06 07:33:51Z

0

You will also want to check Target.Cells.Count > 1 or Target.Cells.CountLarge > 1 to see if more than one cell is included.

Target is a Range object, which can be one cell or multiple cells.

If Target has more than 1 cell, Target.Value is a 2D array, not a single value.

Another option would be to write it in a way that can handle any Range.

Try this test code and watch the immediate window when you click on one cell, or select multiples.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim c As Range
    For Each c In Target
      Debug.Print c.Address
    Next
End Sub

edited Nov 6 at 7:33

answered Nov 6 at 4:00

braX

11.9k5 gold badges24 silver badges38 bronze badges

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

6 Comments

braX Nov 6 at 6:21

Why does it say vbnet in the corner of my code instead of vba when i have it explicitly tagged as vba code?

braX Nov 6 at 6:50

For as often as people are told to pay attention to the difference between vbnet and vba it seems like this would be important to fix.

FunThomas Nov 6 at 6:38

Artificial "Intelligence"

Siddharth Rout Nov 6 at 7:14

Suggestion: Use Target.Cells.CountLarge > 1 instead of Target.Cells.Count > 1. Also, OP is using Worksheet_Change event.

braX Nov 6 at 7:16

The code was just to illustrate how to use a For Each using Target.

Siddharth Rout Nov 6 at 7:18

Yes I know. But my suggestion is not regarding For Each :)

MGonet · Accepted Answer · 2025-11-06 08:29:11Z

0

The Column property does not cause a problem when Target spans multiple cells, because then this property returns the number of the first column of the Target range.
The problem is caused by the Value property, because it returns an array in this case and causes a Type mismatch when compared to a single value. To avoid this problem, you can test
Target(1). Value = "Yes"
Then only one value is always taken into account.

answered Nov 6 at 8:29

MGonet

2,6102 gold badges3 silver badges9 bronze badges

Collectives™ on Stack Overflow

Excel VBA Type Mismatch on Row Delete

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related