找出给定行中最后使用的列 - Excel VBA

Find out the last used column in a given row - Excel VBA

提问by Learner_VBA

I have to get the last used cell in a particular row.

我必须获取特定行中最后使用的单元格。

For example: If I choose A1 the last used column should return 3.

例如：如果我选择 A1，最后使用的列应该返回 3。

Row/LastUsedColumn A1/ 3 A2 / 4 A3 / 3 A4 / 3 A5 / 1 A6 / 3 A7 / 4

行/LastUsedColumn A1/ 3 A2 / 4 A3 / 3 A4 / 3 A5 / 1 A6 / 3 A7 / 4

回答by David Zemens

The function Dan posts from Chip Pearson is super versatile. But probably overkill for most applications. If you're looking for something simpler:

Chip Pearson 的 Dan 发布的函数非常通用。但对于大多数应用程序来说可能有点矫枉过正。如果您正在寻找更简单的东西：

Sub GetLastColumn()
Dim colRange As Range
Dim rowNum As Long

rowNum = InputBox("Enter the row number")

Set colRange = Cells(rowNum, Columns.Count).End(xlToLeft)
lastCol = colRange.Column

MsgBox colRange.Address

End Sub

回答by Dan

The following function has been copied from http://www.cpearson.com/excel/LastCell.aspx

以下函数已复制自http://www.cpearson.com/excel/LastCell.aspx

Public Function GetLastCell(InRange As Range, SearchOrder As XlSearchOrder, _
                        Optional ProhibitEmptyFormula As Boolean = False) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLastCell
' By Chip Pearson, [email protected], www.cpearson.com
'
' This returns the last used cell in a worksheet or range. If InRange
' is a single cell, the last cell of the entire worksheet if found. If
' InRange contains two or more cells, the last cell in that range is
' returned.
' If SearchOrder is xlByRows (= 1), the last cell is the last
' (right-most) non-blank cell on the last row of data in the
' worksheet's UsedRange. If SearchOrder is xlByColumns
' (= 2), the last cell is the last (bottom-most) non-blank cell in the
' last (right-most) column of the worksheet's UsedRange. If SearchOrder
' is xlByColumns + xlByRows (= 3), the last cell is the intersection of
' the last row and the last column. Note that this cell may not contain
' any value.
' If SearchOrder is anything other than xlByRows, xlByColumns, or
' xlByRows+xlByColumns, an error 5 is raised.
'
' ProhibitEmptyFormula indicates how to handle the case in which the
' last cell is a formula that evaluates to an empty string. If this setting
' is omitted for False, the last cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the last cell
' must be either a static value or a formula that evaluates to a non-empty
' string. The default is False, allowing the last cell to be a formula
' that evaluates to an empty string.
'''''''''''''''''''''''''
' Example:
'       a   b   c
'               d   e
'       f   g
'
' If SearchOrder is xlByRows, the last cell is 'g'. If SearchOrder is
' xlByColumns, the last cell is 'e'. If SearchOrder is xlByRows+xlByColumns,
' the last cell is the intersection of the row containing 'g' and the column
' containing 'e'. This cell has no value in this example.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim R As Range
Dim LastCell As Range
Dim LastR As Range
Dim LastC As Range
Dim SearchRange As Range
Dim LookIn As XlFindLookIn
Dim RR As Range

Set WS = InRange.Worksheet

If ProhibitEmptyFormula = False Then
    LookIn = xlFormulas
Else
    LookIn = xlValues
End If

Select Case SearchOrder
    Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
            XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
        ' OK
    Case Else
        Err.Raise 5
        Exit Function
End Select

With WS
    If InRange.Cells.Count = 1 Then
        Set RR = .UsedRange
    Else
       Set RR = InRange
    End If
    Set R = RR(RR.Cells.Count)

    If SearchOrder = xlByColumns Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByRows Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByColumns + xlByRows Then
        Set LastC = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastR = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastCell = Application.Intersect(LastR.EntireRow, LastC.EntireColumn)
    Else
        Err.Raise 5
        Exit Function
    End If
End With

Set GetLastCell = LastCell

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END CODE GetLastCell
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''