VBA 用数据识别第一行和最后一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43179096/
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
VBA identify first and last rows with data
提问by m4sterbunny
I want first and last rows with non-blank cells. My last row works fine, the first row is bust. Suggestions appreciated.
我想要带有非空白单元格的第一行和最后一行。我的最后一排工作正常，第一排是半身像。建议表示赞赏。
Sub idDataRange()
Dim firstRow As Long
Dim lastRow As Long
Sheets("fileNames").Select
' this has been adapted from a Stack overflow answer. lastRow unedited
' first row I changed Up to Down = NOT the solution!
With ActiveSheet
firstRow = .Range("B" & .Rows.Count).End(xlDown).row
lastRow = .Range("B" & .Rows.Count).End(xlUp).row
End With
MsgBox "the first row is " & firstRow
MsgBox "last row is " & lastRow
End Sub
回答by user3598756
if values in column B are not derived from formulas, then you can use SpecialCells():
如果 B 列中的值不是从公式派生的，那么您可以使用SpecialCells()：
Dim firstRow As Long
Dim lastRow As Long
With Sheets("fileNames").Columns("B") '<--| reference your sheet (activating it is bad practice!) column "B" range
If WorksheetFunction.CountA(.Cells) = 0 Then '<--| if no data whatever
MsgBox "Sorry: no data"
Else
With .SpecialCells(xlCellTypeConstants) '<--| reference its cells with constant (i.e, not derived from formulas) values)
firstRow = .Areas(1).Row
lastRow = .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Rows.Count).Row
End With
MsgBox "the first row is " & firstRow
MsgBox "last row is " & lastRow
End If
End With
回答by Darren Bartrup-Cook
Using Find:
使用Find：
Edit: Doesn't appear to find the first cell if it's A1.
I've added .Cells(.Rows.Count, .Columns.Count)to the two Findrows. It will still muck up if the last cell on the sheet is populated - but in 19 years I've never filled an entire sheet with data.
编辑：如果是 A1，则似乎找不到第一个单元格。
我已经添加.Cells(.Rows.Count, .Columns.Count)到两Find行。如果工作表上的最后一个单元格被填充，它仍然会很糟糕 - 但在 19 年里，我从来没有用数据填充整个工作表。
Sub test()
Dim rLastCell As Range
MsgBox LastCell(Sheet1).Address 'Last Cell
MsgBox LastCell(Sheet1, 1).Address 'First Cell.
End Sub
'---------------------------------------------------------------------------------------
' Arguments : Direction = 2 :Find Last Cell, 1 :Find First Cell
'---------------------------------------------------------------------------------------
Public Function LastCell(wrkSht As Worksheet, Optional Direction As Long = 2) As Range
Dim lLastCol As Long, lLastRow As Long
On Error Resume Next
With wrkSht
lLastCol = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , xlByColumns, Direction).Column
lLastRow = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , xlByRows, Direction).Row
If lLastCol = 0 Then lLastCol = 1
If lLastRow = 0 Then lLastRow = 1
Set LastCell = wrkSht.Cells(lLastRow, lLastCol)
End With
On Error GoTo 0
End Function
回答by Michael
This line works by starting at the bottom of the B column and then working up:
这条线的工作原理是从 B 列的底部开始，然后向上工作：
lastRow = .Range("B" & .Rows.Count).End(xlUp).row
To find the first row, you need to start at the top of the sheet and then work down instead, but also checking that the first row doesn't have anything:
要找到第一行，您需要从工作表的顶部开始，然后向下工作，但还要检查第一行是否没有任何内容：
firstRow = iif(isempty(.Range("B1")),.Range("B1").End(xlDown).row,1)
Note that the formula for lastRow assumes that there is no data in the very last cells of column B. Also, my formula for firstRow assumes there is at least one cell in column B with a value.
请注意，lastRow 的公式假定 B 列的最后一个单元格中没有数据。此外，我的 firstRow 公式假定 B 列中至少有一个单元格具有值。
回答by dudshev
'USING features of SELECTION
'使用 SELECTION 的特性
firstrow = Selection.Value
第一行 = Selection.Value
lastrow = Cells(Selection.Rows.Count + ActiveCell.Row - 1, ActiveCell.Column)
lastrow = Cells(Selection.Rows.Count + ActiveCell.Row - 1, ActiveCell.Column)
