Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire row
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire row
On May 12, 2:46*pm, Carol 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.D elete |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire row
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" wrote in message ... 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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire row
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:A 6,A7)=1),"delete","") Best Regards, Gabor Sebo "Carol" wrote in message ... 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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire row
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" wrote in message ... 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy values then delete row for entire sheet | New Users to Excel | |||
Select and delete entire row Excel VBA macro | Excel Programming | |||
Macro - delete entire row which contain a specific text | Excel Worksheet Functions | |||
Help! with Macro - delete entire row | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |