Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Freeze Panes help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Freeze Panes help

    Good day. I am running Excel 2013 on Windows 10 Home. I have a workbook with 7 worksheets with several Macros. There is also a Macro in the "This Workbook" that runs every time the workbook is opened to set all the workbooks to a generic condition. Recently I discovered the need to use the Freeze Panes function to freeze Column 1 on 2 of the worksheets. All of the existing Macros use the same general formatting including freezing Row 1. How do I change the Macro in the 2 worksheets that require freezing Column 1 instead of Row 1? Any help is appreciated.
    Thank you,
    Dan Wilson...
    Have an Ice Cream Day!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Freeze Panes help

    Can you post the code that you are currently using & what are the names of the two worksheets?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Freeze Panes help

    Good day again Fluff. We have to stop meeting like this, people will talk. Here is one of the macros that needs to be modified. One is named RC and the other is named Riders. Except for the actual sort coordinates and the print coordinates, the macros are the same. Is it as simple as changing the ActiveWindow.FreezePanes to ActiveColumn.FreezePanes? Also, let me know if there is anything in the macro that does not need to be there.
    Thank you, Dan Wilson...

    Code:
    Sheets("RC").Select
        ActiveWindow.FreezePanes = False
        ActiveWindow.View = xlPageLayoutView
        Application.PrintCommunication = False
        With ActiveSheet.PageSetup
            .LeftHeader = "Printed on &D"
            .CenterHeader = "Speedway Chapter 3399" & Chr(10) & "Road Captains 2019"
            .RightHeader = "Page &P of &N" & Chr(10) & "RC by Lead"
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.7)
            .RightMargin = Application.InchesToPoints(0.7)
            .TopMargin = Application.InchesToPoints(0.75)
            .BottomMargin = Application.InchesToPoints(0.75)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .Zoom = 100
            .PrintErrors = xlPrintErrorsDisplayed
            .OddAndEvenPagesHeaderFooter = False
            .DifferentFirstPageHeaderFooter = False
            .ScaleWithDocHeaderFooter = True
            .AlignMarginsHeaderFooter = True
            .EvenPage.LeftHeader.Text = ""
            .EvenPage.CenterHeader.Text = ""
            .EvenPage.RightHeader.Text = ""
            .EvenPage.LeftFooter.Text = ""
            .EvenPage.CenterFooter.Text = ""
            .EvenPage.RightFooter.Text = ""
            .FirstPage.LeftHeader.Text = ""
            .FirstPage.CenterHeader.Text = ""
            .FirstPage.RightHeader.Text = ""
            .FirstPage.LeftFooter.Text = ""
            .FirstPage.CenterFooter.Text = ""
            .FirstPage.RightFooter.Text = ""
        End With
        Application.PrintCommunication = True
        ActiveWindow.View = xlNormalView
        Cells.Select
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        Range("A2:EJ36").Select
        ActiveWorkbook.Worksheets("RC").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("RC").Sort.SortFields.Add2 Key:=Range("D2:D36"), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("RC").Sort
            .SetRange Range("A2:EJ36")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Sheets("RC").Select
        ActiveSheet.PageSetup.PrintArea = ""
        Range("A1:K72").Select
        ActiveSheet.PageSetup.PrintArea = "$A$1:$K$72"
        Range("A1").Select
        With Selection.Font
            .Color = -16776961
            .TintAndShade = 0
        End With
        ActiveCell.FormulaR1C1 = "RC sorted by Lead"
        Range("A1").Select
    End Sub
    Last edited by Fluff; Sep 15th, 2019 at 11:28 AM. Reason: Added code tags
    Have an Ice Cream Day!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Freeze Panes help

    Add this line as shown
    Code:
     End With
     Range("B2").Select
     ActiveWindow.FreezePanes = True
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Freeze Panes help

    OK, I tried what you suggested. That freezes the top 3 rows. What I want is to freeze Column A so that when I scroll to the right beyond Column BG, I will still see Column A on the left. Sorry if I didn't explain it correctly. Too bad there isn't a function "ActiveColumn.FreezePanes".
    thank you, Dan Wilson...
    Have an Ice Cream Day!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Freeze Panes help

    Do you have any merged cells?
    Because if you select B2 & freeze panes then row1 & col A should be frozen.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Freeze Panes help

    OK. There are no merged cells in the Riders worksheet. Row A has a height of 39 and Cell A1 is formatted as Wrap Text. It is used to advise the user of the sort method that was last chosen for the worksheet. Cells B1 thru F1 are blank. The ride numbers start at G1 through EA1 and have text aligned straight up and down so that all the ride numbers (19-xxx) fit in to the cell width of 2. Column A contains the rider names. Column B contains riders nicknames. Column C contains the rider membership status. Columns D and E contain formulas for total rider activity. Rows 2 and 3 contain totals of rider activity. Row 4 is totally blank for separation. Cells G6 thru EA205 contain rider activity (1 indicates attendance).

    I removed the Wrap Text from Cell A1 and ran the macro with your suggestion. It eliminated Row 1 and froze Rows 2 and 3.
    Thank you,
    Dan Wilson...
    Have an Ice Cream Day!

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Freeze Panes help

    Hadn't notice that you were using split screen, so why not just
    Code:
        With ActiveWindow
            .SplitColumn = 1
            .SplitRow = 1
        End With
    And get rid of the freezepanes.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular Dan Wilson's Avatar
    Join Date
    Feb 2006
    Location
    North Carolina
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Freeze Panes help

    Hi Fluff. I don't know what Split Screen is. Where did you find that?
    Thanks, Dan...
    Have an Ice Cream Day!

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Freeze Panes help

    It's in your code, I just suggested changing the red 0 to a 1
    Code:
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •