Freezing Panes -- Basic Question

Tonto111

Board Regular
Joined
Jun 17, 2002
Messages
83
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!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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) :biggrin:

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

Tonto111

Board Regular
Joined
Jun 17, 2002
Messages
83
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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

Board Regular
Joined
Jun 17, 2002
Messages
83
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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
 

Forum statistics

Threads
1,144,050
Messages
5,722,231
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top