From: Carol on


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 Developersdex http://www.developersdex.com ***
From: Javed on
On May 12, 2:46 pm, Carol <anonym...(a)devdex.com> wrote:
> 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
From: helene and gabor on
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

"Carol" <anonymous(a)devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276(a)TK2MSFTNGP02.phx.gbl...
>
>
> 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 Developersdex http://www.developersdex.com ***
>

From: helene and gabor on
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


"Carol" <anonymous(a)devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276(a)TK2MSFTNGP02.phx.gbl...
>
>
> 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 Developersdex http://www.developersdex.com ***
>

From: helene and gabor on
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

--------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------


"Carol" <anonymous(a)devdex.com> wrote in message
news:%23qh2Mgb8KHA.3276(a)TK2MSFTNGP02.phx.gbl...
>
>
> 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 Developersdex http://www.developersdex.com ***
>