# Solved delete rows with cells in a column with th same +and- values October 2, 2012 at 01:32:19
Specs: Windows 7
 hi thereI have the same problem with positive and exact negative value in a column which should be delete.for example consider the below sample:Column A123-123554-333-554145-145234I just want the below result:Column A-333234as you see just the values with no negative one, remained.how can I write a macro to do this for me?would you please help See More: delete rows with cells in a column with th same +and- values

October 2, 2012 at 12:41:17
 I'm sorry I did not notice the instructions...I really appreciate your answer.you are all right. I forgot to change the D to B. in my real data the D column play the B role in this example.any way...I tested the below code and fortunately. it worked out...```Sub Delete_Stuff() Dim lastRow As Long, myRow As Long, findRange As Range, findRange1 As Long, goalrow As Long, mynewrow As Long Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess lastRow = Cells(Rows.Count, 1).End(xlUp).Row For myRow = lastRow To 2 Step -1 If Cells(myRow, 1) < 0 Then Set findRange = Range("A1:A" & lastRow).Find(Abs(Cells(myRow, 1)), LookIn:=xlValues, LookAt:=xlWhole) If Not findRange Is Nothing Then goalrow = findRange.Row For mynewrow = goalrow To (myRow - 1) Step 1 If (Cells(mynewrow, 4) = Cells(myRow, 4)) Then Rows(myRow).Delete Rows(mynewrow).Delete Exit For End If Next mynewrow End If End If Next myRow End Sub ```Again thank you very much

#1
October 2, 2012 at 02:21:23
 sorry the example is something like this:Column A Column B123 Jim-123 Jim554 Nick-333 Will-554 Nick145 Susan-145 Susan234 JackI just want the below result:Column A Column B-333 Will234 Jackthe name in the column B is important. the same + and - values in column A which belong to one person. and I want the to corresponding rows be delete.

Report •

#2
October 2, 2012 at 02:24:52
 I already have this macro, but it does not workwhat should i do?Sub Delete_Stuff() Dim lastRow As Long, myRow As Long, findRange As Range, findRange1 As Range Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess lastRow = Cells(Rows.Count, 1).End(xlUp).Row For myRow = lastRow To 2 Step -1 If Cells(myRow, 1) < 0 Then Set findRange = Range("A2:A" & lastRow).Find(Abs(Cells(myRow, 1)), LookIn:=xlValues, LookAt:=xlWhole) If Not findRange Is Nothing Then Set findRange1 = Range("D2:D" & lastRow).Find(Abs(Cells(myRow, 4)), LookIn:=xlValues, LookAt:=xlWhole) If Not findRange1 Is Nothing Then Rows(myRow).Delete findRange.EntireRow.Delete myRow = myRow - 1 End If End If End If Next myRow End Sub

Report •

#3 October 2, 2012 at 08:46:50
 First, a posting tip...If you click on the blue line at the end of this post you'll find the instructions on how to post example data and VBA code in this forum. Please try to follow those instructions in the future. Thanks!As for your question...Your code appears to be searching for data that doesn't exist, at least based on the example data that you posted.Let's look at this section:``` If Cells(myRow, 1) < 0 Then Set findRange = _ Range("A2:A" & lastRow).Find(Abs(Cells(myRow, 1)), _ LookIn:=xlValues, LookAt:=xlWhole) If Not findRange Is Nothing Then '>>>>> Why are you searching Column D in the next instruction? <<<<< Set findRange1 = _ Range("D2:D" & lastRow).Find(Abs(Cells(myRow, 4)), _ LookIn:=xlValues, LookAt:=xlWhole) If Not findRange1 Is Nothing Then Rows(myRow).Delete findRange.EntireRow.Delete myRow = myRow - 1 End If End If End If ```Each time through the loop, the code searches for a negative number in Column A. If it finds that number, it then searches for a number in Column D. Since you don't appear to have any data in Column D, findRange1 will always be Nothing, therefore the code to delete findRange.EntireRow will never run.Why are you searching Column D?BTW...You could do something similar with a formula, although it wouldn't delete the rows.If you created a flitered list of names so that you had a single entry for each name, then you could a SUMIF function.Let's say your original data is in A1:A8 and your filtered list of names starts in B10. Put this formula in A10 and drag it down. The result will only show a value next to Will and Jack.=IF(SUMIF(\$B\$1:\$B\$8,B10,\$A\$1:\$A\$8)<>0,SUMIF(\$B\$1:\$B\$8,B10,\$A\$1:\$A\$8),"")Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
October 2, 2012 at 12:41:17
 I'm sorry I did not notice the instructions...I really appreciate your answer.you are all right. I forgot to change the D to B. in my real data the D column play the B role in this example.any way...I tested the below code and fortunately. it worked out...```Sub Delete_Stuff() Dim lastRow As Long, myRow As Long, findRange As Range, findRange1 As Long, goalrow As Long, mynewrow As Long Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess lastRow = Cells(Rows.Count, 1).End(xlUp).Row For myRow = lastRow To 2 Step -1 If Cells(myRow, 1) < 0 Then Set findRange = Range("A1:A" & lastRow).Find(Abs(Cells(myRow, 1)), LookIn:=xlValues, LookAt:=xlWhole) If Not findRange Is Nothing Then goalrow = findRange.Row For mynewrow = goalrow To (myRow - 1) Step 1 If (Cells(mynewrow, 4) = Cells(myRow, 4)) Then Rows(myRow).Delete Rows(mynewrow).Delete Exit For End If Next mynewrow End If End If Next myRow End Sub ```Again thank you very much 