Trying to adjust a macro to go to next open cell, not last cell in column that's open

Question

I currently send a cells value to another spreadsheet to the next open cell in a column. But my current code sends it to the bottom most cell after all the values, so if I delete any cells in between it still only posts at the end of the data. I would like it to fill in cells I have deleted so it's not leaving empty rows. Any help is greatly appreciated.

Sub S2WL()

     Dim MyValue As Variant: My Value = ThisWorkbook.Activesheet.Range("C2").Value
     With Workbooks("Dash").Worksheets("DASH")
     Dim last As Long: last = .Cells(.Rows.Count, "JF").End(xlUp).Row + 1
     .Cells(last, "JF").Value = MyValue

End sub

I tried declaring a few more variables to try and loop it through but I can't get it to work, it keeps posting only in the very first cell.

Answer 1

Edit

I've updated my code to handle the special case where either JF1 or JF2 is empty.

@BigBen made a good point. Ranges behave differently in tables. This assumes column JF is not part of a table. As for looping, per @Tony comment, I assume you're calling this sub from a loop. But for looping, remove the hardcoded "C2" for MyValue. You'd want to loop through input values, no?

Sub S2WL()
    Dim last As Long, rngCell As Range
    Dim MyValue As Variant: MyValue = ThisWorkbook.ActiveSheet.Range("C2").Value
    
    With Workbooks("Dash").Worksheets("DASH")
        Set rngCell = .Cells(1, "JF")
        
        ' Find first empty cell
        If rngCell = "" Then  ' Special case: JF1 = ""
            last = 1
        ElseIf rngCell.Offset(1, 0) = "" Then  ' Special case: JF1 <> "", JF2 = ""
            last = 2
        Else   ' JF1 <> "", JF2 <> ""; end-down is safe
            Set rngCell = rngCell.End(xlDown)
            last = rngCell.Row
            If last < .Rows.Count Then  ' Avoid error of adding 1 to the last row
                last = last + 1
            End If
        End If
        
        If .Cells(last, "JF").Value = "" Then .Cells(last, "JF").Value = MyValue
    End With
End Sub