Row Height in VBA

[markh1182]

Hi, I have this code that someone on here put together and which I have modified slightly. What I now want is for this to repeat so that the height average of every 59 rows, rather than just the first one, it will put an X in column P.

Sub RowHeight()

tHeight = 0
For n = 2 To 60
x = Range("J" & n).RowHeight
tHeight = tHeight + x
Next

tHeight = Round((tHeight / 12.75), 0)

'rows to exclude
exRows = tHeight - 59

Range("P" & (60 - exRows)).Select
ActiveCell.Value = "X"

End Sub

 

[Andrew Poulsom]

Is it?

Sub RowHeight()
    tHeight = 0
    Count = 1
    For n = 2 To ActiveSheet.UsedRange.Rows.Count
        If n Mod 60 <> 0 Then
            x = Range("J" & n).RowHeight
            tHeight = tHeight + x
        Else
            tHeight = Round((tHeight / 12.75), 0)
'           rows to exclude
            exRows = tHeight - 59
            Range("P" & ((60 * Count) - exRows)).Value = "X"
            tHeight = 0
            Count = Count + 1
        End If
    Next n
End Sub

It might not give the desired results because n starts at 2. If that's the case please explain the purpose of the code.

 

[markh1182]

Hi, thanks for your help. It's almost there but not quite.

The reason this is required is because the height equivalent of every 59 rows (excluding the title) needs to form the size of each page. At the bottom of each page I need to put the words "Page Summary". I was going to use the X to be able to pick this up.

 

[Andrew Poulsom]

Try:

Sub RowHeight()
    Dim HeadHeight As Double
    Dim tHeight As Double
    Dim n As Long
    HeadHeight = Cells(1, 1).RowHeight
    tHeight = 0
    For n = 1 To ActiveSheet.UsedRange.Rows.Count
        tHeight = tHeight + Cells(n, 1).RowHeight
        If tHeight > (752.25 + HeadHeight) Then
            Range("P" & n - 1).Value = "X"
            tHeight = HeadHeight + Cells(n, 1).RowHeight
        End If
    Next n
End Sub