Trouble with a VBA Loop

Question

I am running a loop that is modifying copied data and scanning for unnecessary rows. I think the code is really close but I must have something out of place here:

Dim DeleteRowData As Long
Dim DeleteRowCounter As Long
DeleteRowData = Cells(Rows.Count, 3)


Set DataSheet = Sheets("Class Data")
Set TransType = DataSheet.Range(DataSheet.[C1], DataSheet.Cells(Rows.Count, "C").End(xlUp))
For DeleteRowCounter = DeleteRowData To 1 Step -1
If Cells(DeleteRowCounter, 3) = "TYP" Then
Rows(DeleteRowCounter).Delete
End If
Next

What is happening currently is that the macro is skipping this section of code even when there are lines that have "TYP" in column C

If Cells(DeleteRowCounter, 3) = "TYP" Then
Rows(DeleteRowCounter).Delete
End If

Can someone help me correct this?

Answer 1

1. This doesn't find the last row correctly:

   DeleteRowData = Cells(Rows.Count, 3)
   

   This returns the value of the very last cell in column C on the active sheet. That cell is likely empty, making DeleteRowData equal to 0. Therefore the loop never runs.

2. Specify the parent Worksheet for all Rows and Cells calls.

   Dim DeleteRowData As Long
   Dim DeleteRowCounter As Long
   
   Set DataSheet = Sheets("Class Data")
   With DataSheet
        DeleteRowData = .Cells(.Rows.Count, 3).End(xlUp).Row
   
        For DeleteRowCounter = DeleteRowData To 1 Step -1
             If .Cells(DeleteRowCounter, 3).Value = "TYP" Then
                 .Rows(DeleteRowCounter).Delete
             End iF
        Next
    End With
   

3. If "contains" means that TYP is part (but not necessarily all) of the cell content, then replace

   If .Cells(DeleteRowCounter, 3).Value = "TYP" Then 
   

   with

   If InStr(.Cells(DeleteRowCounter, 3).Value, "TYP") > 0 Then
   

   or

   If .Cells(DeleteRowCounter, 3).Value Like "*TYP*"