CODEWITHSUNDEEP
excelvbaloops
Josh Marchini
Josh Marchini

Reputation: 1

I keep getting a "Type Mismatch" error when I try to Loop through a range of cells

I want to delete an entire column if the header in cells B5:P5 contains specific text.

Here is the code I have so far:

Sub Make_Prod()

Sheets("4. Partic Charges - Final").Select
Dim i As Long
Dim StartCell As Range
Dim EndCell As Range
Dim MyValue As Range
Set StartCell = Range("B5")
Set EndCell = StartCell.End(xlToRight)

'Delete IID, LastName, FirstName, SSN, DOB, DOT, VestBal, TotalSourceDH, HasCovg, and any blank headers with values in column

For i = StartCell To EndCell
    MyValue = Range(i & 5)
        If MyValue = "IID" _
        Or MyValue = "LastName" _
        Or MyValue = "FirstName" _
        Or MyValue = "DOB" _
        Or MyValue = "DOT" _
        Or MyValue = "HasCovg" _
        Or MyValue = "" _
    Then Range(5 & i).EntireColumn.Delete

Next i
    
End Sub

Every time I try to run the code, I get a

Type Mismatch

error on my i = StartCell to EndCell statement.

Can anyone help me with this?

Upvotes: 0

Views: 50

Answers (1)

SJR
SJR

Reputation: 23081

The immediate cause of your error was as Nathan_Sav points out that you are trying to loop through a range using a number.

Try this. As you're deleting columns you need to iterate backwards to avoid skipping cells. Set the range (which you could do in one line) and then use a counter variable to loop through each cell.

You don't need Select Case, you can stick with If but in my view is a little clearer.

Sub Make_Prod()

Sheets("4. Partic Charges - Final").Select
Dim i As Long
Dim StartCell As Range
Dim EndCell As Range
Dim MyValue As Range
Set StartCell = Range("B5")
Set EndCell = StartCell.End(xlToRight)

'Delete IID, LastName, FirstName, SSN, DOB, DOT, VestBal, TotalSourceDH, HasCovg, and any blank headers with values in column
Dim r As Range, c As Long

Set r = Range(StartCell, EndCell)

For c = r.Count To 1 Step -1
    Select Case r.Cells(c).Value
         Case "IID", "LastName", "FirstName", "SSN", "DOB", "DOT", "VestBal", "HasCovg", ""
            r.Cells(c).EntireColumn.Delete
    End Select
Next c

End Sub

Upvotes: 3

Related Questions