Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA

182
james 2012-06-24 02:20.

Khi tôi muốn tìm giá trị ô được sử dụng cuối cùng, tôi sử dụng:

Dim LastRow As Long

LastRow = Range("E4:E48").End(xlDown).Row

Debug.Print LastRow

Tôi nhận được kết quả sai khi đặt một phần tử vào một ô. Nhưng khi tôi đặt nhiều hơn một giá trị vào ô, kết quả đầu ra là chính xác. Lý do đằng sau điều này là gì?

13 answers

314
Siddharth Rout 2012-06-24 03:33.

LƯU Ý : Tôi định đặt đây là "bài đăng một cửa", nơi bạn có thể sử dụng Correctcách để tìm hàng cuối cùng. Điều này cũng sẽ bao gồm các phương pháp hay nhất để làm theo khi tìm hàng cuối cùng. Và do đó tôi sẽ tiếp tục cập nhật nó bất cứ khi nào tôi bắt gặp một kịch bản / thông tin mới.


Những cách không đáng tin cậy để tìm hàng cuối cùng

Một số cách phổ biến nhất để tìm hàng cuối cùng rất không đáng tin cậy và do đó không bao giờ được sử dụng.

  1. Đã sử dụng
  2. xlDown
  3. CountA

UsedRangenên KHÔNG BAO GIỜ được sử dụng để tìm ra ô cuối cùng trong đó có dữ liệu. Nó rất không đáng tin cậy. Hãy thử thử nghiệm này.

Nhập nội dung nào đó vào ô A5. Bây giờ khi bạn tính toán hàng cuối cùng với bất kỳ phương pháp nào được đưa ra bên dưới, nó sẽ cho bạn 5. Bây giờ hãy tô màu A10đỏ cho ô . Nếu bây giờ bạn sử dụng bất kỳ mã nào dưới đây, bạn sẽ vẫn nhận được 5. Nếu bạn sử dụng Usedrange.Rows.Countbạn nhận được gì? Nó sẽ không phải là 5.

Đây là một kịch bản để hiển thị cách UsedRangehoạt động.

xlDown cũng không đáng tin cậy.

Hãy xem xét mã này

lastrow = Range("A1").End(xlDown).Row

Điều gì sẽ xảy ra nếu chỉ có một ô ( A1) có dữ liệu? Bạn sẽ đến hàng cuối cùng trong trang tính! Nó giống như việc chọn ô A1, sau đó nhấn Endphím và sau đó nhấn Down Arrowphím. Điều này cũng sẽ cung cấp cho bạn kết quả không đáng tin cậy nếu có các ô trống trong một phạm vi.

CountA cũng không đáng tin cậy vì nó sẽ cung cấp cho bạn kết quả không chính xác nếu có các ô trống ở giữa.

Và do đó ta nên tránh việc sử dụng UsedRange, xlDownCountAđể tìm ô cuối cùng.


Tìm hàng cuối cùng trong một cột

Để tìm Hàng cuối cùng trong Ô E, hãy sử dụng

With Sheets("Sheet1")
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

Nếu bạn nhận thấy rằng chúng tôi có một .trước đây Rows.Count. Chúng tôi thường chọn bỏ qua điều đó. Xem câu hỏi NÀY về lỗi có thể xảy ra mà bạn có thể gặp phải. Tôi luôn khuyên sử dụng .trước Rows.CountColumns.Count. Câu hỏi đó là một tình huống cổ điển trong đó mã sẽ không thành công vì Rows.Counttrả về 65536cho Excel 2003 trở về trước và 1048576Excel 2007 trở lên. Tương tự Columns.Counttrả về 25616384, tương ứng.

Thực tế là Excel 2007+ có 1048576hàng ở trên cũng nhấn mạnh vào thực tế là chúng ta nên luôn khai báo biến sẽ giữ giá trị hàng Longthay vì Integernếu không bạn sẽ gặp Overflowlỗi.

Lưu ý rằng cách tiếp cận này sẽ bỏ qua bất kỳ hàng ẩn nào. Nhìn lại ảnh chụp màn hình của tôi ở trên cho cột A , nếu hàng 8 bị ẩn, phương pháp này sẽ trả về 5thay vì 8.


Tìm hàng cuối cùng trong một trang tính

Để tìm Effectivehàng cuối cùng trong trang tính, hãy sử dụng mục này. Lưu ý việc sử dụng Application.WorksheetFunction.CountA(.Cells). Điều này là bắt buộc vì nếu không có ô nào có dữ liệu trong trang tính thì .Findsẽ cung cấp cho bạnRun Time Error 91: Object Variable or With block variable not set

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If
End With

Tìm hàng cuối cùng trong bảng (ListObject)

