smartbuyer
08-06-2013, 02:38 AM
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
10377
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
10377