Creating VBA Code to Remove Similar Data Entries in Table
Hi all,
I’m currently working with large data sets where we have 1000s of lines that are equal and opposite to another. For example you can see in the below table;
BATCHNAMEAmountDateDave’s Fund1000008/01/2024Reverses Dave’s Fund-1000020/02/2024Dave’s new Fund500024/02/2024
What I would like to do is have a VBA code that matches the “Dave’s Fund” line and “Reverses Dave’s Fund” line and removes them from the data set (but only if the values add up to nil).
I’ve began on some code that would remove any line that contains the text “Reverses” but I’d like to add in the criteria that removes the original line and checks that the values net to nil.
Sub DeleteRowsOnCriteria()
Dim lastRow As Long, dataRow As Long
Dim ACCOUNTEDBALANCE As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For dataRow = lastRow To 2 Step -1
BATCHNAME = Range(“B” & dataRow).Value
If InStr(1, BATCHNAME, “Reverses”) Then
Rows(dataRow).Delete
End If
Next dataRow
End Sub
Would anyone have any idea on how to add to the code to achieve it’s aims?
Many thanks,
Daniel
Hi all, I’m currently working with large data sets where we have 1000s of lines that are equal and opposite to another. For example you can see in the below table; BATCHNAMEAmountDateDave’s Fund1000008/01/2024Reverses Dave’s Fund-1000020/02/2024Dave’s new Fund500024/02/2024 What I would like to do is have a VBA code that matches the “Dave’s Fund” line and “Reverses Dave’s Fund” line and removes them from the data set (but only if the values add up to nil). I’ve began on some code that would remove any line that contains the text “Reverses” but I’d like to add in the criteria that removes the original line and checks that the values net to nil. Sub DeleteRowsOnCriteria()
Dim lastRow As Long, dataRow As Long
Dim ACCOUNTEDBALANCE As Long
lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
For dataRow = lastRow To 2 Step -1
BATCHNAME = Range(“B” & dataRow).Value
If InStr(1, BATCHNAME, “Reverses”) Then
Rows(dataRow).Delete
End If
Next dataRow
End Sub Would anyone have any idea on how to add to the code to achieve it’s aims? Many thanks, Daniel Read More