Row height randonly(?) changing to zero

[MOB]

I've recently made some improvements to a reporting spreadsheet at the company I'm currently working at, nothing spectacular, just automating tasks etc.

I am now seeing the occasional row height changing to zero, too many times to be a coincidence, and I'm fairly sure not user created.

None of my code affects row height as far as I'm aware.

Any pointers as what could be causing this?

Also, is there a way to use a formula to report the presence of a zero height row somehow? I have looked at this great option https://www.mrexcel.com/forum/excel-questions/501867-there-formula-displays-row-height.html by Fazza, but I could do with a quick formula that looks at a range and tells me, rather than multiple formulae?

I think I know the answer to the last point but here's hoping!

TIA

 

[Yongle]

How about a User Defined Function?

After adding the code....use this formula in one cell in your sheet
=ZeroHeight()

Used range tested and formula returns either "none" or list of row numbers

Paste to a Standard module

Function ZeroHeight() As String
    Application.Volatile
    Dim r As Range, msg As String:  msg = "none"
    For Each r In Application.Caller.Parent.UsedRange.Rows
        If r.RowHeight = 0 Then
            If msg = "none" Then msg = r.Row Else msg = msg & ", " & r.Row
        End If
    Next r
        ZeroHeight = msg
End Function

 

[MOB]

I like that thanks!

What about if I only wanted to check rows 6 to "lastrow"?

 

[Yongle]

Keeps things simple...

For Each r In Application.Caller.Parent.UsedRange.Offset(5).Rows

If you wanted more flexibilty then the funtion could be amended to take a string of row numbers that could be ignored

 

[MOB]

Thanks - tried the amendment, in my example data the rows are from 6 to 66 - 66 being "lastrow" - the code is returning many rows after 66 that I need to ignore

 

[Yongle]

formula now in this style - numbers are first row and last row
=ZeroHeight(5,50)

Function ZeroHeight(firstRow As Long, lastRow As Long) As String
    Application.Volatile
    Dim r As Range, msg As String:  msg = "none"

    For Each r In Application.Caller.Rows(firstRow).Resize(lastRow - firstRow + 1, 1)
        Debug.Print r.Address, r.RowHeight
        If r.RowHeight = 0 Then
            If msg = "none" Then msg = r.Row Else msg = msg & ", " & r.Row
        End If
    Next r
        ZeroHeight = msg
End Function

If you prefer to calculate last row, then I need a starting point
- perhaps enter a cell A5 as the first cell (gives row 5) and for VBA to determine last cell with formula\value in that column?
- some other determinant?

 

[MOB]

Great thanks

Ended up with this;

=ZeroHeight(6,LOOKUP(2,1/(A:A<>""),ROW(A:A))-1)

Which determines the last populated row.

Many thanks for your help!

 

[Yongle]

Thanks for feedback