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]

need to change something on the line above then once they move Up 1 Row the Header Row dissappears.

Like I said you need to freeze the panes to some degree or another. I still don't understand though why you can't freeze it for row 16, which would also eliminate the need for a hidden row 1.

 

[Mister H]

Hi Hotpepper:

I do have Row 1 Frozen but it is hidden. I guess I did not mention that before. Sorry about that. Once it is Unhidden it remains at the top (Frozen).

As for Freezing Row 16. If I do that then ALL the rowsabove are frozen as well. As you can see from my spredsheet this would only leave 1 or 2 Data Input Rows visible (row 17 and 18 in my screenshot). This would not allow the users enough room to work. There may be instances where a cheque is received but it will be split onto 5 or 6 lines. The user needs to see more of the Data Fields.

Maybe I am confused... Do you know of a way that Row 16 will Freeze at the Top and Rows 2 to 15 will dissappear off screen?

If that is the case then that would work because if Row 16 is at the Top then that leaves Rows 17 to 32 visible.

I hope that better explains it.

THANKS for ALL your efforts,
Mark

 

[Scott Huish]

If the user does not need to see the data in rows 2 through 15 in data entry, why not just put this part on a different sheet? Or barring that, why not hide rows 2-15 and just have a couple macros with shortcut keys to hide/unhide these rows?

 

[brian.wethington]

Like I said you need to freeze the panes to some degree or another

I am assuming he has a constant frozen pane at A2 (otherwise the code I offered him does nothing). Now as to why he can't have the pane frozen at A16 I cannot answer that logically, but he seems dead set that this will not help him... of course what do I know I am still trying to figure this out entirely.

need to change something on the line above then once they move Up 1 Row the Header Row dissappears.

Perhaps use of scrollrow might be beneficial (as default I used 2 here since I am not sure exactly what you would be looking for):

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
If x < 727 Then ActiveWindow.ScrollRow = 2
Rows(1).Hidden = x < 727
ActiveSheet.Protect

End Sub

 

[Mister H]

Hi Hotpepper:

I know it is not designed the best. The macro thing may very well work to hide and unhide. For now I have implemented the code that Brian altered.

The users need to enter data in Rows 2 to 15 after that I guess it can be hidden.

I will look at all options a little later.

THANKS Again for your assitance and suggestions.

Have a GREAT night,
Mark

EDIT: As soon as I posted I got the e-mail notifying me Brian had replied as well as Hotpepper. THANKS Brian. I will check out your revised solution when I get home tonight. I am just heading out of the office. I will report back...

 

[brian.wethington]

Hotpepper's suggestion should be a simple implementation (simply hiding the data based on the row you are on instead of hiding/unhiding frozen header row that is put 2 times on a sheet).Again this will mostly depend on what is needed though.

 

[Joe Was]

Unless I am missing what it is you want, which I could be, this code I posted above works. Did you try it?
I used the sheet you posted and the code I posted, if I changed row hights or not, and when I selected any range in the default view, no headder row is shown. If I extended the range selection beyond the default range, the headder row is shown. This code is dynamic and self adjusting, it does not require the use of pre-set values or dimentions. The moment you hard-code, any values that could change, you remove flexibility.


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


Now looking at your sheet I would set it so the portion above row 16 fills the view, like a form view. And if the user selects any other range beyond this then the view jumps, having row 16 as the new top row as a data view. This way the view flips or or the other: psudo-form or data, but not both.

 

[Mister H]

Hi Joe Was:

SORRY I did not reply earlier. I have not yet tried your solution as i have been on vacation for a few days. I just returned to work today and saw your reply so I thought I would update the post.

I will try out your solution and report back once I get my backlog of work caught up.

THANKS to everyone for your assistance...

Take Care,
Mark

 

[Mister H]

Hi Joe Was:

SORRY for the delay in getting back to you. I keep getting pulled in different directions...

Anyway, I just tried to implement the code you supllied but I am not getting any results? I can not get the hidden row to appear. I imagine I have left out a step.

I placed this one code in the Workbook:

Private Sub Workbook_Open()
'ThisWorkbook code module only!


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

I placed this code in my Worksheet titled "Input Sheet":

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

I then Saved It/Closed It and Re-Opened It, started scrolling down but I could not get Row 1 to magically appear? Did I leave out a step?

I am not certain what you mean by this:

I would set it so the portion above row 16 fills the view, like a form view. And if the user selects any other range beyond this then the view jumps, having row 16 as the new top row as a data view. This way the view flips or or the other: psudo-form or data, but not both.

Is there a set up step that I am supose to complete? THANKS for sticking with the Post Joe Was.

Bye 4 Now,
Mark

 

[Joe Was]

The code works by utilizing the current view. So you need to set the Screen Zoom so only 15 rows show on the Screen. Then the code works!