Count last remaining blank rows of a print area

Question

I am looking for an Excel VBA code that could count the last remaining rows of a print area page (Landscape) and decide whether there is enough number of rows available or not to copy/paste the text from another worksheet to active worksheet.

For example:

I have an Invoice template in excel. The Invoice ends with a Grand Total at the very end. I would like to add my Terms and Conditions policy after a space of one blank row under the Grand Total every time. My T&C consists of three paragraphs which are in a merged cell that takes a space of 14 rows.

The invoice can go to any number of pages, my requirement is, on whatever the last page the Grand Total occurs there should be a space of 16 rows at least available underneath it to copy/paste the T&C if that many rows are not available then T&C should be moved to the next page automatically. Basically, I do not want the T&C to be half printed on two separate pages. I have T&C on a separate worksheet which I need to copy/paste to active worksheet i.e. Invoice worksheet under Grand Total. I hope everything makes sense.

Here is the accompanying demo video of what I am looking for. Any help would really be appreciated. Thank you in advance.

https://i.stack.imgur.com/7yORJ.jpg

`ws.Range(ws.PageSetup.PrintArea).Rows.Count` will give you the number of rows in the print area if the print area is set and [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) will give the last row. Simply subtract to get what you need. — Siddharth Rout, Mar 12 '20 at 06:43

score 1 · Accepted Answer · answered Mar 12 '20 at 06:58

Try

Sub test()
    Dim Ws As Worksheet, Ws2 As Worksheet
    Dim Target As Range
    Dim myRow As Integer
    Dim cnt As Integer, r As Long, n As Integer

    cnt = 34 '<~~  Sheet 1 page rows count + 1
    Set Ws = Sheets(1)
    Set Ws2 = Sheets(2)
    With Ws
        Set Target = .Cells.Find("Grand Total")
        n = .HPageBreaks.Count
        r = (n + 1) * cnt
        If r - Target.Row > 16 Then
            myRow = Target.Row + 2
        Else
            myRow = r
        End If
        Ws2.Range("a1").MergeArea.Copy .Range("a" & myRow)
    End With

End Sub

Count last remaining blank rows of a print area

1 Answers1