Formula or Macro to compress worksheet

[psandoz]

Hi Excel Wizards.

Does anyone know of a formula, array processes or macros that will delete rows to compress the worksheet? See the example below. This is the amortization schedule for a $5000 loan. Occasionally on some loans I will make additional Principal payments. As you will see the schedule on the right which includes extra payments is 8 months less than the original 60 month schedule on the left. (Rows for periods 3-49 are hidden for simplicity). The objective is to have Excel remove rows 52-60 on the right hand example and place the row with the Totals as the last row, removing the distraction of the empty months and rows of zeros. On a 30 year loan with minimal extra payments there can be 12+ empty rows.

Thanks for helping me tackle this challenge.


[TABLE="width: 796"]
<colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 5"]Loan Computations[/TD]
[TD="colspan: 5"]Loan Computations with Extra Payments[/TD]
[/TR]
[TR]
[TD]Period[/TD]
[TD]Beginning[/TD]
[TD]Payment[/TD]
[TD]Principal[/TD]
[TD]Interest[/TD]
[TD]Extra Principal[/TD]
[TD] Beginning[/TD]
[TD]Payment[/TD]
[TD]Principal[/TD]
[TD]Interest[/TD]
[TD]Extra Principal[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]$5,000.00[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$74.47[/TD]
[TD="align: right"]$18.75[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$5,000.00[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$74.47[/TD]
[TD="align: right"]$18.75[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]$4,925.53[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$74.75[/TD]
[TD="align: right"]$18.47[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$4,925.53[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$74.75[/TD]
[TD="align: right"]$18.47[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD="align: right"]$1,002.40[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$89.46[/TD]
[TD="align: right"]$3.76[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$83.03[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$92.91[/TD]
[TD="align: right"]$0.31[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]$912.94[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$89.80[/TD]
[TD="align: right"]$3.42[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$92.91[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$92.87[/TD]
[TD="align: right"]$0.35[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD="align: right"]$823.14[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$90.13[/TD]
[TD="align: right"]$3.09[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD="align: right"]$733.01[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$90.47[/TD]
[TD="align: right"]$2.75[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]$642.54[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$90.81[/TD]
[TD="align: right"]$2.41[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD="align: right"]$551.73[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$91.15[/TD]
[TD="align: right"]$2.07[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD="align: right"]$460.58[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$91.49[/TD]
[TD="align: right"]$1.73[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD="align: right"]$369.09[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$91.84[/TD]
[TD="align: right"]$1.38[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD="align: right"]$277.25[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$92.18[/TD]
[TD="align: right"]$1.04[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD="align: right"]$185.07[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$92.53[/TD]
[TD="align: right"]$0.69[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="align: right"]$92.54[/TD]
[TD="align: right"]$93.22[/TD]
[TD="align: right"]$92.87[/TD]
[TD="align: right"]$0.35[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Totals[/TD]
[TD="align: right"]$5,593.20[/TD]
[TD="align: right"]$5,000.33[/TD]
[TD="align: right"]$592.87[/TD]
[TD="align: right"]$0.00[/TD]
[TD] Totals[/TD]
[TD="align: right"]$4,754.22[/TD]
[TD="align: right"]$4,312.75[/TD]
[TD="align: right"]$441.47[/TD]
[TD="align: right"]$790.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gingertrees]

I have created an inelegant solution - I HATE using more than 2 nested If statements, but that's the best I can come up with on a Monday morning:

Place this into a standard module. (If that statement makes you nervous, say so and I'll dig up a good intro site for you.)

Sub removelines()
Application.ScreenUpdating = False

Dim LastRow As Integer
Dim LastCol As Integer
Dim i As Integer

With ActiveSheet
    LastRow = .UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    LastCol = .UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
End With

For i = 2 To LastRow
    If Range("A" & i).Value = 0# Then 'assuming first column=A; adjust as needed
        If Range("B" & i).Value = 0# Then
            If Range("C" & i).Value = 0# Then
            '//add more If statements as needed if for column D, E, etc.
                Rows(i).EntireRow.ClearContents
            End If
        End If
    End If
Next i
Range(Cells(2, 1), Cells(LastRow, LastCol)).Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
Range("a1").Select

Application.ScreenUpdating = True

End Sub

I hope that works for ya.

 

[psandoz]

Thanks Gingertrees. I'm not that experienced with macros but I think I know what to do. I should have stated up front that I'm using MS Exel 2011 for Mac. Not sure if that makes a difference.