Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates Flipping (Excel2003)

    I have dates in a first raw as
    12/8/2004 12/13/2004 12/14/2004 12/15/2004 12/16/2004 12/17/2004 12/18/2004 12/19/2004 12/20/2004 12/21/2004 12/22/2004 12/23/2004
    How can I flip those too? So it is desc?
    Thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    1- Select the cells containing those dates.
    2- Select Sort from the Data menu.
    3- In the dialog box, click on the Options button.
    4- In the Orientation of the Options dialog box, click on "Sort left to right."
    5- Click Ok in the Options dialog box.
    6- In the Sort By drop down list select Row 1 (or whatever row contains the dates).
    7- Click on Decending.
    8- Click on OK.

    The dates should now be in decending order.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    It may work as soon as I'll figure out how to select B1 to the last column which can be whatever columnX.
    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dates Flipping (Excel2003)

    Select B1, while holding <shift> press <end><right arrow>

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    Thanks but I am sort of writing code to do this automatically.

    So I have

    lRowCount = Sheets("Sheet1").[B1].CurrentRegion.Rows.Count
    lColumnCount = Sheets("Sheet1").[B1].CurrentRegion.Columns.Count

    but I need Nmae of the column as my first always will be B column and my last will be whatever column. How do I get a range from B to lColumnCount if lColumnCount value is 15 (so 15 = 'O')

    Thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dates Flipping (Excel2003)

    <P ID="edit" class=small>(Edited by sdckapr on 24-Mar-05 16:27. Added another with xltoleft)</P>Can't you just sort on the "current region"?

    Or use something like:
    iColumn = Sheets("Sheet1").range("b1").end(xltoright).column
    to get the integer value of the column

    To sort the region:
    with Sheets("Sheet1")
    .range(.range("b1"), .range("b1").end(xltoright)).sort 'add parameters
    end with

    Note: this does essentially what the manual method does...

    Or if the region has blanks within row 1:
    with Sheets("Sheet1")
    .range(.range("b1"), .range("IV1").end(xltoleft)).sort 'add parameters
    end with


    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    Copyrighted code replaced with link by HansV

    Thanks, I got it and I have to share this with you (it works a treat!) : the FlipColumns function from Allen Wyat's Excel tips.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    Edited by Legare Coleman. The comments below are about the code originally posted in the message I replied to, not the code on the page that is now linked to. The comments below may not make sense without seeing the code that was originally posted.

    I would like to make a few comments about your code to help you develop better code in the future:

    1- You use this statement to get the count of the number of columns:

    <pre>ColumnCount = Sheets("Sheet1").[b].CurrentRegion.Columns.Count
    </pre>


    That statement gets the count of the number of columns in the current region around column B. This could be a problem for a number of reasons. First, If you delete columns, the currentregion property is not updated to reflect the deletion until the workbook is saved, closed, and reopened. This could cause you to blank cells in your swap. Second, you are using the currentregion of the entire column B. If there are rows below the one you are swapping that contain more data than the swap row, you will again include blank columns in the swap. Third. currentregion stops at the first blank column. This could cause you to miss some columns that you want included if there are any empty cells in the row.

    2- You use this statement to check to see if there is any data:

    <pre>If lColumnCount = "" Then
    </pre>


    In the previous line you assigned a numeric value to lColumnCount and in this statement you are comparing it to a string. You will never get an equal here. This is a perfect example of why all variables should be declared with a DIM statement. Since you did not DIM lColumnCount, VBA allowed the comparison but it is not going to do what you want. If you had DIMed lColumnCount as a Long or an Integer, you would have gotten a Type Mismatch error on that statement and you would have know there was a problem that needed to be fixed. I recommend that you select Options from the Tools menu in the VBA editor and on the Editor tab of the Options dialog box, click on "Require variable declaration." This will include an "Option Explicit" statement in every VBA module which will require you to DIM all variables. You should also only declare variables as Variants when absolutely necessary. This will make your code run faster and help find error like the one above.

    3- Your first message indicated that you wanted the dates in decending order. Your code just reverses the order. If the dates are not in ascending order, then your code will not put them into descending order.

    4- Your code does not include the first cell in the selection in the swap. That is fine if it is what you want, you just did not mention that in your first message. However, if the first cell in the selection is in a completely empty column, then you code does not include the last cell in the swap (the first column in the selection is not included in the CurrentRegion since it is empty, back to point number 1).

    5- If you can use the .Sort method, it will be faster since it is written in compiled code, and you loop is interpreted VBA code. The internal code is almost always faster. However, .Sort will put the cells in descending order, not just reversed.
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    In this wonderful code I need to change one thing I don't see how.
    When columns are starting at A1 - all works fine,
    but I need to start at A5. How do I alter it to start at A5?

    Thanks

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    I don't understand. The code you previously posted started one column to the right of whatever is selected, not a specific column like A1 or A5.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates Flipping (Excel2003)

    Are you talking about Allen Wyatt's code you posted earlier? In that case, it looks from his instructions that you have to simply select it...
    In his own words...
    "In order to use this macro, all you need to do is select the rows you want flipped and run it."

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Dates Flipping (Excel2003)

    Since the code works on the selection, you can add code to modify the selection:

    range(range("A5"), range("A5").end(xltoright)).select

    Before the main code runs...

    Steve

Posting Permissions

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