"Object required" error with VBA loop rows deleting

**JKWIN** · 07-22-2012, 08:21 AM

The macro below should remove rows with text in column "A" containing "1" digit(e.g. A1, AB1) and rows below it, if cell in column "A" is empty and cell in column "K" is not empty. When there is a row with "1" and one row below it- macro removes this row below, then the error appears: "object required" and a row with "1" is not removed. When there are only rows with "1" without these rows below - it is ok, the rows are removed.
Someone helps?

Sub planp()
Dim n
Dim eCell
Dim eCell1
Dim eCell2

For n = 64 To 1 Step -1

Set eCell = Worksheets("Arkusz4").Cells(n, 1)
Set eCell1 = Worksheets("Arkusz4").Cells(n + 1, 1)
Set eCell2 = Worksheets("Arkusz4").Cells(n + 1, 11)

If Right(eCell.Value, 1) = "1" Then

Do While eCell1.Value = "" And eCell2.Value <> ""
Worksheets("Arkusz4").Rows(n + 1).Delete
Loop
Worksheets("Arkusz4").Rows(n).Delete
End If
Next

End Sub

**tangangtanga** · 07-22-2012, 08:24 AM

@ts I suggest you attached the file so that the macro can easily be changed within the excel file you are using.

**JKWIN** · 07-22-2012, 08:53 AM

That's the file.

**Cutter** · 07-22-2012, 08:52 AM

Hello JKWIN, and welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules (Rule #3) require them so please keep that in mind and add them yourself whenever showing code in any of your future posts.
Thanks.

RULE 3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**tangangtanga** · 07-22-2012, 09:31 AM

hi kindly check this macro.. please inform me if this helps.

'Excel 2007
Sub planp()

Dim n
Dim eCell
Dim eCell1
Dim eCell2

For n = 10 To 1 Step -1

Set eCell = Worksheets("Arkusz4").Cells(n, 1)
Set eCell1 = Worksheets("Arkusz4").Cells(n + 1, 1)
Set eCell2 = Worksheets("Arkusz4").Cells(n + 1, 11)
If Right(eCell.Value, 1) = "1" Then 'if a cell contains "1"

Do While eCell.Offset(1, 0).Value = ""
Worksheets("Arkusz4").Rows(n + 1).Delete 'removing row below that containing "1"


Loop
Worksheets("Arkusz4").Rows(n).Delete 'removing row containing "1"
End If
Next

End Sub