Code xác định cell cuối cùng có dử liệu (2 người xem)

[ndu96081631]

Ví dụ trong cột A có dử liệu tùy ý và được bố trí không theo 1 quy luật nào:
- Có thể có 1 vài cell chứa giá trị (chưa biêt trước được kiểu dử liệu)
- Có thể có 1 vài cell chứa công thức (chưa biêt trước được kiểu dử liệu)
Xin hỏi ta sẽ dùng code gì để xác định chính xác vị trí của cell cuối cùng có dử liệu?
Tôi thấy chúng ta thường hay dùng:

Range(Cell cuối cùng).End(xlUp)

Nhưng trong trường hợp cell cuối cùng (là cell A65536 đối với Excel 2003 và là cell A1048576 đối với Excel2007) có dử liệu thì code này sai
Vậy giãi pháp nào là đúng trong mọi trường hợp của dử liệu và mọi Version của Excel?

 

[phamduylong]

Ví dụ trong cột A có dử liệu tùy ý và được bố trí không theo 1 quy luật nào:
- Có thể có 1 vài cell chứa giá trị (chưa biêt trước được kiểu dử liệu)
- Có thể có 1 vài cell chứa công thức (chưa biêt trước được kiểu dử liệu)
Xin hỏi ta sẽ dùng code gì để xác định chính xác vị trí của cell cuối cùng có dử liệu?
Tôi thấy chúng ta thường hay dùng:

Range(Cell cuối cùng).End(xlUp)

Nhưng trong trường hợp cell cuối cùng (là cell A65536 đối với Excel 2003 và là cell A1048576 đối với Excel2007) có dử liệu thì code này sai
Vậy giãi pháp nào là đúng trong mọi trường hợp của dử liệu và mọi Version của Excel?

Trong câu này Range(Cell cuối cùng).End(xlUp) không đúng với mọi Version của Excel vì bạn đã nhập 1 số cụ thể. Thay nó bằng Cells.Rows.Count để ra kết quả tổng số dòng có trong sheet.
Cells.Rows.Count =65536 (Excel 2003)
Cells.Rows.Count =1048576 (Excel 2007)

Code chọn ô cuối cùng của cột A (cột 1)

Cells(Cells.Rows.Count, 1).End(xlUp).Select

 

[ndu96081631]

Thì bổ sung lệnh điều kiện vào.

