Loop if statement for increasing multiples of an integer-CodePudding

I am trying to make the last line of my data at the bottom of the last page if I printed the spreadsheet. The last row of each page is = 81 80x. So I am inserting the required number of rows. I need help looping the code below so I don't need 200 lines of code for the potential of 100 pages.

I have the code below which works but I need to add a 100 more rows of these elseif lines with increasing multiples of 80 to accomplish my goal

Sub Draft()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

If LR < 81 Then
    Range("A" & LR).EntireRow.Resize(81 - LR).Insert Shift:=xlDown
ElseIf LR < 161 Then
    Range("A" & LR).EntireRow.Resize(161 - LR).Insert Shift:=xlDown
ElseIf LR < 241 Then
    Range("A" & LR).EntireRow.Resize(241 - LR).Insert Shift:=xlDown
ElseIf LR < 321 Then
    Range("A" & LR).EntireRow.Resize(321 - LR).Insert Shift:=xlDown
ElseIf LR < 401 Then
    Range("A" & LR).EntireRow.Resize(401 - LR).Insert Shift:=xlDown
End If

End Sub

CodePudding user response：

Is this working for you ?

The trick is to calculate a bottom row of a sheet near your data row. if you below this bottom line, then your calculated bottomline is also your target line. If your Data are past the bottomline of your calculated sheet, then set the next bottom line as your target.

By Using the long Datatype for the division you are getting rid of the unnecessary decimal point numbers in the result variable shtNum. Getting this number enables you to do your calculation in whole sheet steps.

The cast to the datatype long is rounding up to the sheets number thats bottom line is nearest to your datarow.

Sub Draft2()
    Dim LR As Long
    Dim shtNum As Long
    
    LR = Range("A" & Rows.Count).End(xlUp).Row
    shtNum = LR / 80
    
    If (LR < (80 * shtNum   1)) Then
        ' Datas are inside of the 80 Rows of the page. So we set them to the bottom of the page
        Range("A" & LR).EntireRow.Resize((80 * shtNum   1) - LR).Insert Shift:=xlDown
    ElseIf (LR = (80 * shtNum   1)) Then
        'do nothing, we are at the right row
    ElseIf (LR > (80 * shtNum   1)) Then
        ' Datas are off the page. put them on the bottom of the next page
        shtNum = shtNum   1
        Range("A" & LR).EntireRow.Resize((80 * shtNum   1) - LR).Insert Shift:=xlDown
    End If
    
End Sub

CodePudding user response：

Thanks for the help. This is the solution I found and used:

Sub Draft()

Dim LR As Long
LR = Range("E" & Rows.Count).End(xlUp).Row    
Dim j, x As Integer

For x = 0 To 200
j = 81   (80 * x)

If LR < j Then
Range("A" & LR).EntireRow.Resize(j - LR).Insert Shift:=xlDown
Exit For
End If
Next x
End Sub