Solved: Calculating Height of a range

[jazzyt2u]

Hi,

I need to calculate the height of a Range of rows. Some rows may be texted wrapped and therefore taller than others. I will be pasting each range I create into Word and will need to know if two ranges can fit on one page or will I need insert a page break and then dump the Column headers and other data that proceeds each range that starts a new Page.

I will be using the following to generate my ranges.
[vba] ActiveCell.Offset(1,-14)
Dim rngT20 As Range
Dim rngD20 As Range
Dim rngTD20 As Range
Set rngT20 = Selection
ActiveCell.Offset(6, 14).Select
Set rngD20 = Selection
Set rngTD20 = Range(rngT20, rngD20)
[/vba]
Also,
Could someone breakdown the For...Next statement? I see them alot and not sure how to use them...also why does everyone always seem to use j or i when writing the For...Next statement?

[Mavyak]

[VBA]rngTD20.Height[/VBA]

That will get you the height of the range. I believe the value returned is in "twips", which is a printing measurement. To get inches, divide by 1440.

[jazzyt2u]

Thank you...

[TomSchreiner]

"That will get you the height of the range. I believe the value returned is in "twips", which is a printing measurement. To get inches, divide by 1440."

Am pretty sure that height is in points??? 72 points per inch.

[Mavyak]

I couldn't find a unit of measurement on the help page so I assumed twips. Thanks for the correction!

[Dr.K]

Yep, everything in Excel is measured in Points.
From Wikipedia:

In typography, a point is the smallest unit of measure, being a subdivision of the larger pica. It is commonly abbreviated as pt. The traditional printer's point, from the era of hot metal typesetting and presswork, varied between 0.18 and 0.4 mm depending on various definitions of the foot.
Today, the traditional point has been supplanted by the desktop publishing point (also called the PostScript point), which has been rounded to an even 72 points to the inch.
(1 point = 127⁄360 mm = 352.7 ?m) In either system, there are 12 points to the pica.

This is a holdover from the fact that Excel was originally a Mac product, and the Mac was very geared towards desktop publishing.

[Emily]

The row height is measured in points (1pt = 1/72 inch or about 1/28 centimeters). A point is a "unit of measure referring to the height of a printed character" (quoting from the VBA help file for the RowHeight property).

[jazzyt2u]

Thanks!!!!! What's confusing me is let's say I change a row's height to 19. When I use the formula above the value is 18.75. Then If I change the row height to 18.75 it then says 18.75 and 20 rowheight is 19.5...weird.

Can any of you also explain the whole For...Next statement. That too has me a little confused.

[Bob Phillips]

It works in multiples of .75, so you cannot set it to 19. You might think you di, but Excel changed it.

Anf what For ... Next loop are you referring to.

[jazzyt2u]

Ahhhhh thanks....

For...Next in General.... When to use it and how to use it... they confuse me because it looks like they are doing increments but I don't see how...and it's not always clear when it stops like say a Do...Loop Until

[Bob Phillips]

They iterate through a collection, processing each item in the collections in the order they are located within the collection, such as Worksheets, Workbooks, cells etc.

[jazzyt2u]

Hi,

Can you go into a little more detail than that... examples are nice...lol
Please please please...
I'm not even sure when to use them

[Bob Phillips]

It is all explained in VBA help. Just lookup For, and For ... Next will be there