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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top