Select first empty cell in range

Question

So my very basic VBA skills limit my programming codes. I want to search within a Range("A14:A23") the first empty cell in order to fill in a value. I have to do this within various ranges in A, so I can't just use the lastcell in column code. Can anyone help me?

What do you mean by various ranges? And please share what you have coded so far (and if you haven't, give it a go and then post back updating the question with your code and any problems experienced) — QHarr, Dec 04 '17 at 20:29

score 3 · Answer 1 · answered Dec 04 '17 at 20:49

3

In relation to Scott Craner's comment, you can use .Select (which in most cases is advised against).

Please see How to avoid .Select

Sub FindEmptyCell()

   ActiveSheet.Range("A1:A10").Find("").Select

End Sub

This will select the first empty cell

answered Dec 04 '17 at 20:49

Maldred

1,074
4
11
33

this is way better than a loop – Fandango68 Sep 11 '18 at 03:18

score 0 · Answer 2 · answered Dec 04 '17 at 20:58

If you're using various ranges, you could write it as a subroutine to take the first and last cells of the range as arguments:

Sub FindEmpty(a, b)

    ' loop through the range, find the first empty cell, then exit the loop

    For Each x In ActiveSheet.Range(a, b)
        If x.Value = "" Then
            ' do something here
            Exit For
        End If
    Next x

End Sub

a would be the first cell in the range and b would be the last cell in the range. Once the first empty cell is found, you can execute your code, then quit the subroutine.

You could also set the subroutine to grab those values (first and last cell) from a cell on your spreadsheet instead of passing them in directly, or you could hardcode them into the subroutine if you wanted.

Select first empty cell in range

2 Answers2