Select first empty cell in column F starting from row 1. (without using offset )

Question

This is one query that I am really confused with. Coz I have looked for this so many times but I always find the codes related to finding the last used or first non empty cell. Tried at below codes. diff codes have been separated by the word "even"

iRow = Worksheets("Sheet1").Cells(Rows.Count,1).End(XlUp).Row

even

Sub LastCellBeforeBlankInColumn()

Range("A1").End(xldown).Select

End Sub

even

Find the very last used cell in a Column:

Sub LastCellInColumn()

Range("A65536").End(xlup).Select

End Sub

even

Find the last cell, before a blank in a Row:

Sub LastCellBeforeBlankInRow()

Range("A1").End(xlToRight).Select

End Sub

even

Find the very last used cell in a Row:

Sub LastCellInRow()

Range("IV1").End(xlToLeft).Select

End Sub

even

Worksheets("Sheet1").Range("A1").End(xlDown).Row + 1

even

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("SheetName").Range("A" & LastRow).Paste

even

Dim FirstBlankCell as Range
Set FirstBlankCell=Range("A" & rows.Count).end(xlup).offset(1,0)
FirstBlankCell.Activate

'Find the last used row in a Column: column A in this example
Dim LastRow As Long
Dim NextRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
End With
NextRow = LastRow + 1

Have you seen this? http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba — Siddharth Rout, Feb 19 '13 at 12:53
Isnt it the same thing. I want to select or activate the first empty cell. example: If There are values till cell F5 then I want to activate Cell F6 and not use Offset while doing so — Nishant, Feb 19 '13 at 13:00
If you know the last row then simply use `Range("F" & LastRow).Activate` Though I am not too much in favor of `.Activate` — Siddharth Rout, Feb 19 '13 at 13:05
Siddharth i know that... but then again the LastRow is the used cell with some value. I want the cell below it — Nishant, Feb 19 '13 at 13:16
`LastRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1` will give you that row ;) — Siddharth Rout, Feb 19 '13 at 13:17
this doesn't work for finding first empty cell in a column, unless it's always the last cell of the column... user3517220's answer works though with a bit of correction — Patrick Lepelletier, Jul 05 '14 at 23:31
Realize this is an older post, does anyone know the code for the first empty cell in VBScript? — DrHouseofSQL, Sep 22 '17 at 18:58

score 20 · Answer 1 · answered Jun 25 '14 at 13:02

20

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.

answered Jun 25 '14 at 13:02

Cameronface

209
2
2

For anyone else looking for help on this, this is the one that works. Everything above this doesn't work at all. – Bisclavret Jun 09 '22 at 10:37

score 19 · Accepted Answer · answered Feb 19 '13 at 16:09

19

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

answered Feb 19 '13 at 16:09

StoriKnow

5,738
6
37
46

1

Hey Thanks Sam This is what I was trying to do. Though I looked at Siddharth's suggestion and that worked too and have already used it. But your ans would be useful in the future to me. thanks alot – Nishant Feb 20 '13 at 07:58
1

Is there a way to do this as a formula rather than in VBA? – Raj Jul 06 '16 at 19:54
Can't get this to work when there is only one cell above it. E.g. F1 has a value but F2 does not. – user2924019 May 02 '18 at 14:02
Why the number 6? I don't get it... Please explain. – Soufiane Sabiri May 14 '19 at 13:23
1

@SabiriS in the screenshots I'm working out of column `F`. `F` is the 6th column if you count from left to right. `A = 1`, `B = 2` ... `F = 6`. – StoriKnow May 14 '19 at 13:29

score 19 · Answer 3 · edited Apr 04 '18 at 14:18

19

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

edited Apr 04 '18 at 14:18

Laurenz Albe

209,280
17
206
263

answered Nov 20 '15 at 14:16

Chandan B

217
2
4

1

By far the simplest answer. – doriansm Oct 02 '17 at 22:39
3

Not great if the column is empty as it will just select the absolute last cell. – user2924019 Oct 02 '18 at 16:21
Please stop use `.Select`~! – FreeSoftwareServers Oct 23 '19 at 04:55

score 9 · Answer 4 · answered Apr 02 '14 at 12:52

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

and you might want to use Long instead of Integer, because rows can go very far down (or up ?) — Patrick Lepelletier, Jul 05 '14 at 23:23

score 7 · Answer 5 · answered Aug 13 '16 at 01:14

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

Patrick Lepelletier · Answer 6 · 2016-07-05T15:57:23.400

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

This is really overkill and doesn't work for empty columns. – user2924019 May 02 '18 at 14:28 — user2924019, May 02 '18 at 14:28

user2924019 · Answer 7 · 2020-04-01T10:24:17.260

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

edited Apr 01 '20 at 10:24

answered May 02 '18 at 14:26

user2924019

1,983
4
29
49

1

Just curious, what is the purpose of `NextCell`? – Jonny Henly Mar 26 '20 at 18:04
1

Removed this, wasn't needed in this example. – user2924019 Apr 01 '20 at 10:24
What's "very fast and clean" about this? – Excel Hero May 02 '20 at 21:39
1

It doesn't move the active cell around while it runs, which is slow and clunky, a misclick while it runs will switch the column during the script, it can also result in 'not responding' when moving the active cell. Try it and see the difference. – user2924019 May 03 '20 at 22:51
@ExcelHero I think you're confused. It doesn't interact with the spreadsheet. Only the last thing it does is select the blank cell. I literally created this to solve this problem and to also allow it to work on empty columns. – user2924019 Apr 05 '22 at 15:55

score 1 · Answer 8 · edited Apr 27 '15 at 07:06

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 · Answer 9 · answered Nov 16 '19 at 12:36

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 0 · Answer 10 · answered Sep 29 '18 at 19:30

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 · Answer 11 · answered Sep 11 '19 at 06:40

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 · Answer 12 · answered Apr 30 '20 at 22:19

0

NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 1

answered Apr 30 '20 at 22:19

RWB

107
8

2

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel May 02 '20 at 10:14

score 0 · Answer 13 · edited Aug 07 '20 at 12:15

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

edited Aug 07 '20 at 12:15

Roberto Caboni

7,252
10
25
39

answered Aug 06 '20 at 19:10

Piethagoras

1

Oddclock · Answer 14 · 2021-09-12T07:16:03.367

.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 0 · Answer 15 · answered Nov 12 '22 at 08:39

This formula uses the built-in ISBLANK(range) function to match to the first empty row number in a column:=MATCH(TRUE,ISBLANK(F:F),0)

You can narrow down the range in the usual fashion, of course.

To count the number of non-blank entries up to that point, subtract 1: =MATCH(TRUE,ISBLANK(F:F),0)-1 which is useful (eg) for counting the number of entries in a dynamically changing table such as the number of transactions coming in on a csv or similar import. You can exclude header rows by subtracting the number of those; or by changing the range to (F1:Fxxxx) where xxxx is a number greater than the highest expected range.

Select first empty cell in column F starting from row 1. (without using offset )

15 Answers15

Linked