Avatar of -Polak
-Polak
Flag for United States of America asked on

Can I use VBA to freeze detached horizontal and vertical panes?

For example, is it possible to force Excel to freeze-pane Columns A:B, Columns Z:Y: and Rows 1:2 ?
Microsoft ExcelVB ScriptVisual Basic ClassicVBA

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Saqib Husain

Cells(2,3).select
    ActiveWindow.FreezePanes = True
-Polak

ASKER
That will freeze Columns A:B and Row 1, but does nothing for Columns Z:Y....
Saqib Husain

You would have to select the appropriate cell with

Cells(2,3).select

and then do the freeze.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
-Polak

ASKER
If I try and put additional arguments into the cell select to freeze A:B & Y:Z and rows 1:2 I get a too many arguments error.

Again, I'm trying to freeze A:B, Y:Z, AND rows 1:2 simultaneously.....

It would be like Selecting cell C3 and AA3 and then freezing panes. Basically if a user were to scroll to the right past Y:Z both A:B and Y:Z would appear on the left side of the screen.
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
-Polak

ASKER
Thank you, was hoping VBA could force creating a "window"-layer on top of the spreadsheet.
Rob Henson

You could get round it with a manual tweak.

In the View tab select new Window and then Arrange All and choose Vertical and tick box for Windows of Active Workbook.

This will show the same sheet twice. Resize the left hand Window so that you can see only columns A & B and stretch the right hand window to fill the rest of the screen. Scroll the right hand window so that column Y is first column on sheet and then select AA3 and apply Freeze Panes.

In the View tab now select "View Side by Side" and "Synchronous Scrolling". The two windows will now scroll vertically in sync and to an extent horizontally; scrolling right on the Right hand pane won't scroll the left hand pane until after several scrolls.

Thanks
Rob H
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.