Macro to delete entire row

  • Thread starter Thread starter Carol
  • Start date Start date
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.
 
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***
Click to expand...

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
 
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
 
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
 
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
 
Back
Top