Sub thu()
On Error Resume Next
k = Range("A" & Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Address(0, 0)
If Err.Number <> 0 Then
k = "A" & Range("A1").SpecialCells(xlLastCell).Row
End If
MsgBox (k)
End Sub

Thân.

Trong code mà dùng SpecialCells(xlLastCell) là không ồn đâu. Vì code này tìm cell cuối đã dùng...
Trong trường hợp bạn nhập vào A65536 cái gì đó, xong lại xóa đi thì nó vẩn xem LastCell là A65536

Trong câu này Range(Cell cuối cùng).End(xlUp) không đúng với mọi Version của Excel vì bạn đã nhập 1 số cụ thể. Thay nó bằng Cells.Rows.Count để ra kết quả tổng số dòng có trong sheet.
Cells.Rows.Count =65536 (Excel 2003)
Cells.Rows.Count =1048576 (Excel 2007)

Code chọn ô cuối cùng của cột A (cột 1)

Cells(Cells.Rows.Count, 1).End(xlUp).Select

Nhưng code này vẩn không đúng khi cell cuối có dử liệu thầy ơi

 

[Po_Pikachu]

Code của thầy PhamDuyLong chạy được đấy chứ! Chỉ có trường hợp cel cuối cùng có dữ liệu cần phải xét lại thôi. Bác dùng thử xem có đúng ý không?

Sub thu() If Cells(Cells.Rows.Count, 1).Value  "" Then k = Cells(Cells.Rows.Count, 1).Address(0, 0) Else k = Cells(Cells.Rows.Count, 1).End(xlUp).Address(0, 0) End If MsgBox (k) End Sub

Thân.

 

[ndu96081631]

Code của thầy PhamDuyLong chạy được đấy chứ!
Chỉ có trường hợp cel cuối cùng có dữ liệu cần phải xét lại thôi.
Bác dùng thử xem có đúng ý không?

Sub thu()
If Cells(Cells.Rows.Count, 1).Value <> "" Then
k = Cells(Cells.Rows.Count, 1).Address(0, 0)
Else
k = Cells(Cells.Rows.Count, 1).End(xlUp).Address(0, 0)
End If
MsgBox (k)
End Sub

Thân.

Vẩn sai ở 2 trường hợp:

A65536 =IF(B65536="","",1)

và
A65536 có giá trị lổi

Tôi thấy code dùng UsedRange này rất hay:

Sub Test()
  k = Sheet1.UsedRange.Rows.Count
  MsgBox (k)
End Sub

Code này tìm được cả sự tồn tại của Comment... Đáng tiếc UsedRange lại phụ thuộc vào toàn bộ dử liệu trên bảng tính chứ không riêng gì cột A nên trong trường hợp ta có dử liệu ở những cột khác thì code có thể sai

 

[Po_Pikachu]

Tại bác đưa ra yêu cầu quá cao nên mới thấy quá khó? Còn UsedRange này sao không chính xác gì hết. Em test dữ liệu có ở A65531:C65534 mà sao nó cứ báo là 6 không là sao? Đây là code thêm vào việc xác định hàm.

Sub thu() On Error Resume Next If Cells(Cells.Rows.Count, 1).Value  "" Or  _       Cells(Cells.Rows.Count, 1).HasFormula = True Then      k = Cells(Cells.Rows.Count, 1).Address(0, 0) Else      k = Cells(Cells.Rows.Count, 1).End(xlUp).Address(0, 0) End If MsgBox (k) End Sub

Thân.

 

[ndu96081631]

Tại bác đưa ra yêu cầu quá cao nên mới thấy quá khó?
Còn UsedRange này sao không chính xác gì hết. Em test dữ liệu có ở A65531:C65534 mà sao nó cứ báo là 6 không là sao?
Đây là code thêm vào việc xác định hàm.

Sub thu()
On Error Resume Next
If Cells(Cells.Rows.Count, 1).Value <> "" Or _
      Cells(Cells.Rows.Count, 1).HasFormula = True Then
      k = Cells(Cells.Rows.Count, 1).Address(0, 0)
Else
      k = Cells(Cells.Rows.Count, 1).End(xlUp).Address(0, 0)
End If
MsgBox (k)
End Sub

Thân.

Code này thì được!
Thật ra với thuật toán trên:
-Xem cell cuối có dử liệu hay không
-Xem cell cuối có công thức hay không?
===> Cả 2 cái này có thể kết hợp thành 1 = IsEmpty(....) là xong
Tuy nhiên vẩn còn 1 vướng mắc như tôi đã nói hồi nãy: Chưa tìm được cell có Comment <--- Khó thật
---------------
Còn cái UsedRange sở dỉ sai là vì tôi gõ code bị nhầm, đúng ra phải là:

Sub Test()
  k = Sheet1.UsedRange.Rows.Count + Sheet1.UsedRange.Row - 1
  MsgBox (k)
End Sub

Xem thử, nó biết được sự tồn tại của Comment luôn đấy

 

[Po_Pikachu]

Có vẻ như vẫn chưa được vừa ý nhỉ?

Còn vấn đề Comment xem ra đau đầu đây! Ko biết có cách nào xác định ô đó có comment hay ko nhỉ? Thân.

 

[ndu96081631]

Có vẻ như vẫn chưa được vừa ý nhỉ?

.

Thì như tôi đã nói ở trên: Dùng UsedRange rất hay, nhưng đáng tiếc là ta không có cách nào cô lập để nó hiểu là ta đang tìm trong 1 cột nào đó... Nếu dử liệu chỉ có 1 cột thì OK
Ở đây nó tìm tất tần tật các cột đang có dử liệu, cứ cell nào có dử liệu mà có số dòng lớn nhất thì nó lấy <--- Tiếc là tiếc chổ này đây (nếu không thì code quá gọn rồi)

 

[Lệnh Hồ Đại Hiệp]

ThuNghi có thể đưa file đính kèm lên được không? Tôi test thấy bị sai rất nhiều chổ mà chưa biết nên sửa thế nào!
------------------------
Bây giờ nâng cấp lên 1 chút: Tìm cell cuối cùng có dử liệu, trong đó dử liệu là tùy ý, tính luôn cả Comment

Hơi dài dòng một xíu bác ạ :

Function LastCell() As String
    On Error Resume Next
    Dim rV As Long, rC As Long, rF As Long
    rV = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlComments).Row
    rC = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValue).Row
    rF = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas).Row
    LastCell = "A" & WorksheetFunction.Max(rV, rC, rF)
    
