Excel VBA Range.Value type mismatch?

Question

I've written code to delete a row if a cell in column G has the value " - ". However, I'm getting a type mismatch when referring to the cell in the for each loop. I know the problem is coming from "curr.Value," but I don't know how to fix it. Any ideas?

Here's my code:

Sub deleteRows()
Dim curr As Range
Dim ws As Worksheet
Set ws = Workbooks("ExtractedColumns").Sheets("practice")
For Each curr In
    Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")
     If curr.Value = " - " Then
        EntireRow.Delete
     End If
Next
End Sub

Have you tried printing the value of curr when it gives you the type mismatch error? — ARich
– ARich, Commented Feb 3, 2017 at 20:32

TylerH · Accepted Answer · 2023-02-22 21:23:34Z

2

First, when you loop:

For Each curr In
    Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")

the syntax suppose to be in 1 line:

For Each curr In Workbooks("ExtractedColumns").Sheets("practice").Range("G:G")

(unless you had - between the 2 lines of code, or you didn't share your code as you have it).

However, you already assigned Workbooks("ExtractedColumns").Sheets("practice") to ws in the previous line, so why not use For Each curr In ws.Range("G:G")?

Furthermore, looping through your entire column G will take forever, loop through only occupied cells in Column G, with:

For Each curr In ws.Range("G1:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)

2nd reason, once you find the line where curr.Value = " - ", and you want to delete that entire row, you need to use curr.EntireRow.Delete.

And last, like mentioned by @Comintern, you need to trap cells with errors, you don't need to assign another variable to the curr.Value, just use If IsError(curr.Value) Then.

Code

Sub deleteRows()

Dim curr As Range
Dim ws As Worksheet

Set ws = Workbooks("ExtractedColumns").Sheets("practice")

' loop through only occupied cells in Column G
For Each curr In ws.Range("G1:G" & ws.Cells(ws.Rows.Count, "G").End(xlUp).Row)
    On Error Resume Next
    If IsError(curr.Value) Then ' check if cell.value return an error
        If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
        End If
    Else ' current cell.value doesn't return an error
        If curr.Value = " - " Then
            curr.EntireRow.Delete '<-- delete entire row
        End If
    End If
Next curr

End Sub

edited Feb 22, 2023 at 21:23

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 4, 2017 at 8:25

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

4 Comments

PaulG Over a year ago

To say there is no need a add a reference variable is a little pedantic. It was merely something that would explain to him what the error was and for him to do something about the error.

Shai Rado Over a year ago

@PaulG it's not pedantic, it's just not the reason for the error, and now -1 my answer (which has no error) :)

studiis Over a year ago

@ShaiRado Thanks for your answer. I just got around to running it, and I got a runtime error (object variable or with-block variable not set) and it stems from the "for each" line.

studiis Over a year ago

@ShaiRado I just set curr as a Variant instead of a Range and changed the range in the For Each line to ws.Range("G:G"), and the macro ran, but didn't do anything to my spreadsheet at first. After I ran it three times, it worked and deleted the rows with " - " in column G.

PaulG · Accepted Answer · 2017-02-03 20:32:27Z

1

You could try to assign the Value to a variant and checking the type.

Dim vVal as Variant


.....

vVal = curr.Value
if IsError(vVal) then 

etc
....

answered Feb 3, 2017 at 20:32

PaulG

1,2078 silver badges9 bronze badges

1 Comment

Shai Rado Over a year ago

sorry, but the reason for the errors are because he in not trapping cells with errors, no need to assign an extra variable. He can just just If IsError(curr.Value) Then

TylerH · Accepted Answer · 2023-02-22 21:23:45Z

0

I suspect the type mismatch error is being caused by curr containing a numeric value, being compared to a string... hard to know for sure without being able to see the rest of the code and how curr is populated but try using curr.text which returns a string for your If test, instead of curr.value.

edited Feb 22, 2023 at 21:23

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Feb 4, 2017 at 8:32

TheSilkCode

3663 silver badges11 bronze badges

Collectives™ on Stack Overflow

Excel VBA Range.Value type mismatch?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related