Return Last Cell Value in Column

  • Thanks Carim and KjBox,


    So, the code finally works without errors; however, the array does not seem to be populating as expected. Only one sheet has the last row of data copied.


    The good news is the Summary sheet correctly displays all the current worksheets in order.


    Here is the code:


    Here is the result:


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tDebug4.PNG Views:\t1 Size:\t40.3 KB ID:\t1217697","data-align":"none","data-attachmentid":"1217697","data-size":"full"}[/ATTACH]


    Any ideas on why the array is only populating for one worksheet?


    Not sure if this is related, but the array seems to enter data all the way down 7 rows further than the last sheet name (found by pressing Ctrl+End), but all 7 rows are empty.


    Thank you,

  • It will be because your "Road" sheets must have cells that are formatted below the last row that contains actual data, the .UsedRange will include those empty but formatted cells.


    You need to go to each "Road" sheet and clear all from all rows below the last row of data. Easiest way to do that is to select the row immediately below the last row of data, then press ctrl+Down Arrow, then right click within the highlighted area and select Delete from the menu.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Alternatively, you could try changing the code to

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Excellent, thank you so much, KjBox!


    Cells were formatted with WrapText set to True, which was the same as the source cells, so this made reading the results difficult. I added a line of code to fix this, and here is the final code with all the unnecessary stuff removed:



    Thank you! :congrats:

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!