locked
VBA func to return last value from column RRS feed

  • Question

  • Hi, I need to have a VBA function available everywhere within a workbook, that would accept as the only argument column name as string or column as range (C:C), and would return last filled value as variant.

    Can anybody give me suggestion how this function should look? THX

    Wednesday, March 14, 2018 11:16 AM

All replies

  • Please provide your code.... Usually I write:  Range("C:C") ...with quote. 

    Ashidacchi >> http://hokusosha.com/

    Wednesday, March 14, 2018 11:50 AM
  • Hello Anakunda V2.0,

    Please check if below code could work for you.

    Sub Test()
    Debug.Print LastValue("CC")
    End Sub
    
    Function LastValue(columnName As String)
    Dim colRng As Range
    On Error Resume Next
    Set colRng = ActiveWorkbook.ActiveSheet.Columns(columnName)
    If colRng Is Nothing Then
    LastValue = "No exist column, please check your column name"
    Else
    LastValue = colRng.Cells(colRng.Rows.Count, 1).End(xlUp).Value
    If LastValue = "" Then LastValue = "Empty Column"
    End If
    End Function
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 15, 2018 6:50 AM
  • Thanks very much Terry Xu. Just probed and everything works as I need.
    Tuesday, March 20, 2018 9:29 AM
  • Hello Anakunda v2.0,

    I'm glad to hear that you have solved the issue. I would suggest you mark any helpful reply or your solution as answer to close this thread. If you have any other issue, please feel free to post threads to let us know.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 20, 2018 9:55 AM