ForEach loop object required error

Question

Summary: I have a list of policy numbers I'm looping through in Column A using the for each loop

Problem: Everything works except if there is an empty cell in Column A, my code deletes the entire row (as it should), but then when I try to set the policy variable I get a object required error. I've marked in my code where the error occurs.

Question: How can I delete empty rows without theCell losing its object?

The Code:

Dim theRange As Range
    Dim theSheet As Worksheet
    Dim theDate As String, policy As String, amount As String, details As String, entryDate As String

    Set theSheet = Sheets("OneDate")
    Set theRange = Range("A2:A" & theSheet.UsedRange.Rows.Count)

    For Each theCell In theRange                'FOR EACH POLICY IN COLUMN "A"

        If theCell.Value = "" Then

            theCell.EntireRow.Delete      '<-- Row deleted here
            MsgBox (theCell.Value)

        End If

        policy = theCell.Value            '<-- Error occurs here
        theDate = theCell.Offset(0, 1).Value
        theDate = UCase(Format(theDate, "ddMMMyy"))

Thanks in Advance for any help! :)

Answer 1

Here's a different way to do what you want.

Leave out the loop. From previous experimentation if you are looping through rows using for, each time you delete a row you end up skipping the row after the one you deleted. Also, as you noted the range you delete can no longer be referenced because you deleted it.

To delete all of the blank rows based upon the first column update your code to this:

Dim theRange As Range
        Dim theSheet As Worksheet
        Dim theDate As String, policy As String, amount As String, details As String, entryDate As String

        Set theSheet = Sheets("OneDate")
        Set theRange = Range("A2:A" & theSheet.UsedRange.Rows.Count)
'Editted in some perfunctory error trapping incase of no blank rows.
on error resume next
debug.print theRange.SpecialCells(xlCellTypeBlanks).count
on error goto 0
if err.number = 0 then
    theRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end if

Once you've removed the blanks, THEN do your loop for the other checks.