Macro to delete entire row
in [Programming]
|
From: Carol on 12 May 2010 05:46 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 12 May 2010 07:36 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 12 May 2010 10:16 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 12 May 2010 10:54 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 12 May 2010 19:42 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 *** >
|
Pages: 1 Prev: SendKeys with enter Next: Skip condition if cell is blank |