Detect last row on last page

Hello,

I have been trying to figure out how to add borders to all the used pages, and not just the used cells or rows. I can easily detect the last used row by using something like this, but that’s not what I need:

LastRow = ActiveSheet.UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row

I am having problems automatically locating the last row of the last used page. It is possible to locate each page break from something like this:

For Each pb In ActiveSheet.HPageBreaks
        LastRow = pb.Location.Offset(-1, 0).row
Next pb

The problem is that I can’t find the last row from this, since there is no page break at that position. For example if there is only one page, there are no page breaks, but I still want to obtain the last row number. At the moment I have defined the last row number by assuming that the number of rows on each page always is the same and a predefined number, but I want it to be more flexible.

Can anyone help me with this?

Thanks in advance,

/Kristine

With which piece of code would you then find this last used row? Because if i use this example:

Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

MsgBox lastRow

Then it returns the last row with data and not the last row on the page if it is printed. I am not interested in putting borders around the data only, it has to be added to the whole page. I haven't been able to find a piece of code which does what i need, so i'd be thankful if you could give me an example.

/Kristine

Well as a very simple example i have attached something similar to what i wrote in my previous post. The two different ways of finding the last used row number used in the file will both return row 6, but i actually want to get row 50 returned even if the page was blank. I just want to know the last row present on the page no matter if there is data in it or not.

/Kristine