Freezing Panes -- Basic Question

[Tonto111]

I want to keep the top two rows of my spreadsheet from disappearing, so I tried to freeze the pane.
I go to the next cell down (in this case the third row) and click freeze panes, correct?

However, if I run a macro that copies data from one sheet and pastes into my frozen sheet and sorts it, the frozen cells sometimes get screwed up. The sheet gets frozen so that you can't scroll down at all, so it looks like you're seeing the whole sheet when there's data at the bottom you can't get to.

What do I do? Do I have to unfreeze, then refreeze panes as part of my macro? Any suggestions? THANKS!!

 

[Colo]

I go to the next cell down (in this case the third row) and click freeze panes, correct?

I think it's correct.

However, if I run a macro that copies data from one sheet and pastes into my frozen sheet and sorts it, the frozen cells sometimes get screwed up.

I can say nothing without seeing your macro, but I guess your macro was doing *Select* or *Activate* things...
So, before run your macro unfreeze the pane and after run it, set freeze the pane agein.(via macro)

Or...please try to add this code line to the end of your macro.(I'm not sure)

<pre>
Application.Goto [A3]
</pre>

 

[Tonto111]

Here's the macro....Sub constructlog()

Application.ScreenUpdating = False
Range("L3:M650").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="Const"
Selection.AutoFilter Field:=2, Criteria1:="<>*No*", Operator:=xlAnd
Range("F2").Select
ActiveCell.FormulaR1C1 = "Construction Log"
Range("G3").Select
ActiveCell.FormulaR1C1 = "Project Title"
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("M:M").ColumnWidth = 0#
Columns("K:K").ColumnWidth = 0#
Columns("O:O").ColumnWidth = 0#
Rows("4:652").Select
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A4").Select
Application.ScreenUpdating = True
End Sub

 

[Colo]

Hi Tonto, I've modified a little, so pls try this one. But I'm not sure the layout of your worksheet(Especially column L,M and A), So, after try this code, please let me have your comment.

<PRE>
<FONT color=red>Sub </FONT>constructlog_modified()

Application.ScreenUpdating =<FONT color=red> False</FONT>

<FONT color=red>With </FONT>Range("L3:M650")

<FONT color=red>If </FONT>Not .Parent.FilterMode<FONT color=red> Then </FONT>.AutoFilter

.AutoFilter Field:=1, Criteria1:="Const"

.AutoFilter Field:=2, Criteria1:="<>*No*", Operator:=xlAnd

<FONT color=red>End With</FONT>

Range("F2").Value = "Construction Log"

Range("G3").Value = "Project Title"

Columns("E:F").EntireColumn.AutoFit

Columns("K:K").Hidden =<FONT color=red> True</FONT>

Columns("M:M").Hidden =<FONT color=red> True</FONT>

Columns("O:O").Hidden =<FONT color=red> True</FONT>

Rows("4:652").Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlGuess, _

MatchCase:=False, Orientation:=xlTopToBottom

Application.Goto Range("A3"),<FONT color=red> True</FONT>

Application.ScreenUpdating =<FONT color=red> True</FONT>

<FONT color=red>End Sub</FONT>


</PRE>

 

[Tonto111]

That worked great....what is the difference between the two that will keep it from freezing too much and not letting me scroll down?

 

[Colo]

Hi Tonto,
Most of code lines I wrote doesn't matter. I just removed *select* method.
But please note as following code line.<pre>
Application.Goto Range("A3"), True</pre>

And here is a copy of the help


'-----------------------------------------------------------------------
Goto Method

Selects any range or Visual Basic procedure in any workbook, and activates that workbook if it�fs not already active.

Syntax

expression.Goto(Reference, Scroll)

expression Required. An expression that returns an Application object.

Reference Optional Variant. The destination. Can be a Range object, a string that contains a cell reference in R1C1-style notation, or a string that contains a Visual Basic procedure name. If this argument is omitted, the destination is the last range you used the Goto method to select.

Scroll Optional Variant. True to scroll through the window so that the upper-left corner of the range appears in the upper-left corner of the window. False to not scroll through the window. The default is False.
'-----------------------------------------------------------------------
This message was edited by Colo on 2002-10-17 20:02