Excel 2013 VBA: remaining row count from lastrow to end of page

J

jedwardo

Board Regular
Joined
Aug 21, 2012
Messages
122
Hi, I'm trying to find a way with VBA to get the number of empty rows left on a page on Sheet2. I then want to compare that number to another in "Sheet1 A1". If the 2nd number is greater than the row count I want to insert a pagebreak on Sheet2. Is there a way to do this? I can't find much of anything about end of page on a sheet.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
This should give you a starting point:

Code: 
Sub Macro()
    Dim ws As Worksheet
    Dim FirstPrintRow As Long
    Dim LastPrintRow As Long
    Dim LastUsedRow As Long
    
    Set ws = Worksheets("Sheet1")
    
    'get the first row in the print area on the Worksheet
    FirstPrintRow = ws.Range(ws.PageSetup.PrintArea).Row
    
    'get the last row in the print area on the Worksheet
    LastPrintRow = ws.Range(ws.PageSetup.PrintArea).Row + ws.Range(ws.PageSetup.PrintArea).Rows.Count - 1
    
    'get the last row with data on the Worksheet
    LastUsedRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    'display rows
    'replace this with code to find number of empty rows in print area and put in page break
    MsgBox "First row in print area: " & FirstPrintRow & vbCrLf & _
    "Last row in print area: " & LastPrintRow & vbCrLf _
    & "Last used row in sheet: " & LastUsedRow
End Sub
 
Last edited:
Upvote 0
That worked great, how would I go about getting those values into a cell? Or just one actually, the LastUsedRow value.
 
Upvote 0
After you calculate the LastUsedRow value do something like:

Worksheets("Sheet1").Range("A1").value=LastUsedRow

Just change the sheet name and cell
 
Upvote 0
I've been having trouble making this work for me. If I set the print range then lastprintrow will return the last line in that print range but there are multiple dynamic pages after the loop runs. What I'm looking to find is the row number of the last horizontal page break. Here's my code for the loop:


Code: 
Private Sub CommandButtonTestCode_Click()
Dim c As String * 1                
c = "a"                           
Dim iCell As Range
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim RowTop As Integer
Dim RowBot As Integer
'Dim FirstPrintRow As Long
'Dim LastPrintRow As Long
Dim LastUsedRow As Long
Set ws1 = Sheet1
Set ws = Sheet6
Lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
'FirstPrintRow = ws.Range(ws.PageSetup.PrintArea).Row
'LastPrintRow = ws.Range(ws.PageSetup.PrintArea).Row + ws.Range(ws.PageSetup.PrintArea).Rows.Count - 1


    For Each iCell In Range("A2:CG31")
        With iCell
        If iCell.Interior.ColorIndex = 35 And iCell.Value > 0 Then                            
            Lastrow = Lastrow + 1                        
        RowTop = Lastrow                                  
            ws.Cells(Lastrow + 1, 1).Value = iCell.Value & ws1.Cells(10, 85).Text          
            ws1.Cells(10, 85).Value = ws1.Cells(10, 85).Value + 1                             
            ws.Cells(Lastrow + 1, 1).Offset(0, 1).Value = iCell.Offset(0, 1).Value
            ws.Cells(Lastrow + 1, 1).Offset(0, 1).Font.Size = 12
            ws.Cells(Lastrow + 1, 1).Offset(0, 1).Font.Bold = True
            Lastrow = Lastrow + 1
        ElseIf iCell.Value > 0 And iCell.Interior.ColorIndex = 3 Then                        
            Sheets(1).Cells(iCell.Row, 81) = Sheets(1).Cells(iCell.Row, 81) + 1                
            ws.Cells(Lastrow + 1, 2).Value = c             
            c = Chr(Asc(c) + 1)                           
            ws.Cells(Lastrow + 1, 3).Value = Sheets(1).Cells(1, iCell.Column).Value          
            ws.Cells(Lastrow + 1, 10).Value = iCell.Value  
            ws.Cells(Lastrow + 1, 9).Value = "="
            Lastrow = Lastrow + 1
        ElseIf iCell.Value <> "" And iCell.Interior.ColorIndex = 24 Then                 
            Sheets(1).Cells(iCell.Row, 82) = Sheets(1).Cells(iCell.Row, 82) + 1           
            ws.Cells(Lastrow + 1, 2).Value = c                                                       
            c = Chr(Asc(c) + 1)                                                                           
            ws.Cells(Lastrow + 1, 3).Value = Sheets(1).Cells(1, iCell.Column).Value         
            ws.Cells(Lastrow + 1, 6).Value = iCell.Value                                               
            ws.Cells(Lastrow + 1, 6).Cells.HorizontalAlignment = xlHAlignCenter
            ws.Cells(Lastrow + 1, 7).Value = "x"
            ws.Cells(Lastrow + 1, 8).Value = iCell.Offset(0, 1).Value                             
            ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignCenter
            ws.Cells(Lastrow + 1, 9).Value = "="
            ws.Cells(Lastrow + 1, 10) = ws.Cells(Lastrow + 1, 6) * ws.Cells(Lastrow + 1, 8)   
            Lastrow = Lastrow + 1
            If ws1.Cells(iCell.Row - 1, 80).Value > 0 And ws1.Cells(iCell.Row, 79).Value > ws1.Cells(iCell.Row - 1, 80).Value Then
                ws.Rows(LastUsedRow + 2).PageBreak = xlPageBreakManual
            End If
        ElseIf iCell.Value > "0" And iCell.Interior.ColorIndex = 40 Then                                  
            ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(1, iCell.Column).Value             
            ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
            ws.Cells(Lastrow + 1, 9).Value = "="
            ws.Cells(Lastrow + 1, 10).Value = iCell.Value  'move subtotal
            ws.Cells(Lastrow + 1, 10).Borders(xlEdgeTop).LineStyle = xlContinuous
            Lastrow = Lastrow + 1 
        ElseIf iCell.Value > "0" And iCell.Interior.ColorIndex = 46 Then                                 
            If Sheets(1).Cells(iCell.Row, 80).Value = "" Then                                               
                Sheets(1).Cells(iCell.Row, 80).Value = Sheets(1).Cells(1, iCell.Column).Value    
                Else                                                                                         
                Sheets(1).Cells(iCell.Row, 80).Value = Sheets(1).Cells(iCell.Row, 80).Value & ", " & Sheets(1).Cells(1, iCell.Column).Value
            End If
    ElseIf iCell.Value > "0" And iCell.Interior.ColorIndex = 48 Then                           
            ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(1, iCell.Column).Value & " " & Sheets(1).Cells(iCell.Row, 80).Value & " (" & iCell.Text & ")" 
            ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
            ws.Cells(Lastrow + 1, 9).Value = "-"
            ws.Cells(Lastrow + 1, 10).Value = iCell.Offset(0, 1).Value                         
            Lastrow = Lastrow + 1
        ElseIf iCell.Value > "0" And iCell.Interior.ColorIndex = 45 Then                      
            ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(1, iCell.Column).Value            
            ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
            ws.Cells(Lastrow + 1, 9).Value = "="
            ws.Cells(Lastrow + 1, 10).Value = iCell.Value  'move room total
            Lastrow = Lastrow + 1
        ElseIf iCell.Interior.ColorIndex = 19 Then

            LastUsedRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row       
        
            If (Sheets(1).Cells(iCell.Row + 1, 1).Value > "A" Or Sheets(1).Cells(iCell.Row + 1, 1) = "") And Sheets(1).Cells(iCell.Row, 1).Value = "A" Then
                ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(1, 85).Value      
                ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
                ws.Cells(Lastrow + 1, 8).Font.Bold = True
                ws.Cells(Lastrow + 1, 9).Value = "="
                ws.Cells(Lastrow + 1, 10).Value = Sheets(1).Cells(2, 85).Value     
                ws.Cells(Lastrow + 1, 10).Font.Bold = True
                ws1.Cells(10, 85).Value = 1                                        
                c = "a"                                                           
            Lastrow = Lastrow + 1
            ws.Rows(LastUsedRow + 2).PageBreak = xlPageBreakManual                  
            ElseIf (Sheets(1).Cells(iCell.Row + 1, 1).Value > "B" Or Sheets(1).Cells(iCell.Row + 1, 1) = "") And Sheets(1).Cells(iCell.Row, 1).Value = "B" Then
                ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(3, 85).Value 
                ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
                ws.Cells(Lastrow + 1, 8).Font.Bold = True
                ws.Cells(Lastrow + 1, 9).Value = "="
                ws.Cells(Lastrow + 1, 10).Value = Sheets(1).Cells(4, 85).Value 
                ws.Cells(Lastrow + 1, 10).Font.Bold = True
                ws1.Cells(10, 85).Value = 1                                      
                c = "a"                                                            
            Lastrow = Lastrow + 1
            ws.Rows(LastUsedRow + 2).PageBreak = xlPageBreakManual                
            ElseIf (Sheets(1).Cells(iCell.Row + 1, 1).Value > "C" Or Sheets(1).Cells(iCell.Row + 1, 1) = "") And Sheets(1).Cells(iCell.Row, 1).Value = "C" Then 
                ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(5, 85).Value 
                ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
                ws.Cells(Lastrow + 1, 8).Font.Bold = True
                ws.Cells(Lastrow + 1, 9).Value = "="
                ws.Cells(Lastrow + 1, 10).Value = Sheets(1).Cells(6, 85).Value
                ws.Cells(Lastrow + 1, 10).Font.Bold = True
                ws1.Cells(10, 85).Value = 1                                       
                c = "a"                                                          
            ws1.Cells(10, 81).Value = ws1.Cells(10, 81).Value + 1                 
            Lastrow = Lastrow + 1
            ws.Rows(LastUsedRow + 2).PageBreak = xlPageBreakManual               
            ElseIf Sheets(1).Cells(iCell.Row, 1).Value = "D" And Sheets(1).Cells(iCell.Row + 1, 1) = "" Then
                ws.Cells(Lastrow + 1, 8).Value = Sheets(1).Cells(7, 85).Value
                ws.Cells(Lastrow + 1, 8).Cells.HorizontalAlignment = xlHAlignRight
                ws.Cells(Lastrow + 1, 8).Font.Bold = True
                ws.Cells(Lastrow + 1, 9).Value = "="
                ws.Cells(Lastrow + 1, 10).Value = Sheets(1).Cells(8, 85).Value
                ws.Cells(Lastrow + 1, 10).Font.Bold = True
                ws1.Cells(10, 85).Value = 1                                        
                c = "a"                                                            
                Lastrow = Lastrow + 1
                ws.Rows(LastUsedRow + 2).PageBreak = xlPageBreakManual            
            End If
            
            RowBot = LastUsedRow                                                 
            c = "a"                                                               
            ws1.Cells(iCell.Row, 86).Value = RowTop                                
            ws1.Cells(iCell.Row, 87).Value = RowBot                              
            ws1.Cells(iCell.Row, 90).Value = PageB

        End If
        End With

    Next iCell
         With ws.Cells
            ws.Columns("G").Cells.HorizontalAlignment = xlHAlignCenter
            ws.Columns("I").Cells.HorizontalAlignment = xlHAlignCenter
            ws.Columns("A").Cells.HorizontalAlignment = xlHAlignLeft
            ws.Columns("J").NumberFormat = "$#,##0.00"                            
         End With