Các nguyên tắc tương tự cũng được áp dụng, chẳng hạn để lấy hàng cuối cùng trong cột thứ ba của bảng:

Sub FindLastRowInExcelTableColAandB()
Dim lastRow As Long
Dim ws As Worksheet, tbl as ListObject
Set ws = Sheets("Sheet1")  'Modify as needed
'Assuming the name of the table is "Table1", modify as needed
Set tbl = ws.ListObjects("Table1")

With tbl.ListColumns(3).Range
    lastrow = .Find(What:="*", _
                After:=.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
End With

End Sub
35

Lưu ý: câu trả lời này được thúc đẩy bởi Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA . Mục đích của UsedRangekhác với những gì được đề cập trong câu trả lời ở trên.

Đối với cách chính xác để tìm ô được sử dụng cuối cùng, trước tiên người ta phải quyết định những gì được coi là đã sử dụng , sau đó chọn một phương pháp phù hợp . Tôi quan niệm ít nhất ba nghĩa:

  1. Đã sử dụng = không trống, tức là có dữ liệu .

  2. Used = "... đang được sử dụng, nghĩa là phần chứa dữ liệu hoặc định dạng ." Theo tài liệu chính thức , đây là tiêu chí được Excel sử dụng tại thời điểm lưu. Xem thêm tài liệu chính thức này . Nếu một người không nhận thức được điều này, tiêu chí có thể tạo ra kết quả không mong muốn, nhưng nó cũng có thể bị cố ý khai thác (ít thường xuyên hơn, chắc chắn), ví dụ, để làm nổi bật hoặc in các vùng cụ thể, mà cuối cùng có thể không có dữ liệu. Và, tất nhiên, nó được mong muốn như một tiêu chí cho phạm vi sử dụng khi lưu sổ làm việc, vì tránh làm mất một phần công việc của một người.

  3. Used = "... đang sử dụng, nghĩa là phần chứa dữ liệu hoặc định dạng " hoặc định dạng có điều kiện. Tương tự như 2., nhưng cũng bao gồm các ô là mục tiêu cho bất kỳ quy tắc Định dạng có Điều kiện nào.

Cách tìm ô được sử dụng cuối cùng tùy thuộc vào những gì bạn muốn (tiêu chí của bạn) .

Đối với tiêu chí 1, tôi đề nghị đọc câu trả lời này . Lưu ý rằng UsedRangeđược trích dẫn là không đáng tin cậy. Tôi nghĩ rằng điều đó gây hiểu lầm (tức là, "không công bằng" UsedRange), UsedRangeđơn giản là không có nghĩa là để báo cáo ô cuối cùng chứa dữ liệu. Vì vậy, nó không nên được sử dụng trong trường hợp này, như được chỉ ra trong câu trả lời đó. Xem thêm Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA .

Đối với tiêu chí 2, UsedRangelà lựa chọn đáng tin cậy nhất , so với các tùy chọn khác cũng được thiết kế cho mục đích này. Nó thậm chí còn làm cho việc lưu sổ làm việc không cần thiết để đảm bảo rằng ô cuối cùng được cập nhật. Ctrl+ Endsẽ chuyển đến một ô sai trước khi lưu (“Ô cuối cùng không được đặt lại cho đến khi bạn lưu trang tính”, từ http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10% 29.aspx . Đó là một tham chiếu cũ, nhưng về mặt này hợp lệ).

Đối với tiêu chí 3, tôi không biết bất kỳ phương pháp tích hợp nào . Tiêu chí 2 không tính đến Định dạng có Điều kiện. Một có thể có các ô được định dạng, dựa trên công thức, không được phát hiện bởi UsedRangehoặc Ctrl+ End. Trong hình, ô cuối cùng là B3, vì định dạng đã được áp dụng rõ ràng cho nó. Các ô B6: D7 có định dạng bắt nguồn từ quy tắc Định dạng có Điều kiện và điều này không được phát hiện ngay cả bởi UsedRange. Tính toán cho điều này sẽ yêu cầu một số lập trình VBA.


Đối với câu hỏi cụ thể của bạn : Lý do đằng sau điều này là gì?

Mã của bạn sử dụng ô đầu tiên trong phạm vi E4: E48 của bạn làm tấm bạt lò xo, để nhảy xuống với End(xlDown).

Đầu ra "sai" sẽ nhận được nếu không có ô không trống nào trong phạm vi của bạn ngoài phạm vi có lẽ là ô đầu tiên. Sau đó, bạn đang nhảy trong bóng tối , tức là, xuống trang tính (bạn nên lưu ý sự khác biệt giữa chuỗi trốngchuỗi trống !).

Lưu ý rằng:

  1. Nếu phạm vi của bạn chứa các ô không trống không liền nhau, thì nó cũng sẽ cho kết quả sai.

  2. Nếu chỉ có một ô không trống, nhưng không phải là ô đầu tiên, mã của bạn sẽ vẫn cho bạn kết quả chính xác.

21
ZygD 2015-12-24 11:55.

Tôi đã tạo hàm một cửa này để xác định hàng, cột và ô cuối cùng, có thể là dữ liệu, các ô được định dạng (nhóm / nhận xét / ẩn) hoặc định dạng có điều kiện .

Sub LastCellMsg()
    Dim strResult As String
    Dim lngDataRow As Long
    Dim lngDataCol As Long
    Dim strDataCell As String
    Dim strDataFormatRow As String
    Dim lngDataFormatCol As Long
    Dim strDataFormatCell As String
    Dim oFormatCond As FormatCondition
    Dim lngTempRow As Long
    Dim lngTempCol As Long
    Dim lngCFRow As Long
    Dim lngCFCol As Long
    Dim strCFCell As String
    Dim lngOverallRow As Long
    Dim lngOverallCol As Long
    Dim strOverallCell As String

    With ActiveSheet

        If .ListObjects.Count > 0 Then
            MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
            Exit Sub
        End If

        strResult = "Workbook name: " & .Parent.Name & vbCrLf
        strResult = strResult & "Sheet name: " & .Name & vbCrLf

        'DATA:
        'last data row
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataRow = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByRows, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Row
        Else
            lngDataRow = 1
        End If
        'strResult = strResult & "Last data row: " & lngDataRow & vbCrLf

        'last data column
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lngDataCol = .Cells.Find(What:="*", _
             After:=.Range("A1"), _
             Lookat:=xlPart, _
             LookIn:=xlFormulas, _
             SearchOrder:=xlByColumns, _
             SearchDirection:=xlPrevious, _
             MatchCase:=False).Column
        Else
            lngDataCol = 1
        End If
        'strResult = strResult & "Last data column: " & lngDataCol & vbCrLf

        'last data cell
        strDataCell = Replace(Cells(lngDataRow, lngDataCol).Address, "$", vbNullString)
        strResult = strResult & "Last data cell: " & strDataCell & vbCrLf

        'FORMATS:
        'last data/formatted/grouped/commented/hidden row
        strDataFormatRow = StrReverse(Split(StrReverse(.UsedRange.Address), "$")(0))
        'strResult = strResult & "Last data/formatted row: " & strDataFormatRow & vbCrLf

        'last data/formatted/grouped/commented/hidden column
        lngDataFormatCol = Range(StrReverse(Split(StrReverse(.UsedRange.Address), "$")(1)) & "1").Column
        'strResult = strResult & "Last data/formatted column: " & lngDataFormatCol & vbCrLf

        'last data/formatted/grouped/commented/hidden cell
        strDataFormatCell = Replace(Cells(strDataFormatRow, lngDataFormatCol).Address, "$", vbNullString)
        strResult = strResult & "Last data/formatted cell: " & strDataFormatCell & vbCrLf

        'CONDITIONAL FORMATS:
        For Each oFormatCond In .Cells.FormatConditions

            'last conditionally-formatted row
            lngTempRow = CLng(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(0)))
            If lngTempRow > lngCFRow Then lngCFRow = lngTempRow

            'last conditionally-formatted column
            lngTempCol = Range(StrReverse(Split(StrReverse(oFormatCond.AppliesTo.Address), "$")(1)) & "1").Column
            If lngTempCol > lngCFCol Then lngCFCol = lngTempCol
        Next
        'no results are returned for Conditional Format if there is no such
        If lngCFRow <> 0 Then
            'strResult = strResult & "Last cond-formatted row: " & lngCFRow & vbCrLf
            'strResult = strResult & "Last cond-formatted column: " & lngCFCol & vbCrLf

            'last conditionally-formatted cell
            strCFCell = Replace(Cells(lngCFRow, lngCFCol).Address, "$", vbNullString)
            strResult = strResult & "Last cond-formatted cell: " & strCFCell & vbCrLf
        End If

        'OVERALL:
        lngOverallRow = Application.WorksheetFunction.Max(lngDataRow, strDataFormatRow, lngCFRow)
        'strResult = strResult & "Last overall row: " & lngOverallRow & vbCrLf
        lngOverallCol = Application.WorksheetFunction.Max(lngDataCol, lngDataFormatCol, lngCFCol)
        'strResult = strResult & "Last overall column: " & lngOverallCol & vbCrLf
        strOverallCell = Replace(.Cells(lngOverallRow, lngOverallCol).Address, "$", vbNullString)
        strResult = strResult & "Last overall cell: " & strOverallCell & vbCrLf

        MsgBox strResult
        Debug.Print strResult

    End With

