Forum Discussion
Excel help!
How would I capture the data from the last cell in a column when the column length is variable?
Do you mean that some rows are hidden?
Or do you use formatting to make some values invisible?
Yes, the rows that are no longer used because of a shorter payment schedule become invisible. I want to locate that last visible cell.
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 FunctionSwitch 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.