Question
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Question

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Can a hidden formula effect the sorting process? I have a worksheet that I have written code to sort at a certain time. On the one page the sort works fine, but on another page that has several cells with hidden formulas the sort works differently. What I mean is, I sort from A6:C10 and A12:c40, on each page there are 6 cells at the bottom of my list that are blank, this is fine for the first page the sort sorts from cell A12 down leaving the blank ones at the bottom, but on the other page the 6 empty cells contain a hidden formula and when sorted the empty cells are moved to the top. Can I change this code to ignore the hidden formulas so that the empty cells are at the bottom on both pages?

    Code:
    Application.ScreenUpdating = False
    Range("A6:C10").Select
    Selection.Sort Key1:=Range("A6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A13:C40").Select
    Selection.Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I may be missing the point but why not change C40 to C34? Why include the last 6 rows in the sort if you don't want them sorted?
    regards
    Derek

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I do need them to remain at the bottom of the list. I have them there because on the page the sorting works correctly is where data is stored and I need to allow for new entries into those cells. The hidden formula is just an ='sheet 3'!A35 formula so when an entry is made on Sheet 3 A35 it is automattically transferred to the other sheets. It is not really a problem because the sorting still retained all the formulas and everything still does what it should, but it is driving me crazy trying to figure out why on one page I have my empty cells at the bottom of my list and on the other pages the empty cells are at the top of my list and I basically use the same sort cord for all sheets.
    I appreciate the help from everyone at Mr. Excel.

    viper

User Tag List

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
  •  

 

 
DMCA.com