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.

You could look [here](https://stackoverflow.com/questions/14957994/select-first-empty-cell-in-column-f-starting-from-row-1-without-using-offset) to see how to loop through to add to @Richard answer — Kavorka, May 24 '23 at 20:08

RichardCook · Accepted Answer · 2023-05-25T00:09:35.483

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

Hey, Thnx so much everyone! That worked with a slight adjustment RichardCook! BigBen its not a table as everything populates(using index, match) from other spreadsheets once the ticker, which is being pulled from C2 moves to row JF in "DASH". The first three rows are headers and other information so I only changed: (not sure if that was the right way but its working) `Set rngCell = .Cells(4, "JF")` And sorry, looping must be the wrong term as its always Cell C2 with a ticker, but I have copied the same sheet multiple times so it's what sheet is active. — JupBrew, May 25 '23 at 09:11
Set it back to Set rngCell = .Cells(1, "JF") and you'll be safe. Otherwise you still face the same problem, if JF4 or JF5 is empty. To see the problem, in a different sheet, put some random data in A2:A5, leave row 1 empty. Then select the empty A1 and press ctrl-down. It will select the FIRST non-empty cell instead of the LAST, so the code won't work correctly. As long as the first 2 rows have entries ctrl-down will take you to the last non-empty cell. — RichardCook, May 25 '23 at 11:54

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

1 Answers1

Edit