End Sub


This is taking all the data from sheet1 and pasting it to sheet 6 to look like this:

208ilw2.png
[/IMG]

What I'm trying to make it not do is split one room across 2 pages as it did with the upstairs bathroom where the Bid "A" Total ended up on the next page in row 47.

RowTop = the first row number of the last room block, here row 35.
RowBot = the last row number of the last room block, here row 47.

I figured if I could get the row number of the last horizontal page break on the sheet at the end of each loop, if that row number were in between RowTop and RowBot then I could make it insert a page break at RowTop and then continue on loop. I can't for the life of me figure out how to get that row number from the last horizontal page break though.

Any help appreciated,
Jordan
 
Upvote 0
That's pretty much the only criteria for pagebreaks on the sheet. When one room gets split across 2 pages i'd like it to insert a pagebreak at the beginning of that room to keep that from happening. After each pagebreak the new page starts in the first empty row and continues on until either there are no more rooms to copy to sheet or until another room gets split across 2 pages again. That's where my difficulty comes in is the rows are dynamic and will be different with each bid.

The pagebreaks you see in the code as is are added at the end of each bid ie: if there is a "Bid B" it will insert a pagebreak at the end of Bid A before continuing with loop. Those ones are working fine though.

Pages as seen in page break mode
 
Last edited:
Upvote 0
Jordan, have a look at the following code sample to get info on the page breaks. You should be able to work it into your code.

Code: 
Sub PageBreakInfo()
MsgBox ActiveSheet.HPageBreaks.Count
For Each c00 In ActiveSheet.HPageBreaks
    MsgBox c00.Location.Row
Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Number of Pages Doesn't Update in Loop
Replies
1
Views
230
bobsan42
bobsan42
M
  • Solved
VBA to Insert Mismatched Row in separate worksheet
2 3
Replies
21
Views
755
Peter_SSs
Peter_SSs
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
442
Akuini
Akuini
L
  • Question Question
Prevent Pagebreak from hidden columns and rows, combine visible columns to one page wide
Replies
1
Views
239
bobsan42
bobsan42
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
230
igold
igold

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top