Insert # of Rows after last data

[jtmeunier]

Good Day,

I am trying to find the last row with data in it, then insert a number of rows.
The rows inserted will be different for the first page, then similar for every page after that. Basically if the data goes to more than one page I want to put in X rows so the table is filled on th second page, just to fill the page before printing.

The number of rows to insert will be different all the time, some times it doesn't need to insert any because the data fits on one page, other times it will need to insert a few.

Here is the code to date.
Any help is appreciated.

Sub InsertBlankRows()
Dim LastRow As Range
    Dim rng As Range
    Dim CountTrue As Long
    
    Set rng = Sheets("Checklist").Range("Work")
    CountTrue = Application.WorksheetFunction.CountIf(rng, "True")
    Page2 = CountTrue - 27
    InsertRowDiff = 27 - Page2
     
    
    With Sheets("Quote")
        LastRow = Range("C" & Rows.Count).End(xlUp).row
        Range("C:C" & LastRow).Resize(InsertRowDiff, 1).EntireRow.Insert
     End With
End Sub

 

[DavidCoop]

One quick observation from the last line of code inside the With statement! Range("C:C" & LastRow) looks wrong to me. Shouldn't it be Range("C1:C" & LastRow)?

 

[jtmeunier]

Correct Thank-you,

This is where I am know with the code.
Still having issues.

Sub InsertBlankRows()    Dim rng As Range
    Dim CountTrue As Long
    
    Set rng = Sheets("Checklist").Range("Work")
    CountTrue = Application.WorksheetFunction.CountIf(rng, "True")
    Page2 = CountTrue - 27
    InsertRowDiff = 1
    
    If CountTrue = ≤27 Then
        
    ElseIf CountTrue = ≤54 Then
        With ActiveSheet
        Range("C" & Rows.Count).End(xlUp).Resize(InsertRowDiff, 1).EntireRow.Insert
        End With
    End If
    
End Sub

 

[jtmeunier]

Getting Closer

Syntax error above corrected,

Sub InsertBlankRows()    Dim rng As Range
    Dim CountTrue As Long
    
    Set rng = Sheets("Checklist").Range("Work")
    CountTrue = Application.WorksheetFunction.CountIf(rng, "True")
    Page2 = CountTrue - 27
    InsertRowDiff = 1
    
    If CountTrue <= 27 Then
    Exit Sub
    
    ElseIf CountTrue <= 54 Then
        With ActiveSheet
        Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(InsertRowDiff).EntireRow.Insert
        End With
    End If
    
End Sub

 

[DavidCoop]

Getting Closer

Syntax error above corrected,

Sub InsertBlankRows()    Dim rng As Range
    Dim CountTrue As Long
    
    Set rng = Sheets("Checklist").Range("Work")
    CountTrue = Application.WorksheetFunction.CountIf(rng, "True")
    Page2 = CountTrue - 27
    InsertRowDiff = 1
    
    If CountTrue <= 27 Then
    Exit Sub
    
    ElseIf CountTrue <= 54 Then
        With ActiveSheet
        Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(InsertRowDiff).EntireRow.Insert
        End With
    End If
    
End Sub

Sorry JT,

I'm having difficulty working out what you are trying to achieve.

It looks to me that you are trying to set something up for printing (or other output) and this is based on 27 lines per page. (This code assumes a maximum of two pages - if there are more than 54 lines, it will do nothing).

What are you trying to achieve with this line of code?
Range("C" & Rows.Count).End(xlUp).Offset(1).Resize(InsertRowDiff).EntireRow.Insert
Where are you trying to insert ONE row?

If, in fact, you want to enter a blank row at position 27, amend this line of code as follows:
Range("C1:C" & Rows.Count).End(xlUp).Offset(27).Resize(InsertRowDiff).EntireRow.Insert

(You also need to take the Dim statement for rng off the Sub row!