Insert Rows based on 2 Criteria

[smartbuyer]

Need:
1. Copy, Trim, Paste data and remove unneccesary spaces on all cells. (from Sheet1 copy to "Updated Report")
2. in "Updated Report" insert 4 rows based on 2 criteria
a. For Bank codes starting with "a"
-rows will be inserted after all code starting with "a"
b. For Bank codes starting with "n"
-rows will be entered when Bank Name Changes
3. add inputbox to enter number first added row on Bank codes starting with "a" (preferably added to Column D)
4. Sum Reconciled Balance in first added row

What I have so far

Sub RemZer_Click()
    Dim lastrow As Long, n As Long
    Dim lRow As Long
    Dim ws As Worksheet:    Set ws = Sheets("Report")
    Dim lstrow As Long
    Dim iNum As Integer
    Dim iFind As Range

Sheets("Report").Select
    lastrow = Range("R250").End(xlUp).Row
    For n = lastrow To 1 Step -1
        If Cells(n, 5).Value = 0 Then Cells(n, 5).EntireRow.Delete
    Next n

lstrow = ws.Range("A" & Rows.Count).End(xlUp).Row
For iNum = 1 To 4
    Set iFind = ws.Range("A1:A" & lastrow).Find(What:="*a" & iNum & "*", LookIn:=xlValues, LookAt:=xlWhole)
    If Not iFind Is Nothing Then
For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then Rows(lRow).EntireRow.Insert
Next lRow
    End If
Next iNum

End Sub

Book2.xlsx

[Jomathr]

howdy smartbuyer,

for the copy paste section use the macro recorder in the developper tab to generate the code

for the rest something like this should do (untested)

Sub RemZer()
    Dim lastrow As Long, n As Long
    Dim lRow As Long
    Dim ws As Worksheet:    Set ws = Sheets("Updated Report")
    Dim lstrow As Long
    Dim iNum As Integer
    Dim iFind As Range
    Dim strOut As String
    Dim lngLoop As Long
    Dim Frow As String
    
Frow = InputBox("first row")


For Each iFind In ws.Range("A" & Frow & ":" & Range("A" & Frow).End(xlDown).Address)
    'remove spaces
    iFind = Application.WorksheetFunction.Trim(iFind)
    'insert lines
    If Left(iFind, 1) = "a" _
    And Left(iFind.Offset(1, 0), 1) <> "a" _
    And iFind <> "" Then
        For iNum = 1 To 4 Step 1
            iFind.Offset(1, 0).EntireRow.Select
            Selection.Insert
        Next iNum
    ElseIf Left(iFind.Offset(1, 0), 1) = "n" _
    And iFind.Offset(-1, 0) <> "" _
    And iFind <> iFind.Offset(-1, 0) Then
        iFind.EntireRow.Insert
    End If
Next iFind


ws.Range("G" & Frow) = "=sum(C" & Frow & Range("C65536").End(xlUp).adress & "("
End Sub

hope it helps!