Row Height in VBA

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
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.

Code:
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

Code:
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

New Member
Joined
Apr 25, 2006
Messages
48
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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
 

Forum statistics

Threads
1,136,268
Messages
5,674,732
Members
419,523
Latest member
Urnovio

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