Set cell width and height based on cm value

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
Hi all - I have a simple script to set the width and height of cells based on the numbers in B1 and C1. What I need help with is how to set the width and height based on centimeters. Thanks for any guidance.

VBA Code:
    Columns("A:A").Select

    Selection.RowHeight = [B1]

    Selection.ColumnWidth = [C1]
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,784
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Test this:
VBA Code:
Columns("A:A").Select
    Selection.RowHeight = [B1] * 28.9
    Selection.ColumnWidth = [C1] * 4.5
 

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
In B1 I have 2.03
In C1 I have 1.14

The results after running the script made each cell in column A .46 ...

Ugh, my mistake. I meant inches. I'm guessing your formula works if I indeed needed centimeters. I'm so sorry, not sure what I was thinking.

How did you come up with the conversion ratio? Perhaps I can come up with inches if I knew how you did it.

Thank you
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,784
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Go to View tab, Select Page Layout, Then Right Click on Column for width or Row for height, You see Value Based Default measure unit
If you want Change Measure Unit Go to File, Option, Advanced,
Go To Display Section, Ruler Unit and change it to what you want.
AND for Inch Mutiply them At 2.5 Then
VBA Code:
Columns("A:A").Select
    Selection.RowHeight = [B1] * 28.9 * 2.5
    Selection.ColumnWidth = [C1] * 4.5 * 2.5
 

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Go to View tab, Select Page Layout, Then Right Click on Column for width or Row for height, You see Value Based Default measure unit
If you want Change Measure Unit Go to File, Option, Advanced,
Go To Display Section, Ruler Unit and change it to what you want.
AND for Inch Mutiply them At 2.5 Then
VBA Code:
Columns("A:A").Select
    Selection.RowHeight = [B1] * 28.9 * 2.5
    Selection.ColumnWidth = [C1] * 4.5 * 2.5
Thanks, I did read up on how to change them in settings; but, this is something I will be sharing with others and don't want to put them in a position of having to change their settings as well.

The cell height worked dead on @ 1.14 inches. Cell width came out to 1.84 inches, target was 2.03. Halfway there. Thank you
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,784
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Good Luck & Glad We can help.
 

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I've been playing around with the formula and it's driving me crazy. For the width the formula 4.95 * 2.5 gives me 2.02 inches, the formula 4.96 * 2.5 gives me 2.04 inches. What am I missing here? How do I get to the magical 2.03 inches? I even tried 4.9555
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
For column width try
VBA Code:
   Dim i As Long
   With Range("A:A")
      For i = 1 To 3
         .ColumnWidth = Application.InchesToPoints([C1]) * (.ColumnWidth / .Width)
      Next i
   End With
 

288enzo

Board Regular
Joined
Feb 8, 2009
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
Genius! It works perfectly.

Thank you very much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,485
Messages
5,636,606
Members
416,929
Latest member
Nitil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top