loop through many (thousands of) rows find last cell with data, write value to set column

[svancleave]

I am tasked with cleaning up and rebuilding a filing system that has been unmanaged for 20 yrs. First step was to pull fully qualified file names with owner into text file and upload into Excel. I need to be able to sort on owner, so need "Owner" data to be written to a single column. Fully qualified file names are in some cases 20 directories deep! It's a mess. I used back-slash as delimiter. There are a dozen drives, each with hundreds of thousands of files. This is my first post, and attempted to attach small spread sheet.

P-code might go like this:

For each populated row in Worksheet
find first empty cell in first row
write offset (0,-1) value to cell in same row first empty column
move to the next row
and so on

I have searched and struggled for some time. All code attempts--and there have been many--end up throwing object-related errors of some kind.

Here is just one example of failed code. I have tried A1 and R1C1. I would also like to steer away from the three nested levels shown here. Error is always thrown on line with row range reference, like "Range(lngRowCounter, lngLastColumn).Value = rngCell.Offset(0, -1).Value". I am using a named range in this case.

Sub GetFirstEmpty()
    Dim lngRowCounter As Long
    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim rngCell As Range
    Dim obSheet As Worksheet
    Dim rngRow As Range

    Set obSheet = ActiveSheet
    lngLastRow = obSheet.Range("WholeFS").Rows.Count
    lngLastColumn = (obSheet.Range("WholeFS").Columns.Count) + 1
    
    For lngRowCounter = 1 To lngLastRow
    Set rngRow = obSheet.Rows(lngRowCounter)
        For Each rngCell In rngRow.Cells
            If rngCell.Value = "" Then
                Range(lngRowCounter, lngLastColumn).Value = rngCell.Offset(0, -1).Value
            Else
            End If
        Next rngCell
    Next

I know I'm missing something, and likely not referring to the target cell properly. Any and all help will be much appreciated!

[nilem]

Hi svancleave,
maybe so

Sub GetFirstEmpty()
Dim r As Range
'For each populated row in Worksheet
For Each r In ActiveSheet.Columns(1).SpecialCells(2)
    'find the last non-empty cell in the row
    With Cells(r.Row, Columns.Count).End(xlToLeft)
        'write offset (0,-1) value to cell in same row first empty column
        .Offset(, 1).Value = .Value
    End With
    'move to the next row
Next r
End Sub