PDA

View Full Version : Solved: Calculating Height of a range



jazzyt2u
07-28-2008, 10:28 AM
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.
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)

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? :think:

Mavyak
07-28-2008, 10:40 AM
rngTD20.Height

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
07-28-2008, 02:12 PM
Thank you...

TomSchreiner
07-28-2008, 05:45 PM
"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
07-29-2008, 04:49 AM
I couldn't find a unit of measurement on the help page so I assumed twips. Thanks for the correction!

Dr.K
07-29-2008, 05:43 AM
Yep, everything in Excel is measured in Points.
From Wikipedia:


In typography (http://en.wikipedia.org/wiki/Typography), a point is the smallest unit (http://en.wikipedia.org/wiki/Typographic_unit) of measure, being a subdivision of the larger pica (http://en.wikipedia.org/wiki/Pica_%28unit_of_measure%29). It is commonly abbreviated as pt. The traditional printer's point, from the era of hot metal typesetting (http://en.wikipedia.org/wiki/Hot_metal_typesetting) and presswork (http://en.wikipedia.org/wiki/Printing_press), varied between 0.18 and 0.4 mm (http://en.wikipedia.org/wiki/Milimeter) depending on various definitions of the foot (http://en.wikipedia.org/wiki/Foot_%28unit_of_length%29).
Today, the traditional point has been supplanted by the desktop publishing point (also called the PostScript (http://en.wikipedia.org/wiki/PostScript) point), which has been rounded to an even 72 points to the inch (http://en.wikipedia.org/wiki/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
07-29-2008, 08:35 AM
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
07-29-2008, 09:24 AM
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
07-29-2008, 12:15 PM
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
07-29-2008, 01:19 PM
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
07-29-2008, 01:23 PM
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
07-30-2008, 09:46 AM
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
07-30-2008, 10:36 AM
It is all explained in VBA help. Just lookup For, and For ... Next will be there