Forum Discussion

jsrmenke's avatar
jsrmenke
Copper Contributor
Sep 08, 2025

Excel help!

How would I capture the data from the last cell in a column when the column length is variable?
excel
Formulas and Functions
jsrmenke's avatar
jsrmenke
Copper Contributor
Sep 09, 2025

Thanks for all the suggestions!  Something I failed to tell is that when this mortgage table calculates the new value for number of payments the column will also shorten to this new value, and the remaining values will not be visible in the column.  I need it to find the last visible data or value in this column.

 

Thanks!

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 09, 2025

    Do you mean that some rows are hidden?

    Or do you use formatting to make some values invisible?

    • jsrmenke's avatar
      jsrmenke
      Copper Contributor
      Sep 09, 2025

      Yes, the rows that are no longer used because of a shorter payment schedule become invisible.  I want to locate that last visible cell.  

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 09, 2025

        I think you need VBA for that, so it will work only in the desktop version of Excel for Windows and Mac.

        Press Alt+F11 to activate the Visual Basic Editor.

        Select Insert > Module.

        Copy the following code into the new module:

        Function LastValue(r As Range) As Variant
    Dim w As Worksheet
    Set w = r.Parent
    LastValue = w.Cells(w.Rows.Count, r.Column).End(xlUp).Value
End Function

        Switch back to Excel.

        The last visible value in column K is returned by the formula

        =LastValue(K:K)

        Save the workbook as a macro-enabled workbook (*.xlsm), and make sure that you allow macros when you open it.

Resources