PDA

View Full Version : Insert Rows based on 2 Criteria



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

Jomathr
08-06-2013, 11:36 AM
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!