..Range("Z2").Formula = "=if(D2=D1,"""",if(D2=D3,"""",0))"

You need to double quotes instead of 1

"mattg" wrote:

I get an "application defined or object defined" error on the line where the
formula is entered

"Joel" wrote:

I don't know how many rows you plan to delete, but to delete each row one at
a time is slow. To speed up the process I sometimes put the formula into a
new row like what you did. then sort the 1's to the top of the spreadsheet.
Nextt delete the rows with the ones in 1 step

with ActiveSheet
lstRw = .Cells(Rows.Count, "d").End(xlUp).Row
'add formula to column z
.range("Z" & 2).formula = "=if(D2=D1,"",if(D2=D3,"",0))"
.range("Z" & 2).copy _
destination:=.range("Z" & 2 & ":Z" & lstRw)
Rows("2:" & Lstrw).sort _
key1:=.range("Z2")
lstZero = .Cells(Rows.Count, "z").End(xlUp).Row
if lstZero 1 then
.rows("2:" & lstZero).delete
end if

end with

It is a lot of code but it is much quicker than deleting 100 row 1 at a time.


"mattg" wrote:

I'm tyring to delete rows based on these criteria: If the value of any given
cell in column D does not match the value of the cell before it or the cell
after it within that column delete the entire row.

To do it manually I use the formula =if(D2=D1,1,if(D2=D3,1,))

Then I delete the rows with "0" value

Here is what I have so far that doesn't work:

Sub delRws()


Dim lstRw As Long, i As Long

lstRw = ActiveSheet.Cells(Rows.Count, "d").End(xlUp).Row

For i = lstrow To 2 Step -1
If ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i - 1, "d") And
ActiveSheet.Cells(i, "d") < ActiveSheet.Cells(i + 1, "d") Then
EntireRow.Delete

Next

End Sub

Thanks in advance