Problems specifying where "freezepanes" occurs using VBA

[broro183]

Hi all,

I'm having problems specifying where "freezepanes" occurs using VBA.
It doesn't take much to press [ctrl + home], [alt + w +f] (my work
around) before running a formatting macro. However, I'm curious, is
there anything in Excel's (XP, Excel 2002/2003) set up which may
prevent either of the below from working?

Range("A2").Select
ActiveWindow.FreezePanes = True

or:
Application.Goto reference:=Range("a2"), scroll:=True
ActiveWindow.FreezePanes = True

This was recorded using the macro recorder but when I run the code the
second line has sometimes worked & sometimes resulted in the freeze
panes being applied in, for example, cell "D17" approximately the
centre of the centre of my screen (not previously selected). This has
puzzled me to the extent that I have commented out the code, & now
select cell A2 and manually freeze the pane.

This is part of the formatting of a single sheet .csv file which is
received on a daily basis & saved as an xls file. Could it be possible
that turning off the screen updating before running this code is
causing the problem?

thanks in advance
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Scott Hannon]

Try using

ActiveWindow.ScrollRow = 1

before your existing code. It's the only way I've gotten it to work
correctly.

Scott Hannon
AL, USA

 

[broro183]

Hi Scott,

Thanks for the feedback - I tried it at work today & it works
wonderfully
It's great to come back from holiday & have a solution waiting for me.

thanks,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...