identifying last value in a row.

This function will get the column letter for the last cell in the row you pass to it:

Public Function GetLastColLetter(ByRef rngRow As Excel.Range) As String

    Dim c As Excel.Range
    Dim strRange As String
    
    Set c = rngRow.EntireRow.Cells(1, rngRow.Worksheet.Columns.Count)
    Set c = c.End(xlToLeft)
    
    strRange = c.Address(False, False)
    GetLastColLetter = Replace(strRange, CStr(c.Row), "")
    
End Function

Select allOpen in new window

Is the range operator in your location '.'? - if not you may need to change it to a ':' in the example copy you give.

@andrewssd3 - is there a partcular reason you declare as Excel.Range as opposed to just Range?

Yes - it's really just a habit, but I have done a lot of developing of cross-product applications, where I had references say to Word and Excel in the same project.  Then it's sometimes essential to differentiate - Range would be one of those, as both Word and Excel have Range objects.  I just tend to do it all the time now, as it saves trouble later if the code need to be used in a cross-product context.

DEMO Code

Dim THisRow As Range
Dim GetLastColLetter As String
  ...
Set Sourcewb = Workbooks.Open(filepath & Filename, False)      (This works - DOH)
   ...  
 Set SourceSh = Sourcewb.Sheets("Agg Perf History")
                       THisRow = SourceSh.Range("a61:aaa61")
                       lacall = GetLastColLetter(THisRow)
                       MsgBox "lacall is: " & lacall

------------

When I run this, I get 'Compile Error'

AS you can see, I am looking at the row a61 to ea61 and want to find the column ID for the latest value.   Is there a simple code to select this row and call the function so I can use the column ID in a number of copy&paste commands.


Thanks,
Don

Thanks to both of you.