Set cell width and height based on cm value

[288enzo]

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.

    Columns("A:A").Select

    Selection.RowHeight = [B1]

    Selection.ColumnWidth = [C1]

 

[maabadi]

Test this:

Columns("A:A").Select
    Selection.RowHeight = [B1] * 28.9
    Selection.ColumnWidth = [C1] * 4.5

 

[288enzo]

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]

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

Columns("A:A").Select
    Selection.RowHeight = [B1] * 28.9 * 2.5
    Selection.ColumnWidth = [C1] * 4.5 * 2.5

 

[288enzo]

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

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]

Good Luck & Glad We can help.

 

[288enzo]

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]

For column width try

   Dim i As Long
   With Range("A:A")
      For i = 1 To 3
         .ColumnWidth = Application.InchesToPoints([C1]) * (.ColumnWidth / .Width)
      Next i
   End With

 

[288enzo]

Genius! It works perfectly.

Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.