How to determine Last row of a Page in Excel

[divya nila]

Hi,

I want to add Page border (for four sides) around each page in the
spreadsheet of excel. My excel is having multipages in one spreadsheet
and it is having nearly 6 spreadsheets. I have used PageBreaks to
determine last row of a page but the pagebreak is 0 if i have only one
page in a spreadsheet. So i want to know how to determine the last row
of a page.

Thanks in Advance.

 

[Charlotte E.]

LastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count

CE


Den 21.08.2013 12:20, divya nila skrev:

 

[GS]

Hi,

I want to add Page border (for four sides) around each page in the
spreadsheet of excel. My excel is having multipages in one spreadsheet
and it is having nearly 6 spreadsheets. I have used PageBreaks to
determine last row of a page but the pagebreak is 0 if i have only one
page in a spreadsheet. So i want to know how to determine the last row
of a page.

Thanks in Advance.

This will vary dependant on several factors like RowHeight,
PageMargins, and FontSize, for instance. Best way to determine the last
printed page row is to do PrintPreview and observe the dotted page
outline, then set your borders to fit.

If you note a consistent number of rows for a printed page then you can
use that in VBA to set PageBreaks...

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[isabelle]

hi,

MsgBox Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

isabelle

Le 2013-08-21 06:20, divya nila a écrit :

 

[isabelle]

it same goes (nearly) for the last column

Sub test_Last()
'LastRow
LastRow = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'LastColumn
LastColumn = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Application.Goto Range(Cells(1, 1), Cells(LastRow, LastColumn))
End Sub

isabelle

Le 2013-08-21 11:31, isabelle a écrit :

 

[Claus Busch]

Hi Isabelle,

Am Wed, 21 Aug 2013 11:52:56 -0400 schrieb isabelle:

Sub test_Last()
'LastRow
LastRow = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'LastColumn
LastColumn = Cells.Find("*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Application.Goto Range(Cells(1, 1), Cells(LastRow, LastColumn))
End Sub

the OP looks for the last row of the PAGE not the last row of the sheet.
But if there is only 1 page into the sheet HPageBreaks.Count = 0 and
HPAgeBreaks.Location fails


Regards
Claus B.

 

[Claus Busch]

Hi,

Am Wed, 21 Aug 2013 11:20:26 +0100 schrieb divya nila:

I want to add Page border (for four sides) around each page in the
spreadsheet of excel. My excel is having multipages in one spreadsheet
and it is having nearly 6 spreadsheets. I have used PageBreaks to
determine last row of a page but the pagebreak is 0 if i have only one
page in a spreadsheet. So i want to know how to determine the last row
of a page.

try:
Sub PageBreak()
Dim intHPB As Integer

With ActiveSheet
If .HPageBreaks.Count = 0 Then .Cells(65, 1) = "Test"
MsgBox .HPageBreaks(1).Location.Row - 1
.Cells(65, 1).Clear
End With
End Sub


Regards
Claus B.

 

[divya nila]

hi Busch,

thank you for the suggestion. But i am looking forward to a code withou
hardcoding. I have tried already what you have suggested. But our user
doesn't like it.