End Function

Chúc vui

 

[Po_Pikachu]

Bạn Lệnh Hồ Đại Hiệp ơi, mình test lại thấy có thể bỏ bớt dòng lệnh Value được đó. Bạn thử xem.

Function LastCell() As String     On Error Resume Next     Dim rV As Long, rC As Long, rF As Long     rV = ActiveSheet.Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, _           SearchOrder:=xlByColumns, LookIn:=xlComments).Row              rF = ActiveSheet.Columns("A:A").Find(What:="*", _          SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas).Row     LastCell = "A" & WorksheetFunction.Max(rV, rF) End Function

Thân.

 

[ndu96081631]

Hơi dài dòng một xíu bác ạ :

Function LastCell() As String
    On Error Resume Next
    Dim rV As Long, rC As Long, rF As Long
    rV = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlComments).Row
    rC = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValue).Row
    rF = ActiveSheet.Columns("A:A").Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas).Row
    LastCell = "A" & WorksheetFunction.Max(rV, rC, rF)
    
End Function

Chúc vui

Rất hay!
Sáng nay tôi cũng làm theo cách này nhưng báo lổi khi cột A không chứa Comments... Nó báo lổi tại dòng:

ActiveSheet.Columns("A:A").Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlComments)

Cũng hợp lý vì hỏng có Comment lấy đâu mà xác định ... thế mà bây giờ copy code này vào bảng tính lại không có vấn đề
Vậy để tùy biến hơn tôi sửa code lại 1 chút:

Function LastCell(Vung As Range) As String
    On Error Resume Next
    Application.Volatile
    Dim rV As Long, rC As Long, rF As Long
    rC = Vung.Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlComments).Row
    rV = Vung.Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlValue).Row
    rF = Vung.Find(What:="*", _
           SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas).Row
    LastCell = Cells(WorksheetFunction.Max(rC, rV, rF), Vung.Column).Address   
End Function

Cảm ơn Lệnh Hồ Xung Đại Hiệp rất nhiều vì code xác định LastCell tưởng đơn giãn nhưng xem ra chẳng đơn giãn tí nào
(Chúc Đại Hiệp mau hết.. bệnh và lệnh thành bí kiếp võ công... Ẹc... Ẹc...)

 

[SA_DQ]

Nếu ta nhập từ khóa 'Last cells' vô ô tìm kiếm, sẽ bớt đi 1 số bài rồi không!
chẳng hạng:
http://www.giaiphapexcel.com/forum/showthread.php?t=1641#10

 

[ndu96081631]

Nếu ta nhập từ khóa 'Last cells' vô ô tìm kiếm, sẽ bớt đi 1 số bài rồi không!
chẳng hạng:
http://www.giaiphapexcel.com/forum/showthread.php?t=1641#10

Sư phụ ơi xem lại bài #1 của em đi... Giãi pháp trong link mà sư phụ cho em có biết, nhưng đâu có giãi quyết được vấn đề em nêu chứ
Em không phải tìm LastCell của cả bảng tính mà tìm LastCell trong 1 cột được chỉ định trước (nếu không thì em dùng UsedRange rồi, cần gì phải rắc rối)

 

[PhanTuHuong]

