Error during For Loop

[Groves22]

Hey All...

I am trying to run a simple For loop, but I keep getting an error, with no clue why. Could someone shine the light on this code for me??

Thanks

MY ERROR MESSAGE:
Run-time error '438'
Object doesn't support this property or method

The code errors out on the .Delete Shift:=xlUp line.

    rCount = PolicyD.Sheets(1).Range("O1").Value
    
    Dim i As Integer
        For i = 2 To rCount
            If Sheets(1).Cells(i, 3) = Sheets(1).Cells(i - 1, 3) Then
                Sheets(1).EntireRow("i").Delete Shift:=xlUp
                rCount = rCount - 1
            End If
        Next i

 

[Bong]

EntireRow is a property of a Range, not a Worksheet.

_________________
Bob Rashkin

 

[Groves22]

So what code is needed? I tried

    rCount = PolicyD.Sheets(1).Range("O1").Value
    
    Dim i As Integer
        For i = 2 To rCount
            If Sheets(1).Cells(i, 3) = Sheets(1).Cells(i - 1, 3) Then
                Range.EntireRow("i").Delete Shift:=xlUp
                rCount = rCount - 1
            End If
        Next i

and it says Range is not optional.

Thanks

 

[SkipVought]

Hi,

Other than the syntatical error, your code may have a severe error in logic.

When you delete row 4, guess what? Row 5 becomes row 4 and the next row after the original row 4 (that is gone) is now row 6!!!

Is that what you expect in your logic?

    Dim i As Integer
    
    rCount = PolicyD.Sheets(1).Range("O1").Value
    
    With Sheets(1)
        For i = rCount To 2 Step -1
            With .Cells(i, 3)
                If .Value = .Cells(i - 1, 3).Value Then
                    .EntireRow.Delete Shift:=xlUp
                    rCount = rCount - 1
                End If
            End With
        Next i
    End With

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Well first that's not how you use EntireRow. It should look more like
[tab]range("C3").entirerow.delete

But more importantly, even though you use
[tab]rCount = rCount - 1
to lower the final row, i continues to increment. So if you delete row 3, then the data that was on row 4 is now on row 3, but i has moved on to 4, so the data that started off in row 4 is never tested.

For that reason, it is easier to just start at the bottom and work your way up.

Here's how I'd do it:

    LstRow = Sheets(1).Range("C65536").End(xlUp).Row

    For i = LstRow To 2 Step -1
        If Sheets(1).Cells(i, 3) = Sheets(1).Cells(i - 1, 3) Then
            Sheets(1).Rows(i).Delete
        End If
    Next i

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

FYI, I might be apt to do this on the sheet, using a formula to COUNT the number of occurrances in column C
[tt]
Z2: =if(C2=C1,if(isnumber(Z1),Z1,0)+1,1)
[/tt]
then AutoFilter on counts>1, select all the VISIBLE data rows and right-click DELETE. Depends on the situation.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Groves22]

Great! Works smooth now!
Thank you everyone.