vba 从第 1 行开始选择 F 列中的第一个空单元格。（不使用 offset ）

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

提问by Nishant

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

采纳答案by StoriKnow

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:

选择之前 - 要选择的第一个空白单元格：

After Selection:

选择后：

回答by Cameronface

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)

在列中查找第一个空白单元格（我正在使用 D 列但不想包含 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.

NextFree 只是一个名字，如果你愿意，你可以使用香肠。

回答by Chandan B

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

回答by RedLeo

Code of Sam is good but I think it need some correction,

Sam 的代码很好，但我认为它需要一些更正，

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

谢谢

回答by CammoL

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

回答by user2924019

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")

用法： 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

回答by Patrick Lepelletier

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

回答by Goka raju

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

如果任一列连续包含多个空单元格，则此代码将无法正常工作

回答by Matthias Baetens

I think a Do Until-loop is cleaner, shorter and more appropriate here:

我认为Do Until-loop 在这里更干净、更短、更合适：

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

回答by amir talebi

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.

还有另一种方法可以忽略非空单元格之前的所有空单元格，并从第一列的末尾选择最后一个空单元格。列应该用它们的编号来寻址，例如。列“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