+ Reply to Thread
Results 1 to 7 of 7

macro to generate a list of dates down a column

  1. #1
    Registered User
    Join Date
    01-19-2007
    Posts
    6

    macro to generate a list of dates down a column

    Hi all,

    Can anyone help me? I need a macro that can generate a list of month end dates (in date order) down a column. EG:

    Cell A1 = 31 Dec 95
    Cell A2 = 31 Jan 96
    Cell A3 = 29 Feb 96
    Cell A4 = 31 Mar 96
    etc
    etc

    The start date will always be 31 Dec 1995 while the end date needs to be dynamic and show the last month end, so it will need to grow by 1 row each month or grow by multiple rows if the macro has not been run for several months.

    Anyone got any ideas?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Aqua,

    Here is a macro that will fill in the end of month dates from the last date entered. You can choose where to start by changing the cell address in the macro (marked in blue). The macro assumes the worksheet is the active sheet. However, this can be easily changed.

    End Of Month Macro
    Please Login or Register  to view this content.
    Adding the Macro
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Press the keys ALT+F11 to open the Visual Basic Editor
    4. Press the keys ALT+I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL+V
    7. Make any custom changes to the macro if needed at this time
    8. Save the Macro by pressing the keys CTRL+S
    9. Press the keys ALT+Q to exit the Editor, and return to Excel

    To Run the Macro...
    To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Sincerely,
    Leith Ross
    Last edited by Leith Ross; 12-19-2007 at 06:09 PM.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    16,362
    try
    Please Login or Register  to view this content.
    Last edited by jindon; 12-19-2007 at 09:51 PM.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    NewDates

    There are probably better ways to do this, but this works
    NB:- See notes in code for Headers.
    Change column ref to suit your needs


    Please Login or Register  to view this content.
    Regards Mick G

  5. #5
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,651

    More dates

    Aqua , With respect to Leith Ross I have worked through his code and it is a lot better than mine,
    However if you change the following line of code:-
    Please Login or Register  to view this content.
    To
    Please Login or Register  to view this content.
    It will return the dates in the format requested on your original question.

    Apologies, Leith for messing with your code.
    Regards Mick

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello MickG,

    No need for an apology, it is a good addition to the code.

    Thanks,
    Leith Ross

  7. #7
    Registered User
    Join Date
    01-19-2007
    Posts
    6
    Hi all, I have been putting off finishing my spreadsheet. Many thanks MickG, Leith Ross and jindon for the help. I really like Leith's macro and i with a few tweaks I think I will end up using this one. :-D

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1