End Sub

Kết quả như sau:

Để có kết quả chi tiết hơn, một số dòng trong mã có thể được bỏ ghi chú:

Một hạn chế tồn tại - nếu có bảng trong trang tính, kết quả có thể trở nên không đáng tin cậy, vì vậy tôi quyết định tránh chạy mã trong trường hợp này:

If .ListObjects.Count > 0 Then
    MsgBox "Cannot return reliable results, as there is at least one table in the worksheet."
    Exit Sub
End If
11
Bishop 2015-01-03 08:58.

Một lưu ý quan trọng cần ghi nhớ khi sử dụng dung dịch ...

LastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

... là để đảm bảo rằng LastRowbiến của bạn thuộc Longloại:

Dim LastRow as Long

Nếu không, bạn sẽ gặp lỗi OVERFLOW trong một số tình huống nhất định trong sổ làm việc .XLSX

Đây là chức năng được đóng gói của tôi mà tôi sử dụng để sử dụng mã khác nhau.

Private Function FindLastRow(ws As Worksheet) As Long
    ' --------------------------------------------------------------------------------
    ' Find the last used Row on a Worksheet
    ' --------------------------------------------------------------------------------
    If WorksheetFunction.CountA(ws.Cells) > 0 Then
        ' Search for any entry, by searching backwards by Rows.
        FindLastRow = ws.Cells.Find(What:="*", After:=ws.range("a1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
End Function
8
no comprende 2014-11-06 05:24.

Tôi sẽ thêm vào câu trả lời do Siddarth Rout đưa ra để nói rằng lệnh gọi CountA có thể được bỏ qua bằng cách yêu cầu Tìm trả về đối tượng Phạm vi, thay vì số hàng, sau đó kiểm tra đối tượng Phạm vi được trả về để xem nó có gì không (trang tính trống) .

Ngoài ra, tôi sẽ có phiên bản của bất kỳ quy trình LastRow nào của tôi trả về số 0 cho một trang tính trống, sau đó tôi có thể biết nó trống.

8
dotNET 2015-04-28 05:21.

Tôi tự hỏi rằng chưa ai đề cập đến điều này, Nhưng cách dễ nhất để lấy ô được sử dụng cuối cùng là:

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

Điều này về cơ bản trả về cùng một ô mà bạn nhận được bằng dấu Ctrl+ Endsau khi chọn Ô A1.

Lưu ý: Excel theo dõi ô dưới cùng bên phải nhất từng được sử dụng trong trang tính. Vì vậy, nếu ví dụ bạn nhập một cái gì đó vào B3 và một cái gì đó khác trong H8 và sau đó xóa nội dung của H8 , nhấn Ctrl+ Endsẽ vẫn đưa bạn đến ô H8 . Hàm trên sẽ có cùng một hành vi.

8
Nickolay 2018-04-23 13:01.

Vì câu hỏi ban đầu là về các vấn đề với việc tìm ô cuối cùng, trong câu trả lời này, tôi sẽ liệt kê các cách khác nhau mà bạn có thể nhận được kết quả bất ngờ ; xem câu trả lời của tôi cho "Làm cách nào để tìm hàng cuối cùng chứa dữ liệu trong trang tính Excel bằng macro?" để tôi giải quyết vấn đề này.

Tôi sẽ bắt đầu bằng cách mở rộng Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBALỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA , thêm chi tiết hơn nữa:

[...] trước tiên người ta phải quyết định những gì được coi là đã sử dụng. Tôi thấy có ít nhất 6 nghĩa. Ô có:

  • 1) dữ liệu, tức là, một công thức, có thể dẫn đến một giá trị trống;
  • 2) một giá trị, tức là, một công thức không trống hoặc một hằng số;
  • 3) định dạng;
  • 4) định dạng có điều kiện;
  • 5) một hình dạng (bao gồm cả Nhận xét) chồng lên ô;
  • 6) tham gia vào một Bảng (Đối tượng Danh sách).

