# Macro to delete entire row

C

#### Carol

Hi,

I am using this formula

=IF(COUNTIF(\$A\$6:\$A\$33100,A6)=1,TRUE,COUNTIF(\$A\$6:A6,A6)=COUNTIF(\$A\$6:\$A
\$33100,A6))

to highlight the last row item for cells with duplicates. I need to
delete the entire row of all rows that were not highlighted by this
formula.

Hope this makes sense. Any help would be appreciated.

J

#### Javed

Hi,

I am using this formula

=IF(COUNTIF(\$A\$6:\$A\$33100,A6)=1,TRUE,COUNTIF(\$A\$6:A6,A6)=COUNTIF(\$A\$6:\$A
\$33100,A6))

to highlight the last row item for cells with duplicates. I need to
delete the entire row of all rows that were not highlighted by this
formula.

Hope this makes sense. Any help would be appreciated.

*** Sent via Developersdexhttp://www.developersdex.com***

You can use any loop code but if you change the formula a bit then it
can be done very easily----

probably your function returns FALSE and highlights the row.Change
that formula so that 1 returns.

Worksheets("SheetName").Range
_("Coulmn_Where_formula_Placed").Specialcells(type:=xlcelltypeformulas,value:=XlNumbers).Entirerow.Delete

H

#### helene and gabor

Hello Carol,

I am puzzled over the formula that you are using.
If you have Excel 2007 then you can frame your column A (A6 to last line)
and
data
remove duplicates

Best Regards,

Gabor Sebo

H

#### helene and gabor

Hello Carol,

This formula if entered in A7( not in A6) and down will show all second,
third, etc. duplicates you want to get rid of.

=IF(AND(COUNTIF(\$A\$6:\$A\$33600,A7)>1,COUNTIF(\$A\$6:A6,A7)>=1),"delete","")

Best Regards,

Gabor Sebo

H

#### helene and gabor

Hello Carol,

the enclosed program deletes all lines where column J says False.

Sub deleterowiftext()
Dim i As Long
' TO BE CATEGORIZED" in column J
Count = 0
For i = Cells(Rows.Count, "J").End(xlUp).Row To 1 Step -1
'MsgBox "cells (i,j)" & Cells(i, "j")

If Cells(i, "j") = "False" Then Count = Count + 1
If Cells(i, "j") = "False" Then Rows(i).Delete
If Cells(i, "j") = "False" Then MsgBox "i count" & i & Count
Next i
MsgBox "count is " & Count
' number of deleted lines.
End Sub