score:19

Accepted answer

if all you're trying to do is select the first blank cell in a given column, you can give this a try:

code:

public sub selectfirstblankcell()
    dim sourcecol as integer, rowcount as integer, currentrow as integer
    dim currentrowvalue as string

    sourcecol = 6   'column f has a value of 6
    rowcount = cells(rows.count, sourcecol).end(xlup).row

    'for every row, find the first blank cell and select it
    for currentrow = 1 to rowcount
        currentrowvalue = cells(currentrow, sourcecol).value
        if isempty(currentrowvalue) or currentrowvalue = "" then
            cells(currentrow, sourcecol).select
        end if
    next
end sub

before selection - first blank cell to select:

enter image description here

after selection:

enter image description here

score:0

i think a do until-loop is cleaner, shorter and more appropriate here:

public sub selectfirstblankcell(col as string)
    dim column_index as integer
    dim row_counter as 

    column_index = range(col & 1).column
    row_counter = 1

    do until isempty(cells(row_counter, 1))
        row_counter = row_counter + 1
    loop

    cells(row_counter, column_index).select

score:0

there is another way which ignores all empty cells before a nonempty cell and selects the last empty cell from the end of the first column. columns should be addressed with their number eg. col "a" = 1.

with thisworkbook.sheets("sheet1")

        .cells(.cells(.cells.rows.count, 1).end(xlup).row + 1, 1).select

end with

the next code is exactly as the above but can be understood better.

i = thisworkbook.sheets("sheet1").cells.rows.count

j = thisworkbook.sheets("sheet1").cells(i, 1).end(xlup).row

thisworkbook.sheets("sheet1").cells(j + 1, 1) = textbox1.value

score:0

nextrow = application.worksheetfunction.counta(range("a:a")) + 1

score:0

i just wrote this one-liner to select the first empty cell found in a column based on a selected cell. only works on first column of selected cells. modify as necessary

selection.end(xldown).range("a2").select

score:0

.find has a lot of options and returns nothing if there are no empty cells found in your range:

with range("f:f")
     .find("", .rows(.rows.count), xlformulas, , xlbyrows, xlnext).select
end with

.find starts searching after the first cell in the range by default, so it would normally start in row 2. it will find the first empty cell starting in row 1, if its after argument is the last cell in the range. that's because the search wraps.

score:1

public sub selectfirstblankcell()
    dim sourcecol as integer, rowcount as integer, currentrow as integer
    dim currentrowvalue as string

    sourcecol = 6   'column f has a value of 6
    rowcount = cells(rows.count, sourcecol).end(xlup).row

    'for every row, find the first blank cell and select it
    for currentrow = 1 to rowcount
        currentrowvalue = cells(currentrow, sourcecol).value
        if isempty(currentrowvalue) or currentrowvalue = "" then
            cells(currentrow, sourcecol).select
        end if
    next
end sub

if any column contains more than one empty cell continuously then this code will not work properly

score:1

i found this thread while trying to carry out a similar task. in the end, i used

range("f:f").specialcells(xlblanks).areas(1)(1).select

which works fine as long as there is a blank cell in the intersection of the specified range and the used range of the worksheet.

the areas property is not needed to find the absolute first blank in the range, but is useful for finding subsequent non consecutive blanks.

score:2

i adapted a bit the code of everyone, made it in a function, made it faster (array), and added parameters :

public function firstblankcell(optional sh as worksheet, optional sourcecol as long = 1, optional byval startrow& = 1, optional byval selectcell as boolean = false) as long
dim rowcount as long, currentrow as long
dim currentrowvalue as string
dim data()
if sh is nothing then set sh = activesheet

with sh

    rowcount = .cells(.rows.count, sourcecol).end(xlup).row
    data = .range(.cells(1, sourcecol), .cells(rowcount, sourcecol)).value2

    for currentrow = startrow to rowcount
        if data(currentrow, sourcecol) = vbnullstring then
            if selectcell then .cells(currentrow, sourcecol).select
            'if selection is out of screen, intead of .select , use : application.goto reference:=.cells(...), scroll:= true
            firstblankcell = currentrow
            exit for
        end if
    next

end with ' sh

erase data
set sh = nothing
end function

score:2

this is a very fast and clean way of doing it. it also supports empty columns where as none of the answers above worked for empty columns.

usage: selectfirstblankcell("f")

public sub selectfirstblankcell(col as string)

    dim i as integer

    for i = 1 to 10000
        if range(col & cstr(i)).value = "" then
            exit for
        end if
    next i

    range(col & cstr(i)).select
end sub

score:7

if you are looking for a one liner (not including designations and comments) try this

dim irow as long
dim ws as worksheet
set ws = worksheets("name")

    'find first empty cell in column f (coming up from the bottom) and return row number
irow = ws.range("f:f").find(what:="*", searchorder:=xlrows, searchdirection:=xlprevious, lookin:=xlvalues).row + 1

score:9

code of sam is good but i think it need some correction,

public sub selectfirstblankcell()
    dim sourcecol as integer, rowcount as integer, currentrow as integer
    dim currentrowvalue as string

    sourcecol = 6   'column f has a value of 6
    rowcount = cells(rows.count, sourcecol).end(xlup).row

    'for every row, find the first blank cell and select it
    for currentrow = 1 to rowcount
        currentrowvalue = cells(currentrow, sourcecol).value
        if isempty(currentrowvalue) or currentrowvalue = "" then
            cells(currentrow, sourcecol).select
            exit for 'this is missing...
        end if
    next
end sub

thanks

score:17

in case any one stumbles upon this as i just have...

find first blank cell in a column(i'm using column d but didn't want to include d1)

nextfree = range("d2:d" & rows.count).cells.specialcells(xlcelltypeblanks).row
range("d" & nextfree).select

nextfree is just a name, you could use sausages if you wanted.

score:17

if all you're trying to do is select the first blank cell in a given column, you can give this a try:

range("a1").end(xldown).offset(1, 0).select

if you're using it relative to a column you've selected this works:

selection.end(xldown).offset(1, 0).select

Related Query

More Query from same tag