Macro which sorts and matches entries not working but not erroring

[Nick_G]

Hi all,

I've done some programming in Python and R but this is my first foray into VBA so my apologies if any of this turns out to be an absurdly stupid question!

I'm trying to write some VBA code which will do the following two tasks.

1: Sorts a range of data by Name and then by Absolute Value

2: Looks through the sorted data, and if two rows have the same Name and Values which sum to zero, deletes both rows.

As it stands, the code I've written works some of the time, but not all of the time. I've attached a spreadsheet with examples of where it works and where it fails. The code behaves as expected for "Anna" and "Tom" but fails for Jack. It should delete both entries as the names are the same and the values are the same.

Any advice would be greatly appreciated.

The code is as follows (a text box is assigned to the first piece of code (Sort_And_Match):

Sub Sort_And_Match()

Application.ScreenUpdating = False


counter = 0

SortMultipleColumns
Matching

If counter = 1 Then
    SortMultipleColumns
    Matching
    
End If


    
Application.ScreenUpdating = True

End Sub

Sub Matching()

Application.ScreenUpdating = False

Numrows = Range("A1", Range("A1").End(xlDown)).Rows.Count

Set Rng = Range("A2:A" & Numrows)


For Each Cell In Rng
    RowNumber = Cell.Row
    RowNumberPlus = RowNumber + 1
    
    If Range("B" & RowNumber) = -Range("B" & RowNumberPlus) Then
    
        If Range("A" & RowNumber) = Range("A" & RowNumberPlus) Then
            Range("A" & RowNumber).Resize(2).EntireRow.Delete
            counter = 1
            
            
            
        End If
    End If
    
    Next Cell


End Sub

Sub SortMultipleColumns()

Numrows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Set Rng = Range("A2:B" & Numrows)

Columns("C").EntireColumn.Insert


For Each Cell In Rng
    RowNumber = Cell.Row
    
    Range("C" & RowNumber).Value = Abs(Range("B" & RowNumber))
    
       
    Next Cell


With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A1"), Order:=xlAscending
    .SortFields.Add Key:=Range("C1"), Order:=xlAscending
    .SetRange Range("A1:C" & Numrows)
    .Header = xlYes
    .Apply
    
    Range("C" & RowNumber).EntireColumn.Delete

End With
End Sub

[JLGWhiz]

The parameters for the Rng variable were set up incorrectly for proper use in the For Each statement.

Sub SortMultipleColumns()
Numrows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Set Rng = Range("A2:A" & Numrows)
Columns("C").EntireColumn.Insert
For Each Cell In Rng
    RowNumber = Cell.Row
    Range("C" & RowNumber).Value = Abs(Range("B" & RowNumber))
    Next Cell
With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("A1"), Order:=xlAscending
    .SortFields.Add Key:=Range("C1"), Order:=xlAscending
    .SetRange Range("A1:C" & Numrows)
    .Header = xlYes
    .Apply
    Range("C" & RowNumber).EntireColumn.Delete
End With
End Sub

Also, your 'Matching' procedure needed some work.

Sub Matching()
Application.ScreenUpdating = False
Numrows = Range("A1", Range("A1").End(xlDown)).Rows.Count
Set Rng = Range("A2:A" & Numrows)
For i = Rng.Rows.Count + 1 To 3 Step -1
    RowNumber = i
    RowNumberPlus = i - 1
    If Range("B" & RowNumber) = -Range("B" & RowNumberPlus) Then
        If Range("A" & RowNumber) = Range("A" & RowNumberPlus) Then
            i = i - 1
            Range("A" & RowNumberPlus).Resize(2).EntireRow.Delete
            counter = 1
        End If
    End If
    Next
End Sub

To troubleshoot a process using multiple macros, open the vb editor to the primary code module (starting macro) and then use the F8 key to step through the procedure and see where your problems are.

[Nick_G]

Firstly - thank you very much. That works perfectly and is exactly what I expected.

I have a couple of questions about the changes you made to the Matching procedure.

For i = Rng.Rows.Count + 1 To 3 Step -1

This starts at the end and works backwards until it stops at Row 3 (2 if you consider RowNumberPlus, which is really RowNumberMinus with working backwards)

Why do you do it this way?

Also why the manual update of

i = i - 1

? Given

step = -1

, wouldn't this happen automatically?

Once again, it works perfectly and I'm very grateful! Just trying to figure out "why"

[JLGWhiz]

When deleting rows, starting from the bottom assures that rows will not be skipped because the loop automatically increments to the next row on each iteration. Since Excel shifts the rows as rows are deleted, If the loop begins at the top, then the shifted row will be skipped because the automatic increment of the loop looks at the next row and ignores the row that was shifted into the deleted position. By starting at the bottom, that part of the problem goes away. The deleted row will be below the next increment.

The' i = i - 1 ' statement is to compensate for the two row deletion and give i a value so the autm increment will be for the correct row.

The Step -1 simply tells VBA how to increment the loop upward.
Regards, JLG

[Nick_G]

Many thanks! Will mark thread as solved and keep this in my pocket if I'm asked to dip my toes into VBA again.