Type mismatch error with a if then statement

Question

I am trying to write a If then statement that cant take out the rows on my worksheet that have been declined or void. So I am trying to get it to look into a Column then delete the rows with those values.

If Columns("K:K") = "Declined" Or "Void" Then

    Selection.Delete Shift:=x1Up

End If

You can't compare an entire column to a `String`. Perhaps filter on that column and then delete visible rows. — BigBen, Oct 30 '19 at 19:30
Use the macro recorder or look at similar questions here on SO about filtering and deleting. — BigBen, Oct 30 '19 at 19:35
Okay, so just have it auto filter the rows that have Decline and Void then you have to delete them manually? Is there a way to tell the macro to find the last row then stop because the amount of rows for this sheet changes everyday so I just have to set to a number that would never get reached, then I go back and undo it to the last row. — Broten Togstad, Oct 30 '19 at 19:43
See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Oct 30 '19 at 19:44
@Sorceri your answer is suggesting a way to get the last row that is unreliable, for reasons described at length in the post BigBen linked to. — Mathieu Guindon, Oct 30 '19 at 19:46
@MathieuGuindon yes, it can be unreliable depending on how the worksheet is setup. — Sorceri, Oct 30 '19 at 19:50
Also, I'm wondering why there are *two* answers that both inefficiently loop row-by-row :) — BigBen, Oct 30 '19 at 19:51
@Sorceri no, it's unreliable *period*. Please read the linked post, reasons why `UsedRange` is a bad idea are near the top of the answer. Really worth reading. — Mathieu Guindon, Oct 30 '19 at 19:52

score 0 · Answer 1 · answered Oct 30 '19 at 19:40

You'll instead need to loop through your range. One way to do it is:

Sub t()
Dim rng As Range, curCel As Range
Dim i As Long
Set rng = Range("K1:K100") ' Change as needed
For i = rng.Cells(rng.Rows.Count, 1).Row To rng.Cells(1, 1).Row Step -1
    Set curCel = Cells(i, rng.column)
    If curCel.Value = "Declined" or curCel.value = "Void" Then
        curCel.EntireRow.Delete
    End If
Next i
End Sub

score 0 · Answer 2 · edited Feb 02 '20 at 07:59

you can loop through your cells and remove the rows. I would fully qualify your sheet and try to avoid using ActiveSheet. In the example below we are going from the bottom to the top so not to revisit cells twice or adjust the increment counter.

Dim i As Long
Dim iUsedRange As Long

iUsedRange = ActiveSheet.UsedRange.Rows.Count
For i = iUsedRange To 1 Step -1
    If ActiveSheet.Cells(i, 11).Value = "Declined" Or ActiveSheet.Cells(i, 11).Value = "Void" Then
        ActiveSheet.Cells(i, 11).EntireRow.Delete Shift:=xlUp
    End If

Next i

Type mismatch error with a if then statement

2 Answers2