Bạn muốn thử nghiệm sự kết hợp nào? Một số (chẳng hạn như Bảng) có thể khó kiểm tra hơn và một số có thể hiếm (chẳng hạn như hình dạng nằm ngoài phạm vi dữ liệu), nhưng một số khác có thể thay đổi tùy theo tình huống (ví dụ: công thức có giá trị trống).

Những điều khác bạn có thể muốn xem xét:

  • A) Có thể có các hàng ẩn (ví dụ: bộ lọc tự động), ô trống hoặc hàng trống không?
  • B) Loại biểu diễn nào được chấp nhận?
  • C) Macro VBA có thể ảnh hưởng đến sổ làm việc hoặc cài đặt ứng dụng theo bất kỳ cách nào không?

Với ý nghĩ đó, hãy xem những cách phổ biến để lấy "ô cuối cùng" có thể tạo ra kết quả bất ngờ như thế nào:

  • Các .End(xlDown)mã từ câu hỏi sẽ phá vỡ một cách dễ dàng nhất (ví dụ với một tế bào không trống đơn hoặc khi có ô trống ở giữa ) vì những lý do giải thích trong Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA đây (tìm kiếm cho "xlDown cũng không kém phần đáng tin cậy." ) 👎
  • Bất kỳ giải pháp nào dựa trên Counting ( CountAhoặc Cells*.Count) hoặc .CurrentRegioncũng sẽ bị hỏng khi có các ô hoặc hàng trống 👎
  • Một giải pháp liên quan .End(xlUp)đến tìm kiếm ngược từ cuối cột, cũng giống như CTRL + LÊN, sẽ tìm kiếm dữ liệu (các công thức tạo ra giá trị trống được coi là "dữ liệu") trong các hàng hiển thị (vì vậy việc sử dụng nó với bộ lọc tự động được bật có thể tạo ra kết quả không chính xác ⚠️ ).

    Bạn phải cẩn thận để tránh những cạm bẫy tiêu chuẩn (để biết chi tiết, tôi sẽ tham khảo lại Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA ở đây, hãy tìm phần "Tìm hàng cuối cùng trong một cột" ), chẳng hạn như mã hóa cứng hàng cuối cùng ( Range("A65536").End(xlUp)) thay vì dựa dẫm sht.Rows.Count.

  • .SpecialCells(xlLastCell)tương đương với CTRL + END, trả về ô dưới cùng và ngoài cùng bên phải của "dải ô đã sử dụng", vì vậy tất cả các cảnh báo áp dụng cho việc dựa vào "dải ô đã sử dụng", cũng áp dụng cho phương pháp này. Ngoài ra, "phạm vi đã sử dụng" chỉ được đặt lại khi lưu sổ làm việc và khi truy cập worksheet.UsedRange, vì vậy xlLastCellcó thể tạo ra kết quả cũ st️ với các sửa đổi chưa được lưu (ví dụ: sau khi một số hàng đã bị xóa). Xem câu trả lời gần đó của dotNET .
  • sht.UsedRange(được mô tả chi tiết trong Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA tại đây) xem xét cả dữ liệu và định dạng (mặc dù không phải là định dạng có điều kiện) và đặt lại "phạm vi được sử dụng" của trang tính , có thể có hoặc không theo ý bạn.

    Lưu ý rằng một sai lầm phổ biến ️ là sử dụng .UsedRange.Rows.Count⚠️, trả về số hàng trong phạm vi đã sử dụng, không phải số hàng cuối cùng (chúng sẽ khác nhau nếu vài hàng đầu tiên trống), để biết chi tiết, hãy xem câu trả lời của newguy về cách tôi có thể tìm thấy hàng cuối cùng chứa dữ liệu trong trang tính Excel với macro?

  • .Findcho phép bạn tìm hàng cuối cùng với bất kỳ dữ liệu nào (bao gồm cả công thức) hoặc giá trị không trống trong bất kỳ cột nào . Bạn có thể chọn xem bạn có quan tâm đến công thức hoặc giá trị hay không, nhưng điều đáng chú ý là nó đặt lại giá trị mặc định trong hộp thoại Tìm của Excel ️️⚠️, điều này có thể gây nhầm lẫn cho người dùng của bạn. Nó cũng cần được sử dụng cẩn thận, xem Lỗi khi tìm ô được sử dụng gần đây nhất trong Excel với VBA tại đây (phần "Tìm Hàng Cuối cùng trong Trang tính" )
  • Các giải pháp rõ ràng hơn để kiểm tra từng Cells'trong một vòng lặp thường chậm hơn so với việc sử dụng lại một hàm Excel (mặc dù vẫn có thể hoạt động), nhưng cho phép bạn chỉ định chính xác những gì bạn muốn tìm. Xem giải pháp của tôi dựa trên UsedRangevà mảng VBA để tìm ô cuối cùng có dữ liệu trong cột đã cho - nó xử lý các hàng ẩn, bộ lọc, khoảng trống, không sửa đổi mặc định Tìm và khá hiệu quả.

