EXCEL - IF statement with RowHeight as condition not working

Question

I have a long Excel database which I created running a VBA Macro I didn't originally create (thus I don't know exactly how it works). The issue is that for some reason, the VBA Macro shrinks the height of some rows, which makes the text contained inside not always 100% visible. I've already made some changes to the Excel file, so I don't really want to search for the flaw in the VBA I used, so I wanted to write a short VBA code that would increase the height of the rows with shrinked height:

Sub Macro1() Dim rowIndex As Integer Dim lastRowIndex As Integer lastRowIndex = 15000 For rowIndex = 7 To lastRowIndex: If ActiveSheet.Rows(rowIndex).RowHeight = 9.6 Then ActiveSheet.Rows(rowIndex).RowHeight = 10.8 End If Next rowIndex End Sub

For some reason this code isn't executing the way I thought (it does nothing). I checked whether I used the right height value, but everything seemed fine.

Did I miss something?

Your code works fine on rows that are actually 9.6 tall. Why not try `If ActiveSheet.Rows(rowIndex).RowHeight < 10.8 Then` ? — , Aug 31 '18 at 08:24
Code is okay but I think RowHeight cannot take every value. If I enter a height of 9.6 it actually becomes 9.5. So, it might be possible that `RowHeight = 9.6`never becomes true — Storax, Aug 31 '18 at 08:31
Might not be the reason, but comparing floating point values in any language is [notoriously error-prone](https://stackoverflow.com/questions/588004/is-floating-point-math-broken). For example, `Debug.Print 0.1 * 96` outputs `9.6`, but `Debug.Print 0.1 * 96 = 9.6` outputs `False`. — jsheeran, Aug 31 '18 at 10:31

score 1 · Answer 1 · answered Aug 31 '18 at 08:26

Your code works fine on rows that are actually 9.6 tall. Try increasing any row that is less than 10.8 tall.

Sub Macro1()
    Dim rowIndex As Integer
    Dim lastRowIndex As Integer
    lastRowIndex = 15000
    For rowIndex = 7 To lastRowIndex
        If ActiveSheet.Rows(rowIndex).RowHeight < 10.8 Then
            ActiveSheet.Rows(rowIndex).RowHeight = 10.8
        End If
    Next rowIndex
End Sub

score 1 · Answer 2 · answered Aug 31 '18 at 08:38

1

It seems you cannot have a RoweHeight of 9.6

Sub TestItA()
    ActiveSheet.Rows(7).RowHeight = 9.7
    Debug.Print ActiveSheet.Rows(7).RowHeight
End Sub

According to this post you can only set the height in fixed "increments", or to be more precise in "pixel"

Although help, and the Row Height Dialog, say to set the row height in points, Excel actually sets the row height in pixels.

1 pixel = 72 / 96 points= 0.75 points.

That is why you can only change the row height in increments of 0.75 points.

answered Aug 31 '18 at 08:38

Storax

11,158
3
16
33

fwiw, I set rows 7:15 to a rowheight of 9.6, adjusted lastRowIndex and the code ran as expected. Ran it on a surface pro. – Aug 31 '18 at 10:52
As mentioned in the linked post it seems to depend on ppi. For me the output of the code I posted above is 9.75. – Storax Aug 31 '18 at 11:03
Yes, I'm sure if I knocked down my screen resolution I would have a different result. It's just not something that can be depended upon; particularly from one machine to the next. – Aug 31 '18 at 11:06
1

Yes, right. Maybe the OP should try to fix his first problem _The issue is that for some reason, the VBA Macro shrinks the height of some rows_ – Storax Aug 31 '18 at 11:11

EXCEL - IF statement with RowHeight as condition not working

2 Answers2