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! :)

+1 well formatted and clear question.

nutsch
– nutsch

2012-10-03 20:06:13 +00:00
Commented Oct 3, 2012 at 20:06 — nutsch
– nutsch, Commented Oct 3, 2012 at 20:06

Daniel · Accepted Answer · 2012-10-03 20:28:13Z

6

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.

edited Oct 3, 2012 at 20:28

answered Oct 3, 2012 at 20:18

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mike Kellogg Over a year ago

this was the best solution for me since I wanted to use a For Each loop and I don't have to handle deleting empty cells within the loop itself. Thanks Daniel

Doug Glancy Over a year ago

That is the best solution, although you want to check that there are blank cells first. Otherwise SpecialCells will cause an error.

nutsch Over a year ago

+1, use if application.worksheetfunction.countif(theRange,"")>0 then theRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete to avoid the issue raised by Doug.

Mike Kellogg Over a year ago

@DougGlancy if there are no empty cells in the worksheet it will produce an error?

Daniel Over a year ago

@DougGlancy Good point, I keep forgetting that. I added in some perfunctory error trapping.

|

nutsch · Accepted Answer · 2012-10-03 20:14:16Z

3

I assume you only care about theCell if it is not empty. Here's an update to your code, adding an Else to your If structure

Dim theRange As Range, lLastRow as long, lRowLoop as long
    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)
lLastRow =cells(rows.count,1).end(xlup).row

For lRowLoop=lLastRow to 2 step-1 'from last row to first row

    set theCell=cells(lRowLoop,1)

    If theCell.Value = "" Then

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

    Else 

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

    End If

edited Oct 3, 2012 at 20:14

answered Oct 3, 2012 at 20:05

nutsch

5,9622 gold badges23 silver badges36 bronze badges

4 Comments

Mike Kellogg Over a year ago

This did work, in avoiding an object error. I ended up putting my end if after all the loop code right before my Next in order to skip that loop iteration and go to the next. However, after a row is deleted, one row is skipped (i guess because the rows are shifting up) how can this be avoided?

nutsch Over a year ago

You can start from the bottom and go up, but you can't do that in a for each loop. Updating my code to show this.

Mike Kellogg Over a year ago

I have gone from the bottom before using different loops, but what would be a solution to that problem in this case?

Doug Glancy Over a year ago

Yes it would. For/Next must use an index internally or something that results in the same behavior as using an index. You need to go from the bottom up.

Collectives™ on Stack Overflow

ForEach loop object required error

2 Answers 2

7 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related