Dù bạn chọn giải pháp nào, hãy cẩn thận

  • để sử dụng Longthay vì Integerlưu trữ số hàng (để tránh nhận được Overflowhơn 65 nghìn hàng) và
  • để luôn chỉ định trang tính bạn đang làm việc (tức là Dim ws As Worksheet ... ws.Range(...)thay vì Range(...))
  • khi sử dụng .Value(là a Variant) hãy tránh các kiểu ẩn .Value <> ""như thể chúng sẽ bị lỗi nếu ô chứa giá trị lỗi.
4
M-- 2017-05-09 11:48.

Tuy nhiên, câu hỏi này đang tìm cách tìm hàng cuối cùng bằng VBA, tôi nghĩ sẽ rất tốt nếu bao gồm một công thức mảng cho hàm trang tính vì nó được truy cập thường xuyên:

{=ADDRESS(MATCH(INDEX(D:D,MAX(IF(D:D<>"",ROW(D:D)-ROW(D1)+1)),1),D:D,0),COLUMN(D:D))}

Bạn cần nhập công thức không có dấu ngoặc và sau đó nhấn Shift+ Ctrl+ Enterđể biến nó thành công thức mảng.

Điều này sẽ cung cấp cho bạn địa chỉ của ô được sử dụng gần đây nhất trong cột D.

