How do I make all rows 7,...23 exactly 8 pixels higher ?
 

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

Hi Markus:

Try this tiny macro:

Sub row_your_boat()
For i = 7 To 23
Rows(i).RowHeight = Rows(i).RowHeight + 8
Next
End Sub

--
Gary''s Student - gsnu2007b


"Markus Obermayer" wrote:

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Try this tiny macro:

Sub row_your_boat()
For i = 7 To 23
Rows(i).RowHeight = Rows(i).RowHeight + 8
Next
End Sub

To be clear for the OP, your subroutine increases the RowHeight by 8 points,
not 8 pixels.

I do notice a slight discrepancy (not your fault, the system's font handler
is doing it) in that the default standard height for Arial - 10 Points is
12.75 points high; adding 8 to that should make the rows 20.75 points high,
but my system shows it becoming 20.25 points high instead.

Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Here is some Windows-only code to resize rows which you pre-selected by a
specified number of pixels. Copy/paste the following code into your code
window and then run (Alt+F8) the IncreaseRowHeightsInPixels subroutine from
your spreadsheet after selecting the rows you want to increase by the number
of pixels you will specify when asked...

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSY = 90 'Pixels/inch in Y

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long
hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSY)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC
End Function

Sub IncreaseRowHeightsInPixels()
Dim R As Range
Dim Answer As Double
Answer = InputBox("How may pixels higher do you want?")
For Each R In Selection.Rows
R.RowHeight = R.RowHeight + Answer * PointsPerPixel
Next
End Sub


Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

According to Micorsoft, in the site listed below, 6 points equals 8 pixels.
However, it seems to me that I read somewhere that Excel adjusts row height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

"Markus Obermayer" wrote:

The height of the rows 7,..,23 is in a certain Excel sheet to low.

How can I increase the height (for only these rows) by 8 pixels ?

Or more general: If I mark rows n,....,m
How can I make them x pixels (resp y millimeter) higher ?

Markus

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

Hi Rick, No intent to challenge or dispute your code. I was just pointing
out that Excel is not conducive to precision measurement. I have noted that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

The 12.4 and 12.52 (row heights) that you cite are the automatically
determined point sizes corresponding to the selected font and its selected
font size... these (point sizes) cannot be set directly because the number
you pick might not correspond to a font size which will physically occupy a
whole number of pixels. But, because font sizing **is** set by the number of
pixels, you can exactly set the number of pixels a font will be allowed to
occupy which, in turn, has a calculated font size for that number of pixels
and, hence, an equivalent point size to match it. I'm not sure if I stated
that clearly or not; but it is, in essence, what the code I posted is doing.

Rick


"JLGWhiz" wrote in message
...
Hi Rick, No intent to challenge or dispute your code. I was just
pointing
out that Excel is not conducive to precision measurement. I have noted
that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or
some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution
settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each
and
for several different initial RowHeight settings and, in each test, it
has
always increased the height by the number of pixels I specified. Of
course,
that is not *proof* that it will always work for every combination of
font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world
display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number
of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and
then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this
happening
if you increase the spreadsheet's font size by one at a time and look at
the
resulting RowHeight that results. Here is the process being done for
Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel
number
increase. What I am pretty sure is happening is that the font is rounded
to
the nearest whole number of pixels for the give fontsize and then the
point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion
for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily
than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on
the
Windows operating system; I have no idea how all of this would translate
to
on a Mac).

Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

You explained it well enough. I understand the font/pixel relationship and
agree with everything you have stated about setting the pixel measurements.
I think we were on two different trains of thought, so rather than confuse
the OP, I'll drop off this one.

"Rick Rothstein (MVP - VB)" wrote:

The 12.4 and 12.52 (row heights) that you cite are the automatically
determined point sizes corresponding to the selected font and its selected
font size... these (point sizes) cannot be set directly because the number
you pick might not correspond to a font size which will physically occupy a
whole number of pixels. But, because font sizing **is** set by the number of
pixels, you can exactly set the number of pixels a font will be allowed to
occupy which, in turn, has a calculated font size for that number of pixels
and, hence, an equivalent point size to match it. I'm not sure if I stated
that clearly or not; but it is, in essence, what the code I posted is doing.

