使用 Excel vba 查找并选择 B 列中的第一个空白单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25155751/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find and select first blank cell in column B with Excel vba
提问by Stan
The below code works fine to find the first empty cell in a given column (here column B). But what I need is a code to find the first blankcell in that column.
下面的代码可以很好地找到给定列（此处为 B 列）中的第一个空单元格。但我需要的是一个代码来查找该列中的第一个空白单元格。
Sub macro1()
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String
sourceCol = 2 'column B has a value of 2
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
Also, it should start looking from row 10 instead of row 1.
此外，它应该从第 10 行而不是第 1 行开始查找。
Can somebody rewrite this code to do this?
有人可以重写这段代码来做到这一点吗？
回答by Tom
Could something like this be what you're looking for:
像这样的事情可能是你正在寻找的：
Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(2).Cells
If IsEmpty(cell) = True Then cell.Select: Exit For
Next cell
End Sub
This will run through each cell in column B in the active worksheet and select the first empty one it comes across. To set the sheet to a particular one Change Set ws = ActiveSheetto Set ws = Sheets("EnterSheetNameHere")
这将遍历活动工作表中 B 列中的每个单元格，并选择它遇到的第一个空单元格。将工作表设置为特定的更改Set ws = ActiveSheet为Set ws = Sheets("EnterSheetNameHere")
Or you could try using:
或者您可以尝试使用：
Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
For Each cell In ws.Columns(2).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell
End Sub
回答by Stan
My problem is solved by using the following code.
我的问题是通过使用以下代码解决的。
Sheets("sheet1").Select
Dim LR2 As Long, cell2 As Range, rng2 As Range
With Sheets("sheet1")
LR2 = .Range("B" & Rows.Count).End(xlUp).Row
For Each cell2 In .Range("B8:B" & LR2)
If cell2.Value <> "" Then
If rng2 Is Nothing Then
Set rng2 = cell2
Else
Set rng2 = Union(rng2, cell2)
End If
End If
Next cell2
rng2.Select
End With
回答by Eilthalearin Kheru
Just my two cents.
只有我的两分钱。
The function will look for the first encountered BLANK cell in a range, so it should work with columns and rows.
该函数将查找范围内第一个遇到的空白单元格，因此它应该适用于列和行。
'Find first BLANK cell in a given range, returnt a range (one cell)
Function FirstBlank(ByVal rWhere As Range) As Range
Dim vCell As Variant
Dim answer As Range
Set answer = Nothing
For Each vCell In rWhere.Cells
If Len(vCell.Formula) = 0 Then
Set answer = vCell
Exit For
End If
Next vCell
Set FirstBlank = answer
End Function
And then do whatever you want with the cell.
然后对细胞做任何你想做的事情。
回答by user10287104
Try this code to select the first empty cell below cell B10. But it requires B10 and B11 to be pre-occupied.
尝试使用此代码选择单元格 B10 下方的第一个空单元格。但是需要B10和B11预占位。
Range("B10").End(xlDown).Offset(1, 0).Select
Range("B10").End(xlDown).Offset(1, 0).Select
or
或者
Range("B100000").End(xlUp).Offset(1, 0).Select
Range("B100000").End(xlUp).Offset(1, 0).Select
