在Excel VBA中查找最后使用的单元格

我正在寻找一种模拟 CTRL+Shift+End 的方法，所以 dotNET 解决方案非常好，但是对于我的 Excel 2010，如果我想避免错误，就需要添加一个 set：

Function GetLastCell(sh As Worksheet) As Range
  Set GetLastCell = sh.Cells(1, 1).SpecialCells(xlLastCell)
End Function

并且如何自行检查：

Sub test()
  Dim ws As Worksheet, r As Range
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set r = GetLastCell(ws)
  MsgBox r.Column & "-" & r.Row
End Sub

Sub lastRow()

    Dim i As Long
        i = Cells(Rows.Count, 1).End(xlUp).Row
            MsgBox i

End Sub

sub LastRow()

'Paste & for better understanding of the working use F8 Key to run the code .

dim WS as worksheet
dim i as long

set ws = thisworkbook("SheetName")

ws.activate

ws.range("a1").select

ws.range("a1048576").select

activecell.end(xlup).select

i= activecell.row

msgbox "My Last Row Is " & i

End sub

最后一列：

Function lastCol(Optional wsName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastCol = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

最后一行：

Function lastRow(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    lastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

对于此 OP 的情况，获取列 E 中最后一行的方法如下：

Debug.Print lastRow(columnToCheck:=Range("E4:E48").Column)

计算包含空白行的最后一行：

我们可以使用众所周知的 Excel 公式，它可以在不涉及 VBA 的情况下给出工作表中最后一行的位置，公式为=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)

为了将其转换为 VBA 并且不在 Excel 中写任何内容，可以考虑使用后者函数的参数，类似这样：

Public Function LastRowWithHidden(Optional wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet

    If wsName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(wsName)
    End If

    Dim letters As String
    letters = ColLettersGenerator(columnToCheck)
    LastRowWithHidden = ws.Evaluate("=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(" & letters & "))),ROW(" & letters & " )),0)")

End Function

Function ColLettersGenerator(col As Long) As String

    Dim result As Variant
    result = Split(Cells(1, col).Address(True, False), "$")
    ColLettersGenerator = result(0) & ":" & result(0)

End Function

在常规范围或表格（ListObject）中找到最后一行

1. 如果范围是常规范围或表格（List Object），则查找最后一行需要使用不同的方法。
2. 在表格中查找最后一行需要指定其他参数（表格名称，相对于第一个表格列的列位置）。

我创建了这个通用函数来查找最后一行，无论范围类型如何。只需给它任何单元格引用，它就会返回最后一行。 不必费心去了解范围特性，特别是如果您的范围有时是常规范围，有时是ListObject。 在表格上使用常规范围方法可能会返回错误的结果。 当然，您可以提前计划并每次使用正确的方法，但如果您可以利用通用函数，为什么要麻烦呢？

Sub RunMyLastRow()
Dim Result As Long
Result = MyLastRow(Worksheets(1).Range("A1"))
End Sub

    Function MyLastRow(RefrenceRange As Range) As Long
    Dim WS As Worksheet
    Dim TableName As String
    Dim ColNumber As Long
    Dim LastRow As Long
    Dim FirstColumnTable As Long
    Dim ColNumberTable As Long
    Set WS = RefrenceRange.Worksheet
    TableName = GetTableName(RefrenceRange)
    ColNumber = RefrenceRange.Column
    
    ''If the table (ListObject) does not start in column "A" we need to calculate the 
    ''first Column table and how many Columns from its beginning the Column is located.
    If TableName <> vbNullString Then
     FirstColumnTable = WS.ListObjects(TableName).ListColumns(1).Range.Column
     ColNumberTable = ColNumber - FirstColumnTable + 1
    End If 

    If TableName = vbNullString Then
    LastRow = WS.Cells(WS.Rows.Count, ColNumber).End(xlUp).Row
    Else
    LastRow = WS.ListObjects(TableName).ListColumns(ColNumberTable).Range.Find( _
               What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    MyLastRow = LastRow
    End Function
    

    ''Get Table Name by Cell Range
    Function GetTableName(RefrenceRange As Range) As String
        If RefrenceRange.ListObject Is Nothing Then
            GetTableName = vbNullString
        Else
            GetTableName = RefrenceRange.ListObject.Name
        End If
    End Function