Return Last Cell Value in Column

  • Hi, I'll try to set the scene.


    OS: Windows 7 Pro
    Application: Excel 2013


    The Workbook:

    • This workbook contains nearly 100 worksheets, each with data relating to a road network
    • Except for the Summary Sheet, each worksheet has a unique name which is the Road Number
    • Each worksheet for each Road Number has the exact same structure as one template was used to construct each one
    • The used colums are A thru V (22)
    • The last value in the Chainage column will also equal the road length

    The Summary Sheet

    • This Sheet is also formatted into the same columns as each of the other sheets, except that Column A is the Road Number (so there are 23 columns)
    • In the Road Number worksheets, the first Column contains Chainage data (eg. road segment length, where the last segment in this column equals the length of the road)

    The Code

    • For this code, I'm only concerned with the first column as I can adapt the code across the various fields
    • The first worksheet is a Summary sheet which at present contains the Road Numbers down Column A
    • For each Road Number, the code needs to:

      • Read the Worksheet Name from Column A (Road Number)
      • Read the Column Heading (eg Chainage)
      • Return the last value in the Chainage column and paste the value into the Chainage Column on the Summary Sheet

        • In this case, Cell A2 = Road 1
        • Cell B2 = Chainage for Road 1


      • Continue on until the last Road Name has the Chainage recorded in Column B


    I'm familiar to coding, but cannot put this all together, particularly using cell.value to extract the Worksheet name.


    Here is code I have found to find the last used cell in a column, and I expect the cells() to be preceded by worksheets(""). or something in a For Each ws, Next loop:


    [VBA]
    Dim lrow As Long
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    [/VBA]


    Code to insert the relevant chainage will use the cell.value = [last chainage]


    If anyone could point me to some code to use, I think I can extract and manipulate what I need.


    Thank you,


    Neil

  • Hello,


    Your explanations are a little bit confusing :


    1. Do you need the Last Value in Column ... Or ... the Last Row Number in Column ...?


    2. Do you need a simple Formula ... Or do you need to create a Macro ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks, Carim,


    The Summary Sheet contains all the Road Numbers down Column A.


    Column B needs to return the last Chainage for each Road Number.


    Let's say A2 = Road 1


    Worksheet Road 1 has the Chainage in Column A.


    The last value in Column A will be the value I want to extact and copy to cell B2 in the Summary Sheet.


    I have attached a very simple idea of the worksheets, hoping this helps.


    Our work day is over here in eastern Australia, and I'll be back in the office in about 14 hours.


    Thank you,

  • Hello,


    As an initial test ... =INDIRECT("'"&A2&"'!"&"A25")


    and improved formula in cell B2 :


    =LOOKUP(2,1/(INDIRECT("'"&A2&"'!"&"A1"):INDIRECT("'"&A2&"'!"&"A100")<>0),INDIRECT("'"&A2&"'!"&"A1"):INDIRECT("'"&A2&"'!"&"A100"))


    HTH

  • Hello again,


    As far as testing a macro ...


    Code
    Sub ExtractLastValues()
    Dim c As Range
    Dim rng As Range
    Set rng = Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
        For Each c In rng
            c.Offset(0, 1) = Sheets(c.Value).Cells(Rows.Count, "A").End(xlUp).Value
        Next c
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • To update the Summary sheet with the last row of data from all "Road" sheets try this

  • Thanks, HTH, I really appreciate your help.


    These ideas, unfortunately, don't seem to be working for me.


    So, your first formula was just a test?
    =INDIRECT("'"&A2&"'!"&"A25")


    Your next formula worked, but it was only good for 100 rows, but I can see this is hard-coded
    =LOOKUP(2,1/(INDIRECT("'"&A3&"'!"&"A1"):INDIRECT("'"&A3&"'!"&"A100")<>0),INDIRECT("'"&A3&"'!"&"A1"):INDIRECT("'"&A3&"'!"&"A100"))


    This could work, though, if I had a cell somewhere on the Summary Sheet which recorded the last used row number (populated by a macro), then reference this cell instead of "A100".


    I should be able to find this code OK.


    Now to test your code.

  • Hi KjBox, and thank you for your reply.


    While your code worked well in your attached workbook, it failed in mine on the following line:


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tDebug1.PNG Views:\t1 Size:\t74.6 KB ID:\t1217633","data-align":"none","data-attachmentid":"1217633","data-size":"full"}[/ATTACH]
    I tried to understand your code before replying, but arrays are not something I fully understand. Am I right in assuming you are passing the results into an array, then copying these values from the array to populate the cells?


    Would your code fall down if the Summary sheet does not have all the column names? Putting column names into the Summary sheet didn't seem to help.


    I'm also confused with x="Road No." value? If x is to be the current Worksheet, there is no worksheet called "Road No."


    Thank you,


    Neil

  • Can you attach your workbook?

    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.

  • I suspect that your actual workbook does not have a sheet named "Summary".

    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.

  • You do not need to include all 100 "Road" sheets in the workbook you attach, just 3 will be enough.

    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.

  • Hi KjBox, the following could be the problem.


    While the workbook uploaded indicated each sheet had the column headings begin on row 1, they actually begin on rows 6-7, as in the picture below - and every sheet has the exact same format.


    [ATTACH=JSON]{"data-align":"none","data-size":"full","data-attachmentid":1217641}[/ATTACH]


    No cells are merged, but there could be data all the way to column V, and will probably not be on the same row as the last used row in Column A.


    So, I'm sorry for misleading you, as I can probably understand if your code assumes there are column headings across the first row of data.


    All I'm wanting at the moment is for code to return the final chainage number in Column A for each sheet, and I think I can manipulate the code to deal with things like Road Width, etc.


    Thank you,


    Neil

  • I just read your recent replies, KjBox, so I'll work on that now, but it will be a cut-down version so as not to include our sensitive information. Yes, there a worksheet called Summary, as you will see.

  • For that structure of the "Road" sheets change my code to


    By using .UsedRange the code will always pick up the last row of data no matter what is above that last row.

    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.

  • Hi KjBox,


    Attached is a cut-down version of our road network. Our local government area (eg. similiar in understanding to the US County area) is over 47,000km2, so our road network is very extensive. We are only a very, very small part of Australia, very far from capital cities. The area of roads we look after is equal to about half the size of the US state of Michigan, or half the size of the country Hungary - but our population is only around 5,000.


    Thank you,

  • I have copied my test data to your new Sample Workbook, and added a button to the Summary sheet. The code is assigned to that button, click it to update the summary sheet.


    I think VBA would be better for you than a formula version. If you have 100 "Road" sheets with 22 columns and say an average of just 500 rows of data that would result in 110,000 INDIRECT formulas in your Summary sheet. That could be slow to calculate and could bog down Excel.


    You can add headers as required to Row 2 of the Summary sheet.


    If you look at the Road 3 sheet you will see that even with an empty row within the data, the last row of data is still picked up.

  • Thank you so much, KjBox,


    This code was still failing, so I thought it must be something to do with my sheets. After inserting error handling, which trapped the name of the worksheet at the time of the failure and displayed it in a message box, I found the offending road sheet; however, while there were some issues (such as the template structure was missing, so I fixed that), it is still failing on this one sheet.


    I'll keep checking to see if I can find it, but interruptions are impossible at the moment.


    Thank you again,


    Neil

  • If you get time, copy that sheet to a new workbook, alter the data on that sheet so it is not so sensitive and attach it here, I will have a look at it and see if I can figure what is causing the issue.

    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.

  • If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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