Find last row, select specific column in row

Question

I'm just starting to learn VBA and I've tried to find solutions here but to no avail. I'm seeking a VBA macro for this:

I have a sheet in my workbook called LOG that gets a timestamp in column A when I start to fill the row. Once I've completed a task I use =CONCATENATE in column I to summarize the rows A through H. Column I has the formula content filled down to row 300 or more. Column A is blank until I enter a time-stamp ( "ctrl + :" ).

What I am seeking to do is run a macro through a command button where it will find the last timestamped row in column A, and then select and copy contents (not the formula) of that row in column I to clipboard.

I've tried to modify so many different suggestions I've found in stackoverflow but with little success. I'm not sure really what I'm doing wrong and I've tried so many of them I don't know which I would share with you for an example. Any help would be very appreciated! Thanks again!

https://stackoverflow.com/questions/60565181/fill-specific-text-for-a-specific-column/60566813#60566813 Can you help me this link as well? — christian estrella, Mar 06 '20 at 15:19

score 0 · Answer 1 · answered May 10 '17 at 22:04

1) Define a function to copy some text to the clipboard:

Sub CopyText(Text As String)
    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014
    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing
End Sub

Then something like this:

Sub GetLastTimestampAndCopy()
    dim ws as worksheet
    dim strValue as string
    dim lngLastRow as long

    set ws = Activeworkbook.Worksheets("LOG")

    ' Get the last populated cell in the first column
    lngLastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Get corresponding value in the same row but in column I
    strValue = ws.Cells(lngLastRow, 9).Value

    CopyText strValue
End Sub

Execute the second SUB and you should have the value on your clipboard.

That worked perfectly! Wow, thanks so much! I REALLY appreciate the help! — Peter Alberto, May 29 '17 at 16:53
Happy to hear it :) - I had been looking for this for a long time myself, use it all the time. Ultimately found the basis for this on SO. If this solved your problem, could you accept the answer? — pwwolff, Jun 01 '17 at 07:31

Find last row, select specific column in row

1 Answers1