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.

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

<colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>
</tbody>

 

[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.