# I need a macro to delete rows when cells and adjacent cells equal another pair

1. ## I need a macro to delete rows when cells and adjacent cells equal another pair

So i have three columns - A will contain repair orders, B will contain amounts, C is irrelevant in this case. Out of around 200 samples, 30-40 rows need to be deleted. They will have the same value in column A. However, column B will have opposite values - they will net \$0.00 and i need those lines deleted. But not all matching values in A will have matching values in B. Values in B that dont match must remain on the sheet.

So, I need to find out how to write a macro to look in column A, find duplicates, then look in column B and find same absolute values (but with different signs) and delete these rows. Column B values must depend on column A because i will have numerous repair orders closing for \$5.00 in column B.
Attached is a sample data. First 6 rows are "in and out" net \$0.00. So the macro must identify that and delete rows 1-6. The last two rows are giving me a difference of \$1.00-those two rows must stay on the spreadsheet.

 952527 272.2 paid not closed 09.01-09.06 952541 6.65 paid not closed 09.01-09.06 952628 5 paid not clsoed 09.01-09.06 952527 -272.2 closed not paid 952541 -6.65 closed not paid 952628 -5 closed not paid 952528 104 paid not closed 09.01-09.06 952528 -105 closed not paid

Thanks.

2. ## Re: I need a macro to delete rows when cells and adjacent cells equal another pair

Add a column for Absolute value of the numbers in Column B (you can then use your newly added column for Absolute values, let's say J, to do 1st sort and then sort on Column B. This should make it easy to see what 'matches' up and can be eliminated. I add another column, lets say K, and for the equal & opposite amounts I put 'Del' in column K. Then, you can filter on just K(all the amounts you are about to delete) to ensure that they add to 0 IN COLUMN B(where they are the original amounts) if the total is 0, filter on COlumn K for 'Del' ~ it's then ok to delete all these net to 0 amounts.

3. ## Re: I need a macro to delete rows when cells and adjacent cells equal another pair

Copy and paste this code into a macro file. A bit longwinded but it should work for anything up to 100 rows of data.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("D1").Select
ActiveCell.FormulaR1C1 = "=IF(SUMIF(C1,RC[-3],C2)=0,1,0)"
Range("D1").Select
Selection.AutoFill Destination:=Range("D1:D100")
Range("D1:D100").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = ""
Range("A1:D1").Select
Range("D1").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort.SortFields.Clear
("D1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Range("\$A\$1:\$D\$9").AutoFilter Field:=4, Criteria1:="1"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
ActiveSheet.Range("\$A\$1:\$D\$100").AutoFilter Field:=4
Columns("A:D").Select
ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort.SortFields.Clear
Range("A2").Select
ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort.SortFields.Clear
("D1:D9"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet4").AutoFilter.Sort
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
End Sub

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1