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
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
This should work for the general case. Select the rows you want to change
and then run (Alt+F8) this macro...
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 * 0.75
Next
End Sub
The 0.75 Pixels to Points conversion factor was obtained by calculation
using the information at this web page...
http://office.microsoft.com/en-us/excel/HA010346241033.aspx
after opening up the "How is row height measured?" link at the bottom of the
page. I tried setting the spreadsheet to different values and it seems to
work. However, it is possible that there is a dependency on the screen's
font size (dpi) setting (at least in the Windows world) since the 96 in the
"1-inch to 96-pixel" equivalency looks suspiciously like the 96 dpi setting
which is the standard for Windows (but which can be customized via the
Display Panel). If you are willing to work in Points directly, simply remove
the 0.75 multiplication factor and change the prompt in the InputBox
function call to say Points instead of Pixels.
Rick
Okay, yes, the conversion factor **is** dependent on the DPI setting (if you
are using Windows). Here is the Windows code to resize your selected rows 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
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
Rick
"Rick Rothstein (MVP - VB)" <rickNOS...@NOSPAMcomcast.net> wrote in
message news:ePOG%23IoOI...@TK2MSFTNGP02.phx.gbl...
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
Rick
"Rick Rothstein (MVP - VB)" <rickNOS...@NOSPAMcomcast.net> wrote in
message news:%234Kd2Ap...@TK2MSFTNGP04.phx.gbl...
"Rick Rothstein (MVP - VB)"
wrote in message
http://office.microsoft.com/en-us/excel/HP011517241033.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
Rick
"Jim Cone" <jim.c...@rcn.comXXX> wrote in message
news:OgVdVTpO...@TK2MSFTNGP04.phx.gbl...
Rick
"JLGWhiz" <JLG...@discussions.microsoft.com> wrote in message
news:F7AFC8B4-F07D-4826...@microsoft.com...
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/index.php?d=envbawssetrowcol
Gord Dibben Excel MVP
On Sun, 9 Dec 2007 14:10:01 -0800, JLGWhiz <JLG...@discussions.microsoft.com>
wrote: