选择列中的第一个空单元格，适用于空列

Question

我需要找到列中的第一个空白单元格。这个问题的解决方案很简单，假设列中有2个或更多的填充单元格。

Range("A1").End(xlDown).Offset(1, 0).Select

如果唯一填充的单元格是A1或A1为空白，则此操作将停止。

在这种情况下，它将选择工作簿中的最后一个单元格。

是否有任何工作，将始终选择列中的第一个空白单元格，即使该单元格恰好是A1或A2?

Answer 1

下面是测试我们找到的单元格是否为空以及A1的解决方案是空的:

Dim Rng As Range
Set Rng = Range("A1").End(xlDown)
If Rng.Value = "" Then
If Range("A1").Value = "" Then
Range("A1").Select
Else
Range("A2").Select
End If
Else
Rng.Offset(1, 0).Select
End If

在你写的评论中，你不喜欢代码的顺序，这里是另一个例子:

If Range("A1").Value = "" Then
Range("A1").Select
ElseIf Range("A2").Value = "" Then
Range("A2").Select
Else
Range("A1").End(xlDown).Offset(1, 0).Select
End If

下面是另一个避免使用End()的例子和抵消():

Dim Cnt As Long
Cnt = ActiveSheet.UsedRange.Rows.Count
If Cnt = 1 And Range("A1").Value = "" Then Cnt = 0
Range("A" & Cnt + 1).Select

如果你添加一个标题行，那么这个例子可以工作:

Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

我总是在所有表格数据的工作表中包含标题行，以限制特殊情况-它也更用户友好。

Answer 2

通过循环查找第一个空单元格

空

• 除了循环遍历单元格之外，还有各种或多或少可靠的方法。
• 如果有隐藏的行或列，其中许多将无法工作。
• 更糟糕的是，如果工作表被过滤，可能大多数工作表都无法工作。

基本循环

• 如果您循环遍历单元格并测试每个单元格，您肯定会得到正确的结果。

Function RefFirstEmptyCellInColumnBasic( _
ByVal FirstCell As Range) _
As Range

' Validate the given range ('FirstCell').
If FirstCell Is Nothing Then Exit Function

' Create a reference to the Column Range ('crg').
With FirstCell.Cells(1)
Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
End With

' Loop.
Dim cCell As Range
' Loop through the cells of the Column Range...
For Each cCell In crg.Cells
' ... until an empty cell is found.
If IsEmpty(cCell) Then
' Create a reference to the current cell.
Set RefFirstEmptyCellInColumnBasic = cCell
Exit Function
End If
Next cCell
End Function

• 问题是可能需要很长时间。它将"表现"几千行，但例如，如果第一个空单元格是列中的最后一个单元格，那么前面的代码在我的机器上需要"永远"(5s)。

内存循环(Array)

• 为了解决这个问题，你可以在前面的代码中引入一个数组，这将减少十倍的执行时间(0.5s)。(请注意，每次只将值写入数组就会使用0.05s)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the top-most empty cell
'               in the one-column range from the first cell of a range
'               ('FirstCell') through the last cell in its column.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefFirstEmptyCellInColumn( _
ByVal FirstCell As Range) _
As Range

' Validate the given range ('FirstCell').
If FirstCell Is Nothing Then Exit Function

' Create a reference to the Column Range ('crg').
With FirstCell.Cells(1)
Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
End With

' Write the values from the Column Range to the Column Data Array ('cData').
Dim cData As Variant
If crg.Rows.Count = 1 Then ' only one cell
ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
Else
cData = crg.Value
End If

' Loop.
Dim r As Long
' Loop through the elements of the Column Data Array...
For r = 1 To UBound(cData, 1)
' ... until an empty value is found.
If IsEmpty(cData(r, 1)) Then
' Create a reference to the r-th cell of the Column Range.
Set RefFirstEmptyCellInColumn = crg.Cells(r)
Exit Function
End If
Next r
End Function

测试

• 要测试前面的内容，您可以执行以下操作。

Sub RefFirstEmptyCellInColumnTEST()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fCell As Range: Set fCell = ws.Range("A3")
' Empty
Dim feCell As Range: Set feCell = RefFirstEmptyCellInColumn(fCell)
If Not feCell Is Nothing Then
Debug.Print feCell.Address(0, 0)
End If
End Sub

空白

• 您可以对空白单元格执行相同的操作，即空单元格或包含单个引号(')的单元格或包含公式评估为""的单元格。注意，包含空格的单元格既不是空也不是空。

Function RefFirstBlankCellInColumnBasic( _
ByVal FirstCell As Range) _
As Range ' (Empty, ="" and ')

' Validate the given range ('FirstCell').
If FirstCell Is Nothing Then Exit Function

' Create a reference to the Column Range ('crg').
With FirstCell.Cells(1)
Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
End With

' Loop.
Dim cCell As Range
' Loop through the cells of the Column Range...
For Each cCell In crg.Cells
' (exclude cell containing error value)
If Not IsError(cCell) Then
' ... until a blank cell is found.
If Len(cCell.Value) = 0 Then
' Create a reference to the current cell.
Set RefFirstBlankCellInColumnBasic = cCell
Exit Function
End If
End If
Next cCell
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the top-most blank cell
'               in the one-column range from the first cell of a range
'               ('FirstCell') through the last cell in its column.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefFirstBlankCellInColumn( _
ByVal FirstCell As Range) _
As Range ' (Empty, ="" and ')

' Validate the given range ('FirstCell').
If FirstCell Is Nothing Then Exit Function

' Create a reference to the Column Range ('crg').
With FirstCell.Cells(1)
Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
End With

' Write the values from the Column Range to the Column Data Array ('cData').
Dim cData As Variant
If crg.Rows.Count = 1 Then ' only one cell
ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
Else
cData = crg.Value
End If

' Loop.
Dim r As Long
' Loop through the elements of the Column Data Array...
For r = 1 To UBound(cData, 1)
' (exclude error values)
If Not IsError(cData(r, 1)) Then
' ... until a blank is found.
If Len(cData(r, 1)) = 0 Then
' Create a reference to the r-th cell of the Column Range.
Set RefFirstBlankCellInColumn = crg.Cells(r)
Exit Function
End If
End If
Next r
End Function
Sub RefFirstBlankCellInColumnTEST()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim fCell As Range: Set fCell = ws.Range("A3")
' Blank
Dim fbCell As Range: Set fbCell = RefFirstBlankCellInColumn(fCell)
If Not fbCell Is Nothing Then
Debug.Print fbCell.Address(0, 0)
End If
End Sub