CODEWITHSUNDEEP
excelvba
user206168
user206168

Reputation: 1025

How to freeze multiple rows and columns in excel?

I want to freeze the range S1:Y17, hide the columns A:R, and from column Z onward I only want to freeze the top 2 rows.

Is that possible?

Upvotes: 3

Views: 30984

Answers (5)

Pat Cruz
Pat Cruz

Reputation: 66

Range("A1").Select

With ActiveWindow
    .SplitColumn = 1
    .SplitRow = 1
    .FreezePanes = True
End With

You can play with split column and rows.

Upvotes: 5

Marcucciboy2
Marcucciboy2

Reputation: 3259

I know this question is old but I visit it often enough that I thought I would add a VBA version of @daniellopez46's answer. This code will:

  1. Create a second window of your spreadsheet
  2. Tile the windows vertically (side by side)
  3. Show a range starting at column S on one window
  4. Scroll to column Z onward on the second window
  5. Freeze the top 2 rows of the second window

Once you are finished working on the spreadsheet and close one of the windows you may not want to keep the formatting that was done, so I included a ResetWindow macro.

Sub MacroA()

    Dim window1 As Window
    Set window1 = ActiveWindow

    ResetWindowA

    Dim window2 As Window
    Set window2 = window1.NewWindow

    Windows.Arrange xlArrangeStyleVertical

    With window2
        'jumps to column S
        .ScrollRow = 1
        .ScrollColumn = 19
    End With

    With window1
        'jumps to column Z
        .ScrollRow = 1
        .ScrollColumn = 26

        'freezes the first two rows
        .SplitRow = 2
        .SplitColumn = 0
        .FreezePanes = True
    End With

End Sub

Sub ResetWindowA()

    With ActiveWindow
        'reset previous freeze, if any
        .FreezePanes = False
        .SplitRow = 0
        .SplitColumn = 0
    End With

End Sub

If you would like code that hides the ranges you're not using instead of simply scrolling over to where you want to work, I made the next snippet as well to hide all but the ranges you're working with.

It also has its own ResetWindow for when you're done working with both windows and want to close and save the document.

Sub MacroB()

    Dim window1 As Window
    Set window1 = ActiveWindow

    ResetWindowB

    Dim window2 As Window
    Set window2 = window1.NewWindow

    Windows.Arrange xlArrangeStyleVertical

    With window2
        .ScrollRow = 1
        .ScrollColumn = 1

        'Hide all but S1:Y17
        Columns("A:R").EntireColumn.Hidden = True
        Columns("Z:XFD").EntireColumn.Hidden = True
        Rows(18 & ":" & Rows.Count).EntireRow.Hidden = True
    End With

    With window1
        .ScrollRow = 1
        .ScrollColumn = 1

        'Hide all columns before Z
        Columns("A:Y").EntireColumn.Hidden = True

        'freezes the first two rows
        .SplitRow = 2
        .SplitColumn = 0
        .FreezePanes = True
    End With

End Sub

Sub ResetWindowB()

    'unhide rows
    If Columns("XFD").EntireColumn.Hidden = True Then
        Columns("A:R").EntireColumn.Hidden = False
        Columns("Z:XFD").EntireColumn.Hidden = False
        Rows(18 & ":" & Rows.Count).EntireRow.Hidden = False
    Else
        Columns("A:Y").EntireColumn.Hidden = False
    End If

    With ActiveWindow
        'reset previous freeze, if any
        .FreezePanes = False
        .SplitRow = 0
        .SplitColumn = 0

        .ScrollRow = 1
        .ScrollColumn = 1
    End With

End Sub

Upvotes: 0

Paul Cook
Paul Cook

Reputation: 21

This was possible in older versions of excel. You could select any cell, go to the windows tab and the Freeze Panes. Everything to the left and above that cell was frozen. But Microsoft seems determined to remove more functionality with each new version of Office. Each has fewer of the old functions we knew and loved. Soon, you might as well use Works, or Open Office. I wish I could switch to Word Perfect, but too many companies are using MS Office.

Upvotes: 2

daniellopez46
daniellopez46

Reputation: 604

There is no way to accomplish this using any of the options under any of the ribbons.

Alternatively you can set your freeze point at Z18, especially since columns A:R are hidden or use View>New Window and then Arrange All.

Upvotes: 4

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

sure just select a cell Z3, and on the Window menu click Freeze Panes

and in VBA, try this:

Range("Z3").select
ActiveWindow.FreezePanes = True

Upvotes: 2

Related Questions