Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Show/Position A1 To Top left Of Screen

  1. #1
    Join Date
    23rd April 2008
    Location
    Manchester, UK
    Posts
    12

    Show/Position A1 To Top left Of Screen

    I have a very large workbook with multiple sheets and use macros to copy and paste all data as values, then delete some rows and columns depending on certain criteria. This starts at cell A1 and works right, then down.

    This is then saved as a copy and distributed to a wide audience.

    The problem is that when each page is opened up, the data shown is the bottom right of the whole sheet instead of the top left (Panes are frozen for row and column headers).

    I'm not sure I've explained properly, apologies if not. Please go easy on me this is my first post.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th July 2006
    Location
    San Diego
    Posts
    215

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Just end the macro with Range("B2").Select assuming the first column/row are freeze paned.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,869

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Depending on how your sheets are referenced in the macro, perhaps like this?
    VB:
    Application.Goto ActiveSheet.Range("A1") 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2008
    Location
    Manchester, UK
    Posts
    12

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Sorry, I realise I haven't explained properly. There are over 60 sheets and the panes are not frozen in the same place on each sheet. I tried Range("a1").Select at the end of the macro but the view is still not right, even though it does go to that cell.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,869

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Using either of our suggestions will take you to the upper left corner. Are you now saying that you want a particular range to be visible? Btw if the freeze panes don't follow any logical pattern I don't think you can write appropriate code.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2008
    Location
    Manchester, UK
    Posts
    12

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Yes I want the visible range to be A1 to whatever fits in the window without hiding anything behind the panes. The panes are frozen to enable the end users to scroll right and down without losing the row/column headers.

    Does that make sense?

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,869

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Sorry but no! What exactly did my code not do that you want doing because to my eyes it fits the bill?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    20th February 2006
    Location
    London, UK
    Posts
    2,869

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Site seems to be on a go-slow...

    I do understand what you mean now. The only way I can think of off the top would be store the cell at which you freeze panes and then reference that cell in the Goto statement.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    23rd April 2008
    Location
    Manchester, UK
    Posts
    12

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    This code doesn't do what I want because columns G:AJ are hidden and rows 5:120 are hidden where panes are frozen at G5 and last cell containing data is AT155.

    Essentially I need the code equivalent of Ctrl Home, if you see what I mean.
    Auto Merged Post Until 24 Hrs Passes;

    Quote Originally Posted by StephenR
    Site seems to be on a go-slow...

    I do understand what you mean now. The only way I can think of off the top would be store the cell at which you freeze panes and then reference that cell in the Goto statement.
    I think I know what you mean however the panes are frozen in a different place on the worksheets (already frozen - not part of macro).

    If there is no work-around I will just have to carry on manually doing Ctrl Home on each page before I send it out, a bit of a pain as it's a daily report. Never mind, nothing ventured nothing gained.

    Many thanks for your suggestions.

    Paula
    Last edited by peanut1999; April 23rd, 2008 at 06:43. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    16th April 2008
    Location
    Seattle, WA
    Posts
    88

    Re: Show Leftmost And Uppermost Cells With Freeze Panes After Running Macro

    Not sure if this helps. Try the ScrollColumn, ScrollRow command to get the uppermost A1 cell to always display. It may not be displaying for you using the Select command because you have ScreenUpdating = False.
    VB:
    ActiveWindow.ScrollColumn = 1 
    ActiveWindow.ScrollRow = 1 
    Range("B4").Select 
    ActiveWindow.FreezePanes = True 
    
    
    Bert
    The one with the fewest lines of code wins

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Blank Or Grey Area In The Left Of The Screen
    By andreco in forum EXCEL HELP
    Replies: 4
    Last Post: May 1st, 2008, 00:44
  2. Position Message Box On Screen
    By April82 in forum EXCEL HELP
    Replies: 4
    Last Post: March 3rd, 2008, 21:35
  3. Lock Shape Position On Screen?
    By darthrader in forum EXCEL HELP
    Replies: 4
    Last Post: February 17th, 2007, 02:58
  4. Replies: 4
    Last Post: January 14th, 2006, 17:45
  5. Position an object in center of screen
    By Ktrasler in forum EXCEL HELP
    Replies: 2
    Last Post: October 29th, 2005, 22:03

Bookmarks

Posting Permissions

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