Code TO HIDE or UNHIDE Row.

Mister H

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

I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the code:
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

What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...

SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.

Any ideas anyone??? :eek:

THANKS,
Mark :biggrin:
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I don't understand too well what you are after, but, it occurs to me that, maybe, you want the headers to remain visible all the time. If so, then I would just click on row 2, then on Window, Freeze Panes. Is this what you need?
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
H RalphA:

Sorry for the delay in replying but I just saw your post...

You are correct in that I want the Headers to be visible at ALL times but unfirtunately the Header row is Row 16 not Row 2. 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 one it reaches Row 16 that would work as well.

Any ideas?

THANKS,
Mark
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
But, if "row 1 contains the headers as well", why don't you just freeze only pane 1, thereby showing the headers at all time?
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
Hmm.... this sounds like you could adjust your layout a little better. What's in all those rows that you want to have on every page? Could you put it in the actual HEADER instead? (in FILE > PAGE SETUP)

If so, you could delete all those rows from the worksheet (putting it in the header instead) and set your "rows to repeat at Top" to only row 1 and free up a lot of screen real estate.
 

Mister H

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

Confusing hey...

The reason I also have the Headers in Row 1 is due to the fact that I could not Freeze Row 16 (ACTUAL Header Row) once it reached the top and I could not Freeze Rows 1 to 15 because as I mentioned earlier this only leaves a couple of data rows visible which seemed to confuse the Users.

So the Solution (requested by me and supplied by the Board) was to put the headers in Row 1 and hide it. Once Row 41 was reached Row 16 would be off screen and then Row 1 would appear so that the Users ALWAYS had a Header Row. I hope this makes sense

It is not the best design but it did work until I found out that the Rows had to be set up to Autofit to accomodate the data that some users had to enter. Now of course hen 1 or more rows change height this effects when Row 1 should appear.

THANKS,
Mark
 

Mister H

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

I know the layout from an Excel point of view is not good but the users wanted a pretty printout. :biggrin:

As for what goes in rows 2 to 15 are things like Client Name (Drop Down List), Account Number, Persons Name that is entering Data, Totals on what is contained in Rows 17 to 266, Deposit Slip Number, Amount and Date. These are not things that are entered in the Actual Data fields. Have I confused you a little more? :biggrin:

Hope this clarifies things a little. If not let me know and I will babble some more... :p

THANKS again to everyone who is trying to come up with a solution.

Bye 4 Now,
Mark
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Help!!! PLEASE.

Hi All:

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

THANKS,
Mark :biggrin:
 

TrippyTom

Well-known Member
Joined
Nov 16, 2004
Messages
587
Part of the problem is everyone uses different resolutions. For instance, I see way more than 41 rows on my screen because I run in 1600x1200 mode. As far as I know, there's no way to know if a row has gone "off-screen" because screen dimensions vary so much.

Your best bet is to modify your layout. Is there any reason all those header rows couldn't go onto a separate worksheet?
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
THANKS Tom,,,

I know the layout is not good but unfotunately I will need to stick with it for now as I have too much time invested.

The sheet I am referring to with the headers feeds about 25 different hidden sheets and I know if I start modifying at this late stage things will get messy.

For now I will just use the code I have and the Users will just have to deal with it. :biggrin:

Take Care,
Mark :p
 

Forum statistics

Threads
1,181,055
Messages
5,927,858
Members
436,573
Latest member
CMR237

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