Looking for VBA code to delete some rows and add another rows

[hbsqn]

Dear all,

I would like to ask for your help to solve the following issue. I am encoding bank statements transaction in Excel by recording 4 fields, data, transaction code, amount and balance. One analysis required from this statement is the average daily balance which require me to do several actions to my statement manually as following, I hope that I can automate this action to save my time.

here is example to one statement,

first, in each day, I delete all transactions in that day and keep only last transaction ( where day closing balance is mentioned )

then In insert the missing days, for such extra rows, I add the missing days, fixed code 22 and last closing balance

 

[Comfy]

Please confirm the fixed code as you have said 22 in your post but the example says 33.

Additionally, would you also like to SUM the amount column for each day as the balance no longer makes sense due to the rows that have been deleted?

So in your example 30-Sep would read 3,675.00 rather than 100.00

 

[hbsqn]

Please confirm the fixed code as you have said 22 in your post but the example says 33.

Additionally, would you also like to SUM the amount column for each day as the balance no longer makes sense due to the rows that have been deleted?

So in your example 30-Sep would read 3,675.00 rather than 100.00

Dear Comfy, thanks for your comment.

you are right, additional rows will have fixed code of 33 and not 22.

Regarding Amount, it is important for me only in the beginning to get the running ( moving ) balance, but not after that as I will convert balance column to values by the end ( before start the process explained above) . After this point, amount field will be meaningless and not important to me that is why i keep it empty in the added rows.

If that is possible, it would be great if the VBA code would start by fixing balance column ( make it values and not formulas ) and then continue with the other tasks explained above. This will save one more manual step.

 

[Comfy]

Ok. Save your work. Highlight your data and then run this:

Sub FormatClosingBalance()
Dim rng As Range
Dim i As Long


Set rng = Selection
rng.Copy
rng.PasteSpecial xlPasteValues


For i = rng.Rows.Count To 2 Step -1
    Select Case rng(i - 1, 1)
        Case rng(i, 1)
            Rows(i - 1).Delete
        Case Is = rng(i, 1) - 1
            'Do nothing here
        Case Is < rng(i, 1) - 1
            Rows(i).Insert
            Cells(i, 1).Value = rng(i + 1, 1) - 1
            Cells(i, 2).Value = 33
            Cells(i, 4).Value = rng(i - 1, 4)
            i = i + 1
        Case Else
            'Headers dont match row 2
    End Select
Next i


rng.Columns(3).Delete 'Delete this if you don't need to delete the Amount Column


End Sub

 

[hbsqn]

Dear Comfy,

I would like to thank you for your great help. I got another code which I found it quicker than your code and doesn't require me to highlight the range. I am adding it here as you might find it useful.

Appreciate your help again.

[COLOR=#333333]Sub hbsqn()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Dim i As LongDim x As IntegerApplication.ScreenUpdating = FalseFor i = Range("A" & Rows.count).End(3)(1).Row To 2 Step -1    If Range("A" & i).Value = Range("A" & i - 1).Value Then        Range("A" & i - 1).EntireRow.Delete    End IfNext ix = 9Do Until x = 0Range("A" & Rows.count).End(3)(0).SelectDo Until ActiveCell.Row = 1    If ActiveCell.Value + 1 <> ActiveCell.Offset(1).Value Then        ActiveCell.Offset(1).EntireRow.Insert        ActiveCell.Offset(1).Value = ActiveCell.Value + 1        ActiveCell.Offset(1, 1).Value = 33        ActiveCell.Offset(1, 2).Value = ""        ActiveCell.Offset(1, 3).Value = ActiveCell.Offset(, 3).Value    End If    ActiveCell.Offset(-1).SelectLoopx = x - 1LoopApplication.ScreenUpdating = True </code></pre>[COLOR=#333333]End Sub[/COLOR]