CODEWITHSUNDEEP
vbaexcel
studiis
studiis

Reputation: 95

Excel VBA Range.Value type mismatch?

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

Upvotes: 0

Views: 18633

Answers (3)

TheSilkCode
TheSilkCode

Reputation: 366

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.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 2

PaulG
PaulG

Reputation: 1189

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
....

Upvotes: 1

Related Questions