Can VBA keep track of Total Row height???

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why can't you just freeze it for row 16?
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

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

THANKS,
Mark :biggrin:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I still don't quite understand what you are trying to accomplish, but does this do what you want?

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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi Hotpepper:

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

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

Well-known Member
Joined
Jul 20, 2006
Messages
1,739
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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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...
Code:
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
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. :biggrin:

THANKS Again,
Mark
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi Joe Was:

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

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. :biggrin:
Code:
For Each c In Range("A1:A10") 
    y = y + c.RowHeight

Thanks for your suggestions everyone,
Take Care,
Mark
 

Forum statistics

Threads
1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

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