Em nghĩ có thể dùng vòng lặp (for each ... next hoặc for ... next) duyệt qua từng ô trong cột. Trong quá trình lặp, ta có thể xác định được ô chứa dữ liệu bằng cách gán lại địa chỉ ô (Set Diachicuoi = ....), nếu ô đó không chứa thì có thể bỏ qua.
Cuối cùng ta vẫn xác định được địa chỉ ô cuối cùng chứa dữ liệu (Msgbox Diachicuoi.Addess).

 

[ndu96081631]

Em nghĩ có thể dùng vòng lặp (for each ... next hoặc for ... next) duyệt qua từng ô trong cột. Trong quá trình lặp, ta có thể xác định được ô chứa dữ liệu bằng cách gán lại địa chỉ ô (Set Diachicuoi = ....), nếu ô đó không chứa thì có thể bỏ qua.
Cuối cùng ta vẫn xác định được địa chỉ ô cuối cùng chứa dữ liệu (Msgbox Diachicuoi.Addess).

Cái này đương nhiên, nhưng ở đây tôi muốn tính tới tốc độ xử lý nữa! Với Excel2007 mà duyệt từ A1 đến A... chắc đuối luôn thầy ơi
Giãi pháp thì có rất nhiều, nhưng cái nào hay nhất và nhanh nhất thì ta xài

 

[nmhung49]

Nhân chủ đề này tôi cũng chia sẽ cách tìm ô cuối cùng dữ vào SpecialCells như sauL

[GPECODE=vb]
Sub LastCell()
Dim rng As Range
Dim LastRow As Long
With ActiveSheet
Set rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox LastRow
End With
End Sub


[/GPECODE]

 

[AnhThu-1976]

Em có viết code xác định cell cuối có dữ liệu, nhưng bị báo lỗi

Sub thu()

If Cells(Cells.Rows.Count, 1).Resize(, 13).Value = "" Then Cells(Cells.Rows.Count, 1).Resize(, 13) = 0
End Sub

Mục đích của em như sau:
Từ dòng "Cộng", xê dịch đến cột M, cụ thể hiện nay là cell M21
Nếu cell M21 này trống thì điền vào cell M21 giá trị bằng 0 (nếu khác trống thì không điền gì hết)
Nhờ anh/chị sửa code giúp
Xin cảm ơn!

 

[befaint]

Em có viết code xác định cell cuối có dữ liệu, nhưng bị báo lỗi

Sub thu()

If Cells(Cells.Rows.Count, 1).Resize(, 13).Value = "" Then Cells(Cells.Rows.Count, 1).Resize(, 13) = 0
End Sub

Mục đích của em như sau:
Từ dòng "Cộng", xê dịch đến cột M, cụ thể hiện nay là cell M21
Nếu cell M21 này trống thì điền vào cell M21 giá trị bằng 0 (nếu khác trống thì không điền gì hết)
Nhờ anh/chị sửa code giúp
Xin cảm ơn!

Option Explicit

Sub thu()
Dim lRcotA As Long
'Dòng cuối cùng có dữ liệu ở cột A (từ dưới lên):
lRcotA = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Dim cll As Range
'Ô ở cột M cùng dòng với lRcotA:
Set cll = Sheet1.Range("M" & lRcotA)
If cll = Empty Then cll = 0
End Sub

 

[AnhThu-1976]

Option Explicit

Sub thu()
Dim lRcotA As Long
'Dòng cuối cùng có dữ liệu ở cột A (từ dưới lên):
lRcotA = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Dim cll As Range
'Ô ở cột M cùng dòng với lRcotA:
Set cll = Sheet1.Range("M" & lRcotA)
If cll = Empty Then cll = 0
End Sub

Cám ơn bạn,
Có cách nào thay đổi Sheet1 thành cái gì đó không. Vì mình có thể chạy code ở bất kỳ sheet nào

 

[befaint]

Cám ơn bạn,
Có cách nào thay đổi Sheet1 thành cái gì đó không. Vì mình có thể chạy code ở bất kỳ sheet nào

Chỗ nào là "Sheet1" sửa thành "Activesheet"

Hoặc:

Sub thu()
Dim cll As Range
Set cll = Range("M" & Range("A65000").End(xlUp).Row)
If cll = Empty Then cll = 0
End Sub