Copy cell value to first empty cell in "B" column

Question

How to copy cell value to first empty cell in B column, so if range("B22") have value than copy below

My code is

Set Rng = Range("B36:B360")
For Each cll In Rng
   If cll.Row < Today And cll.Value <> "" And cll.Font.Color = vbBlack Then
If Range("B22") = "" Then
    Range("B22") = cll
Else
    Range("B23") = cll
End If

But i will have 10 cells or more that i need to copy in B22 cell and below, so there must be another way to paste these cells?

Christofer Weber Christofer Weber · Accepted Answer · 2019-05-15T21:09:00

Instead of trying to make an IF ELSE for each possible scenario, you can make a loop.

Something along the lines of

For i = 22 To 36 'Or however far you are willing to go down
    If Cells(i, 2) = "" Then '2 Here being column 2, or "B"
        Cells(i, 2) = cll
        Exit For 'Stop looking once we found the empty cell
    End If
Next i

I mostly use variation of Range("B" & Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row)
To find the first empty cell after the last used cell. But it doesn't work if you have empty rows in-between your data.

You could also use find

Set emptyRng = Range("B22:B" & Rows.Count).Find(What:="", lookat:=xlWhole)
emptyRng.Value = cll

And other combinations. Here I put Rows.Count as end limit instead, just to show some options.

Copy cell value to first empty cell in "B" column

1 Answers