Solved delete rows with cells in a column with th same +and- values

October 2, 2012 at 01:32:19
Specs: Windows 7
hi there
I have the same problem with positive and exact negative value in a column which should be delete.
for example consider the below sample:
Column A
123
-123
554
-333
-554
145
-145
234
I just want the below result:
Column A
-333
234
as 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

Report •

✔ Best Answer
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 B
123 Jim
-123 Jim
554 Nick
-333 Will
-554 Nick
145 Susan
-145 Susan
234 Jack
I just want the below result:
Column A Column B
-333 Will
234 Jack

the 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 work
what 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
✔ Best Answer
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

Report •

Ask Question