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.

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

CE


Den 21.08.2013 12:20, divya nila skrev:

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.

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[s], 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

hi,

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

isabelle

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

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.

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 :
hi,

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

isabelle

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

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.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

hi Busch,

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