OK, I just thought someone may have had a version work-around in mind without seeing the code.
Here is the main processing macro(Keep in mind there is also 2 forms, a Class Module and an Event triggered sheet)
Thanks..........
Sub JNLMacro()
Dim rng As Range, r As Range, iCol As Integer, Counter As Integer
Dim FinalRow As Long, i As Long, statCount As Integer
On Error GoTo Err_JNLMacro
'Determines Sheet preparedness
If Sheets.Count > 1 Then
MsgBox "There can only be one sheet in this workbook and it must be named Sheet1"
GoTo Exit_JNLMacro
ElseIf ActiveSheet.Name <> "Sheet1" Then
MsgBox "The Sheet Must Be Named Sheet1"
GoTo Exit_JNLMacro
End If
Application.ScreenUpdating = False
With UserForm2
.Show
End With
'Sets up counter
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
statCount = FinalRow / 15
Call GasGuage(FinalRow, statCount)
'Copies and hides Inform Import sheet
Range("J:J,R:R,AI:AI,AM:AM,BW:BW").Copy
Sheets.Add.Name = "JNL Summary"
With Sheets("JNL Summary")
.Paste
End With
statCount = statCount * 1.7
Call GasGuage(FinalRow, statCount)
'Copies and pastes applicable fields into first 5 columns
Columns("A:B").Cut Destination:=Columns("F:G")
Columns("A:B").Delete Shift:=xlToLeft
With Sheets("Sheet1")
.Visible = False
.Name = "Inform Import"
End With
'Sorts asc by CUSIP
Range("A1:E" & FinalRow).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
statCount = statCount * 1.2
Call GasGuage(FinalRow, statCount)
''''Begin Aggregate''''''''''''''''''''''''''''''''''''''''''''''
Counter = 0
'The next line labours under the assumption that no blank cells exist within Col A used range
Set rng = Range("A2", [A2].End(xlDown))
For Each r In rng
With Cells(r.Row, 1)
If .Value = .Offset(1).Value Then
Counter = Counter + 1
Else: .Offset(, 5).Formula = "=SUM(D" & r.Row & "
" & r.Row - Counter & ")"
.Offset(, 6).Formula = "=SUM(E" & r.Row & ":E" & r.Row - Counter & ")"
Counter = 0
End If
End With
statCount = statCount + 1
Call GasGuage(FinalRow, statCount)
Next
Columns("F:G").Copy
Columns("D:E").PasteSpecial (xlPasteValues)
Columns("F:G").Delete
Application.CutCopyMode = False
'Deletes rows with blank cells, leaving summed values
For i = FinalRow To 2 Step -1
With Cells(i, 4)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next i
'Simple Formatting
With Range("A1").Resize(1, 5)
.Value = Array("Cusip", "Asset Short Description", "Asset Classification Category Name", _
"Quantity", "Quantity Amortized")
.Font.Bold = True
End With
With Columns("D:E")
.Font.Bold = True
.NumberFormat = "#,##0.00"
End With
With Cells
.WrapText = False
.Columns.AutoFit
End With
Application.ScreenUpdating = True
Range("A2").Select
Unload UserForm2
ActiveWindow.FreezePanes = True
Exit_JNLMacro:
Exit Sub
Err_JNLMacro:
MsgBox Err.Description
Unload UserForm2
Resume Exit_JNLMacro
End Sub
