print preview , value of ending line on page

[majlo]

Hello guys, I would like to know is it possible to find out what is value(in inches or cm) of the last row at bottom on the page in print preview in excel.

[p45cal]

In the absence of other replies, I tentatively put forward this suggestion ('cos I suspect there's probably a property somewhere which could be used instead!):
Sub blah()
OriginalBotMarg = ActiveSheet.PageSetup.BottomMargin
BotMarg = 72
Increment = 2048
ActiveSheet.PageSetup.BottomMargin = BotMarg
Do
Increment = Increment / 2
If ActiveSheet.HPageBreaks.Count = 0 Then
BotMarg = BotMarg + Increment
Else
BotMarg = BotMarg - Increment
End If
ActiveSheet.PageSetup.BottomMargin = BotMarg
Loop Until Increment < 1 'change resolution here
MsgBox BotMarg / 72 & " inches, or " & BotMarg & " points."
ActiveSheet.PageSetup.BottomMargin = OriginalBotMarg 'restore original.
End Sub where BotMarg is the Bottom Margin (measured from where, I'm not sure). It may give you some ideas.. the default printer might have to be the printer you intend to print to. It's not a proper 'binary chop' search.

You can use
Application.InchesToPoints
or
Application.CentimetersToPoints
in the code if you prefer, otherwise
72 points = 1 inch
and
28.3464566929134 points = 1 cm.

[majlo]

thnx much, its almost thati was looking for, but unfortunately it s not calculating real bottom line of my table. It is giving me the value of the last row, but its not the same line, the larger height of the row, the bigger mistake.
P.S. why is increment=2048

[p45cal]

OK, try
Sub blah()
OriginalBotMarg = ActiveSheet.PageSetup.BottomMargin
BotMargTooBig = 2048
BotMargTooSmall = 0
BotMarg = (BotMargTooSmall + BotMargTooBig) / 2
Do
ActiveSheet.PageSetup.BottomMargin = BotMarg
If ActiveSheet.HPageBreaks.Count = 0 Then
BotMargTooSmall = BotMarg
Else
BotMargTooBig = BotMarg
LastRight = BotMarg
End If
BotMarg = (BotMargTooSmall + BotMargTooBig) / 2
Loop Until BotMargTooBig - BotMargTooSmall < 1 'change resolution here
ActiveSheet.PageSetup.BottomMargin = LastRight
MsgBox LastRight / 72 & " inches, or " & LastRight & " points."
ActiveSheet.PageSetup.BottomMargin = OriginalBotMarg 'restore original.
End Sub Why 2048? because it repeatedly divides by 2 neatly all the way down to 1. It doesn't have to.

[majlo]

thanx mucho again, its even better,still got that little gap beetween margin and bottom line, but I came to an idea, to set row under last line on row height =1, and thats great. I was wondering what to add to this great code, so this macro would work on more pages in one sheet...to give me value of margin on 1st page, on 2nd page, etc...hope not bothering you..this, you gave me since is quite enough thanx again.

[p45cal]

It's doable, but it would be ugly. Setting the bottom margin sets the bottom margin on all pages. It would/might involve copying the whole sheet, doing something similar (similar, because the earlier code depended on there being only one page to print) to what we've done for the first page, then deleting all the rows of that first page and doing it again.. and again, until all the data had gone. I'm not prepared to do the coding for that, especially as I haven't convinced myself that it's the best way to do it.
Sorry :( ..unless I find myself with nothing to do.. well... who knows?

[majlo]

no problem, thanx again for this since..