• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro code to have multiple freeze panes in one worksheet

Solocharles

New Member
Hi Guys, need your help.

I am looking to freeze panes in worksheet but I wanted to apply multiple freeze panes in single worksheet.

Attached the sample document to better understand my requirement.

In the form I wanted to freeze panes on cell C2 so that when I scroll down my heading to stay and when I scroll to right column A & B to stay.

The same I wanted to freeze panes on cell C11 so that when I scroll down my heading to stay and when I scroll to right column A & B to stay.

The same I wanted to freeze panes on cell C19 so that when I scroll down my heading to stay and when I scroll to right column A & B to stay.
 

Attachments

  • Sample.xlsx
    11.5 KB · Views: 5
Solocharles
Is there always fixed number of rows like in Your sample sheet?
How do You scroll down ... and up?
I would answer, that this would be possible - if Your sample file could be more realistic?
Now, is there any reason to that option?
... hmm? I found one sample based Your file.
 

Attachments

  • Sample (1).xlsb
    18.4 KB · Views: 7
Last edited:
Attached the file is more realistic sample file.

I wanted to apply freeze panes for every "month headings" like apply freeze on Q26 and Q49 so on till December month between every month there will be 20 rows.

I cannot add or remove any rows as it all fixed now. I need to just apply macro to freeze the panes for every month

In the form I wanted to freeze panes on cell Q36 so that when I scroll down my heading to stay and when I scroll to right column A to P to stay.
 

Attachments

  • Sample to Freeze.xlsx
    21.4 KB · Views: 4
Solocharles
Did You tested my sample?
Your the newest sample is ... a little different ... than Your original.
If all fixed now --- then how You could then add/remove rows?
If now freeze panes on cell Q36 ... about middle of January - February ... hmm?
Why freeze rows, if there are always those 20 rows visible?
My sample has rows limited too.

Question: Do Your freezing works as You want if You'll do next steps?
#1 Select Header Q ( that column will select )
#2 Select Window > Freeze Panes ( You can scroll columns as You want )

If You would like to have 'multi-scrollable-rows' too - then get an idea from my sample.
 
I have another macro which will hide and unhide the data grid based the month selection (Toggle Button). For example if user clicks on the "Jan" month and then the Jan month grid (heading + 20 rows) will show-up once the fill the grid with data then user can click on "Jan-Hide" to hide the Jan month grid. Like wise this is build for 12 months.


I saw your macro code on sample file, it almost works fine but there are additional rows added. I don't want to add or remove any rows or columns
 
Solocharles
It would be positive if You could answer to questions?
Test this sample.
Instructions:
#1 select Your Month-text
#2 repeat #1 as many times as need
 

Attachments

  • Sample to Freeze.xlsb
    22 KB · Views: 6
Solocharles
Many things could be possible,
but if You cannot answer questions and explain what would You really like to get
what should Your screen help You?
 
The macro code is in sample is not working on original file.

I just need macro code to apply freeze panes on the below mentioned cells. Please help me.

P22
Q26
Q49
Q72
Q95
Q118
Q141
Q164
Q187
Q210
Q233
Q256
Q279
 
Solocharles
Did You copy that code as it is in my sample file?
Do Your original file's sheet's layout is same as Your sample file's sheet's layout?
... seems not - based Your above given rows!
Same means ... same ... otherways, it's different.
Can You upload more realistic sample based Your original file?
... Your other threads sample file has protected sheet - if protected sheet then You should take care that protection itself allows some functionalties.
Here other sample with basic protections.
 

Attachments

  • Sample to Freeze copy.xlsb
    24 KB · Views: 8
Last edited:
Below is code is working but its freezing only the side ways(when I scroll to right its working fine) but scroll down it's not working heading is not staying

Same code can you modify to stay the heading as well. basically I need freeze panes to the below mentioned cells

P22
Q28
Q51
Q74
Q97
Q120
Q143
Q166
Q189
Q212
Q235
Q258
Q281
 
Solocharles
Seems You skipped my previous reply #10.
Please, reread that again and focus with sentences which ends with ?.
Those sentences are questions.
 
Did You copy that code as it is in my sample file?
Yes, I just copied the code to my original file

Do Your original file's sheet's layout is same as Your sample file's sheet's layout?
Yes, that's correct

Can You upload more realistic sample based Your original file?
This is more realistic sample file. The top blank area is sample file is filled with few formulas in my original file.
Jan and Feb are sample, like wise I have till Dec
 
Solocharles
I could not find Your This -file, I used Your other file.
Your P22 ... I skipped.
Take care.
 

Attachments

  • Testing2.xlsb
    59.2 KB · Views: 5
Last edited:
Back
Top