Selecting the last row on a page RRS feed

  • Question

  • On an excel sheet I have a page break for every 20 rows (not counting the headers on the first page of data), so that when printed, there are only 20 rows for each sheet.

    I would like to highlight the first column of the last row of each page, even if there are less than 20 rows of data (e.g. the last page has only 11 rows, but that last row should still be highlighted) but I'm unsure how to do that.

    I was able to find Ron de Bruin's page on finding the last row, column or last cell in an active sheet.

    Sub LastRowInOneColumn() 'Find the last used row in a Column: column A in this example Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With MsgBox LastRow End Sub Sub LastColumnInOneRow() 'Find the last used column in a Row: row 1 in this example Dim LastCol As Integer With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With MsgBox LastCol End Sub

    How can this be modified so that it selects the last row on each page break in the sheet?

    Friday, November 18, 2016 10:27 PM

All replies

  • For example:

    Sub HighlightLast()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 21 To m Step 20
            Range("A" & r).Resize(1, 3).Interior.Color = vbYellow
        Next r
        ' Last row
        If m Mod 20 <> 1 Then
            Range("A" & m).Resize(1, 3).Interior.Color = vbYellow
        End If
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (

    Friday, November 18, 2016 11:04 PM