3
Ashwith Ullal 2015-10-01 19:09.
sub last_filled_cell()
msgbox range("A65536").end(xlup).row
end sub

Đây, A65536là ô cuối cùng trong Cột A, mã này đã được kiểm tra trên excel 2003.

2
J. Chomel 2017-05-18 05:23.

Tôi đang tìm cách bắt chước dấu CTRL+ Shift+ End, vì vậy giải pháp dotNET rất tuyệt, ngoại trừ với Excel 2010, tôi cần thêm dấu setnếu muốn tránh lỗi:

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

và cách kiểm tra điều này cho chính bạn:

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
1
PGSystemTester 2019-11-28 18:11.

Đây là hai xu của tôi.

IMHO rủi ro về một hàng ẩn với dữ liệu bị loại trừ là quá lớn để xlUpđược coi là câu trả lời Một cửa . Tôi đồng ý rằng nó đơn giản và sẽ hiệu quả NHẤT mọi thời điểm, nhưng nó có nguy cơ làm thấp hàng cuối cùng mà không có bất kỳ cảnh báo nào. Điều này có thể tạo ra thảm họa kết quả tại một số poinit cho ai đó nhảy trên Stack Overlow và đã tìm cách để "cách chắc chắn" để nắm bắt giá trị này.

Các Findphương pháp là hoàn hảo và tôi sẽ chấp nhận nó như là một One Stop trả lời . Tuy nhiên, hạn chế của việc thay đổi Findcài đặt có thể gây khó chịu, đặc biệt nếu đây là một phần của UDF.

Các câu trả lời khác được đăng là ổn, tuy nhiên độ phức tạp hơi quá mức. Vì vậy, đây là nỗ lực của tôi để tìm sự cân bằng về độ tin cậy, độ phức tạp tối thiểu và không sử dụng Find.

Function LastRowNumber(Optional rng As Range) As Long

If rng Is Nothing Then
    Set rng = ActiveSheet.UsedRange
Else
    Set rng = Intersect(rng.Parent.UsedRange, rng.EntireColumn)
    If rng Is Nothing Then
        LastRowNumber = 1
        Exit Function
    ElseIf isE = 0 Then
        LastRowNumber = 1
        Exit Function

    End If

End If

LastRowNumber = rng.Cells(rng.Rows.Count, 1).Row

Do While IsEmpty(Intersect(rng, _
    rng.Parent.Rows(LastRowNumber)))

    LastRowNumber = LastRowNumber - 1
Loop

End Function

Tại sao điều này là tốt:

  • Đơn giản hợp lý, không nhiều biến.
  • Cho phép nhiều cột.
  • Không sửa đổi Findcài đặt
  • Động nếu được sử dụng làm UDF với toàn bộ cột được chọn.

Tại sao điều này là xấu:

  • Với tập hợp dữ liệu rất lớn và khoảng cách lớn giữa phạm vi đã sử dụng và hàng cuối cùng trong các cột được chỉ định, điều này sẽ hoạt động chậm hơn, trong một số trường hợp hiếm hoi chậm hơn.

Tuy nhiên, tôi cho rằng Giải pháp một cửa có nhược điểm là làm rối findcài đặt hoặc hoạt động chậm hơn là một giải pháp tổng thể tốt hơn. Sau đó, người dùng có thể chỉnh sửa cài đặt của họ để cố gắng cải thiện, biết điều gì đang xảy ra với mã của họ. Việc sử dụng xLUpsẽ không cảnh báo những rủi ro tiềm ẩn và họ có thể tiếp tục cho những ai biết trong bao lâu nếu không biết mã của họ đã hoạt động không chính xác.

1
Vityata 2018-06-02 09:30.

Trong hơn 3 năm qua, đây là những hàm mà tôi đang sử dụng để tìm hàng cuối cùng và cột cuối cùng trên mỗi cột xác định (cho hàng) và hàng (cho cột):

Cột Cuối cùng:

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

Hàng cuối cùng:

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

