vba 找到最后使用的行的更好方法

Better way to find last used row

提问by Alexis Moreno

I am trying to make this way of finding the last row as I found the last column:

我试图通过这种方式找到最后一行，因为我找到了最后一列：

Sheets("Sheet2").Cells(1,Sheets("Sheet2").Columns.Count).End(xlToLeft).Column`enter code here`

I know how to do it this way but it is not as helpful as the prior would be:

我知道如何以这种方式做到这一点，但它不如之前的有用：

u = Sheets("Sheet1").Range("A65536").End(xlUp).Row

I tried doing this:

我尝试这样做：

Sheets("Sheet2").Cells(Sheets("Sheet2",1).Rowa.Count).End(xlToUP).Column

Any help would be greatly appreciated.

任何帮助将不胜感激。

Synopsis:Would like the below way again for last row. Thanks

剧情简介：最后一排还想要下面的方式。谢谢

Sheets("Sheet2").Cells(1,Sheets("Sheet2").Columns.Count).End(xlToLeft).Column`enter code here`

回答by

You should use a with statement to qualify both your Rowsand Columnscounts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.

您应该使用 with 语句来限定您的Rows和Columns计数。这将防止在使用较早的 2007 年之前和较新的 2007 Excel 工作簿时出现任何错误。

Last Column

最后一列

With Sheets("Sheet2")
    .Cells(1, .Columns.Count).End(xlToLeft).Column
End With 

Last Row

最后一行

With Sheets("Sheet2")
    .Range("A" & .Rows.Count).End(xlUp).Row
End With 

Or

或者

With Sheets("Sheet2")
    .Cells(.Rows.Count, 1).End(xlUp).Row
End With 

回答by Phil Brewer

I use this routine to find the count of data rows. There is a minimum of overhead required, but by counting using a decreasing scale, even a very large result requires few iterations. For example, a result of 28,395 would only require 2 + 8 + 3 + 9 + 5, or 27 times through the loop, instead of a time-expensive 28,395 times.

我使用这个例程来查找数据行的计数。所需的开销最小，但通过使用递减的规模进行计数，即使是非常大的结果也需要很少的迭代。例如，结果 28,395 只需要 2 + 8 + 3 + 9 + 5，即循环 27 次，而不是耗时的 28,395 次。

Even were we to multiply that by 10 (283,950), the iteration count is the same 27 times.

即使我们将其乘以 10 (283,950)，迭代次数也是相同的 27 次。

Dim lWorksheetRecordCountScaler as Long
Dim lWorksheetRecordCount as Long

Const sDataColumn = "A"   '<----Set to column that has data in all rows (Code, ID, etc.)

    'Count the data records
    lWorksheetRecordCountScaler = 100000  'Begin by counting in 100,000-record bites
    lWorksheetRecordCount = lWorksheetRecordCountScaler

    While lWorksheetRecordCountScaler >= 1

        While Sheets("Sheet2").Range(sDataColumn & lWorksheetRecordCount + 2).Formula > " "
            lWorksheetRecordCount = lWorksheetRecordCount + lWorksheetRecordCountScaler
        Wend

        'To the beginning of the previous bite, count 1/10th of the scale from there
        lWorksheetRecordCount = lWorksheetRecordCount - lWorksheetRecordCountScaler
        lWorksheetRecordCountScaler = lWorksheetRecordCountScaler / 10

    Wend

    lWorksheetRecordCount = lWorksheetRecordCount + 1   'Final answer

回答by Brigham Ray

This function should do the trick if you want to specify a particular sheet. I took the solution from user6432984 and modified it to not throw any errors. I am using Excel 2016 so it may not work for older versions:

如果你想指定一个特定的工作表，这个函数应该可以解决问题。我从 user6432984 那里得到了解决方案并修改了它以不抛出任何错误。我使用的是 Excel 2016，所以它可能不适用于旧版本：

Function findLastRow(ByVal inputSheet As Worksheet) As Integer
    findLastRow = inputSheet.cellS(inputSheet.Rows.Count, 1).End(xlUp).Row
End Function

This is the code to run if you are already working in the sheet you want to find the last row of:

如果您已经在要查找最后一行的工作表中工作，则这是要运行的代码：

Dim lastRow as Integer
lastRow = cellS(Rows.Count, 1).End(xlUp).Row

回答by Antanas ?ukevi?ius

This gives you last used row in a specified column.

这为您提供了指定列中最后使用的行。

Optionally you can specify worksheet, else it will takes active sheet.

您可以选择指定工作表，否则它将采用活动表。

Function shtRowCount(colm As Integer, Optional ws As Worksheet) As Long

    If ws Is Nothing Then Set ws = ActiveSheet

    If ws.Cells(Rows.Count, colm) <> "" Then
        shtRowCount = ws.Cells(Rows.Count, colm).Row
        Exit Function
    End If

    shtRowCount = ws.Cells(Rows.Count, colm).Row

    If shtRowCount = 1 Then
        If ws.Cells(1, colm) = "" Then
            shtRowCount = 0
        Else
            shtRowCount = 1
        End If
    End If

End Function

Sub test()

    Dim lgLastRow As Long
    lgLastRow = shtRowCount(2) 'Column B

End Sub

回答by Juan Cappuccino

How is this?

这怎么样？

dim rownum as integer
dim colnum as integer
dim lstrow as integer
dim lstcol as integer
dim r as range

'finds the last row

lastrow = ActiveSheet.UsedRange.Rows.Count

'finds the last column

lastcol = ActiveSheet.UsedRange.Columns.Count

'sets the range

set r = range(cells(rownum,colnum), cells(lstrow,lstcol))

回答by Matthew Goheen

This is the best way I've seen to find the last cell.

这是我见过的找到最后一个单元格的最好方法。

MsgBox ActiveSheet.UsedRage.SpecialCells(xlCellTypeLastCell).Row

One of the disadvantages to using this is that it's not always accurate. If you use it then delete the last few rows and use it again, it does not always update. Saving your workbook before using this seems to force it to update though.

使用它的缺点之一是它并不总是准确的。如果您使用它然后删除最后几行并再次使用它，它并不总是更新。在使用之前保存您的工作簿似乎会强制它更新。

Using the next bit of code after updating the table (or refreshing the query that feeds the table) forces everything to update before finding the last row. But, it's been reported that it makes excel crash. Either way, calling this before trying to find the last row will ensure the table has finished updating first.

在更新表（或刷新提供表的查询）后使用下一段代码强制所有内容在找到最后一行之前更新。但是，据报道它会使excel崩溃。无论哪种方式，在尝试查找最后一行之前调用它都将确保表首先完成更新。

Application.CalculateUntilAsyncQueriesDone

Another way to get the last row for any given column, if you don't mind the overhead.

如果您不介意开销，则另一种获取任何给定列的最后一行的方法。

Function GetLastRow(col, row)
    ' col and row are where we will start.
    ' We will find the last row for the given column.
    Do Until ActiveSheet.Cells(row, col) = ""
        row = row + 1
    Loop
    GetLastRow = row
End Function