Delete rows with matching positive and negative numbers

[sshirazi]

I have data in column A with a header. Need a macro to delete all matching positive and negative values. Thanks

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA to delete all matching Negative and positive values
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

This solution also posted at cross-post location.

This code assumes you want to delete the entire row, not just move up the cells in column A.

Public Sub DeleteMatches()

   Dim LastRow As Long
   Dim Found As Range
   Dim Row As Long
  
   LastRow = Cells(Rows.Count, "A").End(xlUp).Row

   Row = LastRow
   Do Until Row = 2

      Set Found = Range("A:A").Find(what:=-Cells(Row, "A"), lookat:=xlWhole, LookIn:=xlValues)
      If Not Found Is Nothing Then
         Rows(Row).EntireRow.Delete
         Rows(Found.Row).EntireRow.Delete
         Row = Row - 1
      End If
     
      Row = Row - 1
  
   Loop


End Sub

 

[sshirazi]

Not sure why it did not work. See the sample data, only the last two rows should remain.

Balance

(1,409.02)​

(8,152.80)​

(4,365.65)​

429.32​

(214.66)​

188.73​

80.00​

89.62​

86.12​

190.00​

180.00​

80.01​

80.00​

100.00​

100.00​

140.07​

180.03​

86.28​

141.02​

40.00​

102.53​

205.85​

214.20​

202.69​

40.01​

100.02​

20.00​

89.78​

96.29​

100.02​

180.03​

186.27​

128.94​

77.27​

2,420.20​

1,118.00​

1,417.20​

674.80​

1,863.00​

659.60​

213.54​

225.39​

206.28​

100.00​

100.00​

100.01​

100.01​

159.23​

100.01​

100.00​

160.01​

99.98​

100.00​

200.00​

89.78​

(8,452.85)​

(2,665.75)​

 

[6StringJazzer]

I have also responded to this in the cross-posted forum, and will continue the discussion there since it supports file attachments.