Rick


"JLGWhiz" wrote in message
...
Hi Rick, No intent to challenge or dispute your code. I was just
pointing
out that Excel is not conducive to precision measurement. I have noted
that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or
some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution
settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each
and
for several different initial RowHeight settings and, in each test, it
has
always increased the height by the number of pixels I specified. Of
course,
that is not *proof* that it will always work for every combination of
font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world
display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number
of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and
then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this
happening
if you increase the spreadsheet's font size by one at a time and look at
the
resulting RowHeight that results. Here is the process being done for
Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel
number
increase. What I am pretty sure is happening is that the font is rounded
to
the nearest whole number of pixels for the give fontsize and then the
point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion
for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily
than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on
the
Windows operating system; I have no idea how all of this would translate
to
on a Mac).

Rick

How do I make all rows 7,...23 exactly 8 pixels higher ?
 

JLG

You could set the row heights in mm which is not OS font or
resolution-dependent.

Ole Erlandson has code for setting row and column dimensions in mm.

http://www.erlandsendata.no/english/...vbawssetrowcol


Gord Dibben Excel MVP

On Sun, 9 Dec 2007 14:10:01 -0800, JLGWhiz
wrote:

Hi Rick, No intent to challenge or dispute your code. I was just pointing
out that Excel is not conducive to precision measurement. I have noted that
my settings are sometimes adusted, as an example, from 12.40 to 12.52 or some
variation other than what I set. Of course if there is a way to get
precision measurements to set, I would be interested in knowing how.

"Rick Rothstein (MVP - VB)" wrote:

According to Micorsoft, in the site listed below, 6 points equals 8
pixels.
However, it seems to me that I read somewhere that Excel adjusts row
height
and columns width based on the font used and screen resolution settings.
That means that if you expect to get a precision setting, forget it.

http://office.microsoft.com/en-us/ex...517241033.aspx

I have tested the code I posted with two different (in Windows only) DPI
settings against several fonts at several different font sizes for each and
for several different initial RowHeight settings and, in each test, it has
always increased the height by the number of pixels I specified. Of course,
that is not *proof* that it will always work for every combination of font,
fontsize and row height, but I suspect that it will.

Why? Well, font heights are **always** set to an exact number of pixels
where they are displayed (you can't have a font in the Windows world display
at a fractional pixel height on a monitor), so I'm thinking the point
measurements you see for row heights are determined from the exact number of
pixels a font at a given size occupies and not the other way around. That
would mean the ultimate row height settings are set by pixels first and then
translated back to points meaning calculation at the pixel level should
convert back to the point level properly. You can sort of see this happening
if you increase the spreadsheet's font size by one at a time and look at the
resulting RowHeight that results. Here is the process being done for Arial

FontSize Points Pixels
========================
10 12.75 17
11 14.25 19
12 15.00 20
13 16.50 22
14 18.00 24
15 18.75 25
16 20.25 27

Notice that the pixels go up either 1 or 2 per fontsize increase of 1 and
the the point size goes up either 0.75 or 1.5 in step with the pixel number
increase. What I am pretty sure is happening is that the font is rounded to
the nearest whole number of pixels for the give fontsize and then the point
size equivalent for that number of pixels (for the DPI setting of the
monitor) is then calculated and applied to the RowHeight property.

I also think Excel is "fooling" us by reporting the fontsize in as whole
numbers. In Windows, screen font sizes are usually floating point values
8.25, 10.75, etc. and these correspond to the pixel to points conversion for
the exact number of pixel the fontsize occupies on the monitor. I believe
Excel, like a lot of programs now-a-days, is reporting the printer's
fontsize setting... the printer has a much higher pixel density per inch
than the monitor and can support whole number point sizes more readily than
the monitor can.

Anyway, a lot of what I have discussed above comes from my first-hand
experience when dealing with fonts in the compiled VB world (again, on the
Windows operating system; I have no idea how all of this would translate to
on a Mac).

Rick