Đối với trường hợp của OP, đây là cách để lấy hàng cuối cùng trong cột E:

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

Hàng Cuối cùng, đếm các hàng trống có dữ liệu:

Ở đây, chúng tôi có thể sử dụng các công thức Excel nổi tiếng , cung cấp cho chúng tôi hàng cuối cùng của trang tính trong Excel mà không liên quan đến VBA -=IFERROR(LOOKUP(2,1/(NOT(ISBLANK(A:A))),ROW(A:A)),0)

Để đặt điều này trong VBA và không viết bất kỳ thứ gì trong Excel, bằng cách sử dụng các tham số cho các hàm sau này, bạn có thể lưu ý điều gì đó như sau:

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
0
user85489 2016-06-29 22:07.
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

Related questions

MORE COOL STUFF

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett chia tay chồng sau 3 ngày bên nhau và vẫn kết hôn với anh ấy 25 năm sau

Cate Blanchett đã bất chấp những lời khuyên hẹn hò điển hình khi cô gặp chồng mình.

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Tại sao Michael Sheen là một diễn viên phi lợi nhuận

Michael Sheen là một diễn viên phi lợi nhuận nhưng chính xác thì điều đó có nghĩa là gì?

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Hallmark Star Colin Egglesfield Các món ăn gây xúc động mạnh đối với người hâm mộ tại RomaDrama Live! [Loại trừ]

Ngôi sao của Hallmark Colin Egglesfield chia sẻ về những cuộc gặp gỡ với người hâm mộ ly kỳ tại RomaDrama Live! cộng với chương trình INSPIRE của anh ấy tại đại hội.

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Tại sao bạn không thể phát trực tuyến 'chương trình truyền hình phía Bắc'

Bạn sẽ phải phủi sạch đầu đĩa Blu-ray hoặc DVD để xem tại sao Northern Exposure trở thành một trong những chương trình nổi tiếng nhất của thập niên 90.

Where in the World Are You? Take our GeoGuesser Quiz

Where in the World Are You? Take our GeoGuesser Quiz

The world is a huge place, yet some GeoGuessr players know locations in mere seconds. Are you one of GeoGuessr's gifted elite? Take our quiz to find out!

8 công dụng tuyệt vời của Baking Soda và Giấm

8 công dụng tuyệt vời của Baking Soda và Giấm

Bạn biết đấy, hai sản phẩm này là nguồn điện để làm sạch, riêng chúng. Nhưng cùng với nhau, chúng có một loạt công dụng hoàn toàn khác.

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Hạn hán, biến đổi khí hậu đe dọa tương lai của thủy điện Hoa Kỳ

Thủy điện rất cần thiết cho lưới điện của Hoa Kỳ, nhưng nó chỉ tạo ra năng lượng khi có nước di chuyển. Bao nhiêu nhà máy thủy điện có thể gặp nguy hiểm khi các hồ và sông cạn kiệt?

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Quyên góp tóc của bạn để giúp giữ nước sạch của chúng tôi

Tóc tỉa từ các tiệm và các khoản quyên góp cá nhân có thể được tái sử dụng như những tấm thảm thấm dầu và giúp bảo vệ môi trường.

Xem đoạn giới thiệu cho bộ phim chuyển thể đầy khói lửa, có sự góp mặt của ngôi sao Edward Norton từ phim Motherless Brooklyn của Jonathan Lethem

Xem đoạn giới thiệu cho bộ phim chuyển thể đầy khói lửa, có sự góp mặt của ngôi sao Edward Norton từ phim Motherless Brooklyn của Jonathan Lethem

Edward Norton đã muốn đưa cuốn tiểu thuyết Motherless Brooklyn của Jonathan Lethem's Joycean 1999 lên màn ảnh kể từ khi nó được xuất bản. Bây giờ, 20 năm sau, một đoạn giới thiệu đã xuất hiện cho câu chuyện sôi nổi, đưa câu chuyện của Lethem trở lại những năm 1950 với rất nhiều gương mặt quen thuộc.

Cách dễ dàng chọn không tham gia trọng tài ràng buộc thẻ Apple

Cách dễ dàng chọn không tham gia trọng tài ràng buộc thẻ Apple

Có thích thú khi sử dụng Thẻ Apple mới của bạn không? Trước khi bạn bắt đầu chi tiêu, có một nhiệm vụ bổ sung cần xem xét: chọn không tham gia trọng tài ràng buộc. Bạn sẽ phát hiện ra các điều khoản trọng tài ràng buộc trong nhiều thỏa thuận tài chính vì nó giúp ngăn các ngân hàng và đối tác kinh doanh của họ không phải ra tòa.

Những người chơi Fortnite World Cup không ghi bàn có cảm giác hài hước về điều đó

