Excel VBA -- Find last row, column or last cell.

demo · 发表于 2012-8-24 10:58:56

本帖最后由 demo 于 2012-8-25 03:17 编辑

Last used cell in one row or one column

      The macro's give you the row or column number of the last cell with data in one row or one column.
      Note: This code below will not work correct if the last row  with data is hidden.

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow
End Sub
Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox LastCol
End Sub</pre>

复制代码

Last used cell in a worksheet

      Possible problems with xlCellTypeLastCell and UsedRange are:

      The last cell will only re-set when you save (or save/close/reopen the file).
      If cell formatting is changed it will not reset the last cell, clearing the data is not
      enough, you must delete the rows or columns then,
      See: http://www.contextures.com/xlfaqApp.html#Unused

      So when using VBA you cannot rely on this two macros if you want the last row
      or column with data on your worksheet.


      Last Row :

Sub xlCellTypeLastCell_Example_Row()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
MsgBox LastRow
End Sub
Sub UsedRange_Example_Row()
Dim LastRow As Long
With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With
MsgBox LastRow
End Sub

复制代码

Last Column :

Sub xlCellTypeLastCell_Example_Column()
Dim LastColumn As Long
With ActiveSheet
LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
End With
MsgBox LastColumn
End Sub
Sub UsedRange_Example_Column()
Dim LastColumn As Long
With ActiveSheet.UsedRange
LastColumn = .Columns(.Columns.Count).Column
End With
MsgBox LastColumn
End Sub

复制代码

Use the "Last" function to Find the last row, column or cell in range or worksheet


      In the example macros we call the function Last and this function have two arguments

      Argument 1 can be 1, 2 or 3
      1 = last row
      2 = last column
      3 = last cell
      Argument 2 is the range where you want to search in

      Copy the example macros together with the function "Last" in a normal module of your workbook.

Sub LastRow_Example()
Dim LastRow As Long
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last row
LastRow = Last(1, rng)
' After the last row with data change the value of the cell in Column A
rng.Parent.Cells(LastRow + 1, 1).Value = "Hi there"
End Sub
Sub LastColumn_Example()
Dim LastCol As Long
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Or use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last column
LastCol = Last(2, rng)
' After the last column with data change the value of the cell in row 1
rng.Parent.Cells(1, LastCol + 1).Value = "Hi there"
End Sub
Sub LastCell_Example()
Dim LastCell As String
Dim rng As Range
' Use all cells on the sheet
Set rng = Sheets("Sheet1").Cells
'Or use a range on the sheet
'Set rng = Sheets("Sheet1").Range("A1:D30")
' Find the last celll
LastCell = Last(3, rng)
' Select from A1 till the last cell in Rng
With rng.Parent
.Select
.Range("A1", LastCell).Select
End With
End Sub

复制代码

'This is the function we use in the macro's above

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
Dim lrw As Long
Dim lcol As Long
Select Case choice
Case 1:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
On Error Resume Next
lcol = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
On Error Resume Next
Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
If Err.Number > 0 Then
Last = rng.Cells(1).Address(False, False)
Err.Clear
End If
On Error GoTo 0
End Select
End Function

复制代码

Caveats:

      1: Tom Ogilvy posted this in the newsgroup
      Find does not find a cell containing the Null string "" entered when
      you do Edit=>Copy and then Edit=>

aste Special, Values for a cell containing
      a formula like =IF(A1="","",A1*1.19), which may or may not be desirable (end(xlup) does).

      2 : Find not see cell formatting but only data, if this is important for you see the
      xlCellTypeLastCell and UsedRange section of this page to find the last row or column.

      3: If you use merged cells (Please do not use merged cells) maybe you get unexpected results.
      It will give you the column number of the first cell and not the last cell in the merged cells.


http://www.rondebruin.nl/last.htm

账号		自动登录	找回密码
密码			注册