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

 

[brian.wethington]

I am really not sure what you want to do with this but then again I have not really understood much of this post. One thing I have noticed is I a believe you were looking for the row height of A1:A40 and I am seeing you continue to use A1:A10. Perhaps this would help you get what you were looking for

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

As for the following function:

Option Explicit
Function RHeight(Rng As Range)
Dim Ce As Range

For Each Ce In Rng
    RHeight = RHeight + Ce.RowHeight
Next Ce

End Function

Hope this helps!

 

[Mister H]

Help... PLEASE!!!

Just thought I would bump this since I had not seen any further replies. I hope someone can help figure this out. It will be great if I can get it to function properly.

THANKS...
Mark

 

[brian.wethington]

You bumped it 4 minutes after my last reply?

 

[Mister H]

Hi:

You bumped it 4 minutes after my last reply?

Just to clarify, it may have been 4 minutes after your reply but it was 5 minutes before my notification of your reply came to me and when I staraed posting your reply was not visible to me? No biggie, I just wanted to mention that so I don't look like a complete fool.

I also only refer to A1:A10 in the code because that is what Hotpepper wrote. I adjusted it at my end. Sorry for the confusion. The A1:A40 (as you stated is what I was chagning it to

It is unfortunate for me that people are not understanding what I am babbling about. I just can't seem to type the right wording for my situation. When I read:

I need the User to Always see the Header Row which is Row 16.

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

The above makes sense to me but of course I have the spreadsheet sitting here in front of me. I can tell you that the next design will not be similar. Live and learn.

I have not yet tried your suggestion but I was wondering if you coudl further elaborate on the Function? Do I just place that code in the Workbook? I am not familiar with Functions. Sorry

Anyway, I really apprecuate everyones help and I apologize for the confusion I have caused.

Have a GREAT day ALL,
Mark

 

[brian.wethington]

As for the function:

Insert a module in the workbook you will be using it in and paste the function there. Then to use it in a sheet (if that is in fact your intent) you will type

=RHeight(Range)

Where range is a single contiguous column of the rows you wish to check. As far as I am aware there is no way to make this function volatile as changing a row height will not set it off, so you will have to calculate it each time (to me this would mean that it would probably be better to perform the function within my code).


As for your designed sheet perhaps you could post it using Colo's (See link below).


Perhaps this will help us understand your needs.. normal header showing and how you want it to show with the second header according to your needs.

 

[Mister H]

Hi Brian (and Whomever else that is Looking):

I have been away from my desk for a bit but now that I am back I thoguht I would TRY to post part of my sheet so you can see what I am talking about...

FINALTemplate.xls
	A	B	C	D	E	F	G	H	I	J
2	DailyDepositSummary-RoyalBank:
3	1		SelectMinistryhere			Account#:100000085-1	$0.00
4	1	018	MGS	MinistryofGovernmentServices				Debit		018
5	1	18	Account#:100000085-1			Number	ChequeAmount	Reconciled		InvoiceAmount
6	1		AR	ARReceipts		0	$-	Yes		$-
7	1		MISC	Misc.Receipts		0	$-	Yes		$-
8	1		REV	Revenue		0	$-	Yes		$-
9	1		ER	ExpenditureRefund		1	$1.00
10	H-ER		NPC	NetPayClearing		0	$-
11	AR		H-ER			1	$1.00			000
12	MISC	CHQ	HasthisDepositbeenVERIFIED?	VerifiedBy: (Signature)	Date:		1-00-JAN-00	###
13	REV	Visa		X	Completedby:		#ofcheques=1
14	ER	MC			DepositSlipNumber&DepositAmount:	1	$1.00	###
15	NPC	Amex				$-	DepositBalanced
16	Type		CustomerName orPayee	DateofCheque (dd-mm)	ChequeNumber (NObeginning0's)	ChequeAmount	Description	Min	poststale	Balancing
17	H-ER	CHQ	test	06-Jun-07	123	$1.00		018		018
18	MISC	CHQ						018		018
Input Sheet

[/img]

Well it is not what it really look like but I think you can get the idea. Currently Row 1 is hidden. Row 16 is the Header row. Once Row 16 is not visible to the user anymore I wanted Row 1 to magically appear and remain at the top so the user can still tell what field they are in...

Once I get back from my meeting I will check out your other suggestion I just thought I would post this (as requested) to see if it helps...

THANKS Again,
Mark

 

[brian.wethington]

So something like this work?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range, Ce As Range, x As Single

Set Rng = Range("A2:A" & Target.Row)
For Each Ce In Rng
    x = Ce.RowHeight + x
Next Ce
ActiveSheet.Unprotect
Rows(1).Hidden = x < 570
ActiveSheet.Protect

End Sub

 

[Scott Huish]

Once Row 16 is not visible to the user anymore I wanted Row 1 to magically appear and remain at the top so the user can still tell what field they are in...

That would still require freezing panes for row 1, even if it was done later in code. But it looks like most data entry is done in row 17 down. If you can see rows 2-15 when there is enough room to see all that, why can't you freeze it at row 16 so you can still see it when data is being entered in rows lower in the spreadsheet?

 

[Joe Was]

This will work only when a cell is selected by the user, it does not work when the scroll bars are used.


Private Sub Workbook_Open()
'ThisWorkbook code module only!

Sheets("Sheet1").Range("A2").Activate
ActiveWindow.FreezePanes = True
Sheets("Sheet1").Range("A1").EntireRow.Hidden = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Sheet module code, like: Sheet1, only!

If Target.Row < ActiveWindow.VisibleRange.Rows.Count Then
Range("A1").EntireRow.Hidden = True
Else

Range("A1").EntireRow.Hidden = False
End If
End Sub

 

[Mister H]

THANKS........

Hi Brian (& Hotpepper):

I think you got it.

I adjusted the number (570 to 727) to suit my requirements. I recalculated what number I needed in there and it seems to be working. AWESOME.

The only little glitches that I can see is:

1) Both Row 1 and Row 16 appear on screen once the number 727 is reached. When the number is reached then Row 1 appears and this pushed the Active Cell off screen. However as soon as I Tab out it the Active Cell is visible and the Duplicate Row is gone.

2) The other glitch is that once I reach the designated number (727) Row 1 appears but if the users decides that they need to change something on the line above then once they move Up 1 Row the Header Row dissappears.

If there is not a fix for those little problems then I will just explain to the Users in advance of what they may experience.

For the record I still did not try the function code you supplied. I am assuming that my best bet is to stick with your latest code?

THANKS again to BOTH of you for sticking with this CONFUSING Post. Your time and efforts are very much appreciated.

Have a GREAT day,
Mark