Advertisement
Advertisement


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
2018/07/09
1
23
7/9/2018 7:34:03 PM

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

2013/02/19
16
2/19/2013 4:09:49 PM


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.

2014/06/25

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

2014/04/02

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
2016/08/13

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
2016/07/05

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
2020/04/01