Những người chơi Fortnite World Cup không ghi bàn có cảm giác hài hước về điều đó

Điểm số tại vòng chung kết Fortnite World Cup Solo hôm nay là rất lớn, với người chiến thắng Bugha ghi được nhiều hơn 26 điểm so với người về thứ hai là Psalm. Nhưng không phải ai cũng có thể giành chiến thắng: Bốn cầu thủ ra về với 0 điểm, nhưng — ít nhất là trên Twitter — họ là những người thể thao tốt về điều đó.

Báo cáo: Cánh cửa tuyển sinh có thể đã mở cho các ứng viên UCLA có mối quan hệ có ảnh hưởng

Báo cáo: Cánh cửa tuyển sinh có thể đã mở cho các ứng viên UCLA có mối quan hệ có ảnh hưởng

Huấn luyện viên trưởng của bộ môn thể dục dụng cụ UCLA Valorie Kondos-Field theo dõi Katelyn Ohashi thi đấu thăng bằng trong trận gặp Stanford tại Pauley Pavilion vào ngày 10 tháng 3 năm 2019 ở Los Angeles, California. Vụ bê bối gian lận tuyển sinh đại học tiết lộ các chi tiết của một quá trình chính thức hóa để đưa những đứa trẻ thất bại của các gia đình giàu có và nổi tiếng vào các trường đại học đáng tin cậy và danh tiếng, sử dụng một "cửa phụ" đắt tiền cho các bậc cha mẹ mà sự giàu có của họ khiến họ không có cơ hội. chỉ cần tài trợ cho một cánh mới trong khuôn viên trường.

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Forced to Borrow Paris' 'I Love Paris' Sweatshirt After 'Airline Loses All [My] Luggage'

Nicky Hilton Rothschild's luggage got lost, but luckily she has an incredible closet to shop: Sister Paris Hilton's!

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa

Kate Middleton dành một ngày bên bờ nước ở London, cùng với Jennifer Lopez, Julianne Hough và hơn thế nữa. Từ Hollywood đến New York và mọi nơi ở giữa, hãy xem các ngôi sao yêu thích của bạn đang làm gì!

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

17 tuổi bị đâm chết trong khi 4 người khác bị thương trong một cuộc tấn công bằng dao trên sông Wisconsin

Các nhà điều tra đang xem xét liệu nhóm và nghi phạm có biết nhau trước vụ tấn công hay không

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Thanh thiếu niên, Gia đình Florida Hội đồng quản trị trường học về Luật 'Không nói đồng tính': 'Buộc chúng tôi tự kiểm duyệt'

Vụ kiện, nêu tên một số học khu, lập luận rằng dự luật "Không nói đồng tính" được ban hành gần đây của Florida "có hiệu quả im lặng và xóa bỏ học sinh và gia đình LGBTQ +"

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Hãy tưởng tượng tạo ra một chiến lược nội dung thực sự CHUYỂN ĐỔI. Nó có thể.

Vào năm 2021, tôi khuyến khích bạn suy nghĩ lại mọi thứ bạn biết về khách hàng mà bạn phục vụ và những câu chuyện bạn kể cho họ. Lùi lại.

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Sự mất mát của voi ma mút đã mở ra trái tim tôi để yêu

Vào ngày sinh nhật thứ 9 của Felix The Cat, tôi nhớ về một trong những mất mát lớn nhất trong cuộc đời trưởng thành của tôi - Sophie của tôi vào năm 2013. Tôi đã viết bài luận này và chia sẻ nó trên nền tảng này một thời gian ngắn vào năm 2013.

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Khi bạn không thể trở thành người mà Internet muốn bạn trở thành

Tôi ghét từ "tàu đắm". Mọi người cảm thấy thoải mái trong la bàn đạo đức của riêng mình, và khi làm như vậy, họ thấy mình vượt qua sự phán xét.

Tầm nhìn đám mây phi tập trung của DFINITY Blockchain

Lưu ý của người biên tập: Bạn đang xem tài liệu lỗi thời từ blog DFINITY đang được bảo quản cho mục đích lưu trữ.

Tầm nhìn đám mây phi tập trung của DFINITY Blockchain

Bài đăng này khám phá tầm nhìn về đám mây phi tập trung của nhóm DFINITY và cách nó liên quan đến các nhà cung cấp blockchain truyền thống và đám mây hiện có như Amazon Web Services. Các minh chứng về công nghệ DFINITY được áp dụng bởi một mạng lưới quy mô lớn sẽ được thực hiện vào mùa thu năm 2017, sau đó sẽ được gây quỹ Chính cho quỹ hỗ trợ phi lợi nhuận, với mạng “đám mây mở” dự kiến ​​sẽ ra mắt vào đầu mùa hè năm 2018 .

Language