Originally Posted by chrisjack001
I want to create a code that can run through the entire spreadsheet and delete the rows of all accounts that have $0 or less Outstanding balance. Example in the above sample account 31203 started with an outstanding balance of $21,803 but had one transaction that resulted in an ending balance of $0. As a result of this all rows of account 31203 should be deleted and data moved up. All accounts that have Outstanding balances like account # 76292 & 56512 in the above will not be deleted because they have outstanding balances of $1000 and $24.900 respectively. The only Accounts that will remain on the spreadsheet will be account numbers with Outstanding balances. Can you please help me create a macro or a VBA code to accomplish this task I am new at this. Thanks for your help.
Hi there ChrisJack001,
I have had a stab at writing a macro for you to perform this function. The macro below relies on the account numbers being sorted (or at least grouped together) and starting in cell A1, whilst the corresponding values are in the column next to them (i.e. column B). If this isn't the case, amend the code, or let me know and I can make appropriate changes for you.
Also, if you need detailed instructions how to put this macro in, and run it, let me know.
Dim CurrentRow As Integer
Dim CurrentAccount, RowsToDelete As Variant
Range("A1").Activate ' This is the position of the first account number - change to suit
Do Until ActiveCell = "" ' Loop until there are no more account numbers
CurrentRow = ActiveCell.Row ' Holds the top row of a group of matching accounts
CurrentAccount = ActiveCell.Value
Do Until ActiveCell.Offset(1, 0) CurrentAccount ' Move down to the last entry for this account
If ActiveCell.Offset(0, 1) = 0 Then ' Final balance=$0?
RowsToDelete = CurrentRow & ":" & ActiveCell.Row 'Build the string for rows to delete
Rows(RowsToDelete).Select ' Delete the rows
ActiveCell.Offset(1, 0).Activate 'Move to the next account