how to get a 'Cell' object in excel VBA

To get an object reference and not the value itself you need to use 'Set'

Set C = Worksheets("Blah").Cells(iRow, 2)

When you delete rows, it's always best to go from the bottom up. Your particular problem is that you need the Set keyword to set C to the range object rather than "Let" C to the range Value. Range's have a default property, Value, and when you omit a property that's the property that's used. For that reason, you have to use Set when assigning object variables. Here's an example of looping backward to delete rows and assigning an object variable.

Sub DeleteRows()

    Dim i As Long
    Dim rRng As Range
    Dim rCell As Range

    Set rRng = Sheet1.Range("A1:A9")

    For i = rRng.Rows.Count To 1 Step -1
        Set rCell = rRng.Cells(i, 1)
        If rCell.Value Mod 2 = 1 Then
            rCell.EntireRow.Delete
        End If
    Next i

End Sub

Tags:

Excel

Vba