Can VBA keep track of Total Row height???

[Mister H]

Hi All:

I was wondering if VBA or Excel has the capability to add and total Row Height or Pixels? For example I have my Rows set to a height of 14.25 (19 pixels). My Range that I am working with is A2:T41 for a TOTAL ACCUMULATED ROW HEIGHT of 570 (40 Rows X 14.25) and 760 Pixels (40 X 19).

What I would like to accomplish is that once the TOTAL ACCUMULATED ROW HEIGHT of my Range reaches 570 then Row 1 appears. If the TOTAL is less then 570 then Row 1 remains Hidden.

Does anyone know IF and HOW this can be done? I need this due to the fact that the Rows will Autosize and I need to have my header Row appear. I can't just Freeze Row 1 because my actual Headers are on Row 16.

Hope this makes sense and hope that someone has a solution.

Bye 4 Now,
Mark

 

[Scott Huish]

Why can't you just freeze it for row 16?

 

[Mister H]

Hi Hotpepper:

Thanks for the reply.

Unfortunately if I Freeze Row 16 it only leaves about 2 rows visible for entering data due to the Information that is contained in Rows 2 to 15.

If there is a way to make the Window Freeze once Row 16 reached the top that would work as well.

THANKS,
Mark

 

[Mister H]

Hi All:

Does anyone on the morning crew have any ideas or solutions for me???

THANKS,
Mark

 

[Scott Huish]

I still don't quite understand what you are trying to accomplish, but does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, y As Single
For Each c In Range("A1:A10")
    y = y + c.RowHeight
Next
Rows(1).Hidden = y < 570
End Sub

 

[Mister H]

Hi Hotpepper:

For someone that is not sure of what I want it is you seem to be doing quite well... THANKS

I think you have the right idea but it doesn't seem to be working as it should (or should I say as I want). I see it is checking the Row Height but is it SUMMING them up? As far as I can see when I step through it it seems to be checking the Row Height of each Row but i can't see where it is keeping the total. Maybe I am missing somethig. From what I can see Row one would appear IF ONE of the Rows is Greater then 570.

Let me know if I have done something incorrect?

THANKS for sticking wiht this Post. I VERY much APPRECIATE it.

Bye 4 Now,
Mark

 

[brian.wethington]

For Each c In Range("A1:A10")
y = y + c.RowHeight
Next

This is taking the height of c and adding it to a variable y then going to the next c in the range and adding that height to y, continuing through until it has looped through each c.

 

[Mister H]

THANKS for the info Brian...

I think you have it right then Hotpepper but I can't get it to work flawlessly. One of the problems I am having is that I also need another code that I have to work in coonjunction with it. Things are never easy.

Here is the intial code I am using.I was wondering if a variable can be put in there. Let see if I can explain this right...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Rows(1).Hidden <> (Target.Row < 41) Then
    ActiveSheet.Unprotect
    Application.ScreenUpdating = False
    Rows(1).Hidden = Target.Row < 41
    ActiveSheet.Protect
    Application.ScreenUpdating = True
    End If

End Sub

Can your code somehow be added in here? I tried messing around a bit but can't get it to function properly.

I guess another way to do it (if possible) would be if your peice of code

For Each c In Range("A1:A10") 
    y = y + c.RowHeight

can be put into a formula that I can place on the actual spreadsheet. If it just adds up the total Row Height of the Range I can then maybe get a code that would say something like:

If Value B11 > 570
Then Row 1 is Visible
Else Row 1 is Hidden

Hope I make sense.

THANKS Again,
Mark

 

[Joe Was]

The easy way to do this is to lock the Screen Scroll to only let the user select a Defind Range of Cells, independent of their sizes!


Private Sub Workbook_Open()
'ThisWorkbook code!
'Save then open workbook to activate!
'Or add code to UserForm initialize event!

Sheets("Sheet1").ScrollArea = "A1:O24"
'To reset back to full Scroll:
'Sheets("Sheet1").ScrollArea = "A1:IV65536"
End Sub

Sub myLock()
Sheets("Sheet1").ScrollArea = "A1:O23"
End Sub

Sub myUnLock()
Sheets("Sheet1").ScrollArea = "A1:IV65536"
End Sub

 

[Mister H]

Hi Joe Was:

THANKS for the info but I am not sure exactly what your code will do for me (SORRY ). I need the User to Always see the Header Row which is Row 16. Will what you suggest do that?

Once Row 16 is Off Screen I need the users to see the Header Row still which I Duplicated on a Hidden Row 1

I am not sure that locking the scroll area will work for me. Maybe I am wrong. I will look at this in more detail when I get home...

If anyone knows how to write a formula for the code below I am pretty sure that I can get that to function for my purposes.

For Each c In Range("A1:A10") 
    y = y + c.RowHeight

Thanks for your suggestions everyone,
Take Care,
Mark