Code xác định cell cuối cùng có dử liệu (1 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?

 

[Po_Pikachu]

Vậy thì dùng Worksheet_Change để lưu lại vị trí cuối cùng đã thao tác là được chứ gì? Bác xem file này có đúng ko? Thân.

 

[cadafi]

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:
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?

Nếu worksheet chỉ có dữ liệu ở cột A, ta có thể dùng:

Range("A1").SpecialCells(xlLastCell).Select

Nếu dữ liệu nằm ở nhiều cột, nhưng cột A sẽ phải là cột chuẩn chứa dữ liệu thì ta có thể dùng kết hợp cách trên và hàm offset để xác định ô có dữ liệu cuối cùng ở cột A.

 

[ndu96081631]

Vậy thì dùng Worksheet_Change để lưu lại vị trí cuối cùng đã thao tác là được chứ gì?
Bác xem file này có đúng ko?
Thân.

Lở vị trí cuối cùng ta thao tác không phải là dòng cuối cùng thì sao? Ví dụ:
- Tôi nhập vào A5
- Nhập tiếp vào A7
- Nhập cuối cùng vào A1
???
-------------------

Nếu worksheet chỉ có dữ liệu ở cột A, ta có thể dùng:


Nếu dữ liệu nằm ở nhiều cột, nhưng cột A sẽ phải là cột chuẩn chứa dữ liệu thì ta có thể dùng kết hợp cách trên và hàm offset để xác định ô có dữ liệu cuối cùng ở cột A.

Cái này là tìm LastCell đã dùng ---> Trong trường hợp tôi gõ vào A65536 1 giá trị, sau đó tôi xóa đi thì từ thời điểm này LastCell luôn nằm ở dòng 65536 đấy
Cái tôi cần: Tìm cell cuối cùng có dử liệu chỉ trong cột A

 

[Po_Pikachu]

Lở vị trí cuối cùng ta thao tác không phải là dòng cuối cùng thì sao? Ví dụ: - Tôi nhập vào A5 - Nhập tiếp vào A7 - Nhập cuối cùng vào A1 ???

Thi bác nhập thử xem, phải là RngCuoiCung báo là địa chỉ ở A1 là cuối cùng đó sau? Nếu muốn giới hạn chỉ có cột A mới lưu vị tri cuối cùng thôi thì viết thêm 1 lệnh IF nữa là được.

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then ActiveWorkbook.Names.Add Name:="RngCuoiCung", RefersToR1C1:=Target.Address(0, 0) End If End Sub

Thân.

 

[ndu96081631]

Thi bác nhập thử xem, phải là RngCuoiCung báo là địa chỉ ở A1 là cuối cùng đó sau?
Nếu muốn giới hạn chỉ có cột A mới lưu vị tri cuối cùng thôi thì viết thêm 1 lệnh IF nữa là được.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
ActiveWorkbook.Names.Add Name:="RngCuoiCung", RefersToR1C1:=Target.Address(0, 0)
End If
End Sub

Thân.

Ah... ha... vậy là người nói 1 đàng, kẽ hiểu 1 nẻo rồi
CUỐI CÙNG ở đây là đang nói về KHÔNG GIAN chứ không phải THỜI GIAN
ví dụ:
- Tôi nhập vào A7
- Nhập tiếp vào A15
- Rồi nhập tiếp vào A1
vậy vị trí tôi cần tìm là cell A15 (tức cell nằm dưới cùng chứ không phải nhập lần cuối )

From Po_Pikachu :
Trời!
Vậy thì dùng lệnh này cũng được rồi, cần gì hỏi chi cho mệt vậy bác?

 k = Range("A60000").End(xlUp).Address(0,0)

Thân.

 

[cadafi]

Trời!
Vậy thì dùng lệnh này cũng được rồi, cần gì hỏi chi cho mệt vậy bác?

k = Range("A60000").End(xlUp).Address(0,0)

Thân.

Anh ndu muốn xài công thức đúng cho cả excel 2003 và Excel 2007 luôn cơ! Với lại nếu dữ liệu tại ô A65536 thì k = Range("A60000").End(xlUp).Address(0,0)không cỏn đúng nữa.

@ ndu:
Vậy anh thêm If vào được không, đại khái như sau:

Function FindLastRng() As String
Application.Volatile
Dim RngTarget As Range
''Mượn tạm cột B:
Set RngTarget = Sheets("Sheet1").[B1].End(xlDown).Offset(, -1)
If RngTarget <> "" Then
    FindLastRng = RngTarget.Address
Else
    FindLastRng = RngTarget.End(xlUp).Address
End If
End Function

 

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

Lở vị trí cuối cùng ta thao tác không phải là dòng cuối cùng thì sao? Ví dụ:
- Tôi nhập vào A5
- Nhập tiếp vào A7
- Nhập cuối cùng vào A1
???
-------------------

Cái này là tìm LastCell đã dùng ---> Trong trường hợp tôi gõ vào A65536 1 giá trị, sau đó tôi xóa đi thì từ thời điểm này LastCell luôn nằm ở dòng 65536 đấy
Cái tôi cần: Tìm cell cuối cùng có dử liệu chỉ trong cột A

Dùng phương thức Find thôi bác ạ:
Tìm trong cột A:

Function LastCell() As String
    On Error Resume Next
    LastCell = ActiveSheet.Columns("A:A").Find(What:="*", _
                  SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Address
End Function

Nếu muốn mở rộng thì có thể tham khảo UDF của hoangdanh.

Chúc vui.

 

[Po_Pikachu]

Không biết kết hợp dòng cuối để quét lên không biết dùng được không nhỉ?

Sub thu() k = Range("A" & Range("A1").SpecialCells(xlLastCell).Row + 1).End(xlUp).Address(0, 0) MsgBox (k) End Sub

Thân.

 

[boyxin]

Trời!
Vậy thì dùng lệnh này cũng được rồi, cần gì hỏi chi cho mệt vậy bác?

k = Range("A60000").End(xlUp).Address(0,0)

Thân.

File đính kèm minh hoạ, làm rõ hơn nội dung câu hỏi​

 

[tedaynui]

Xem ra cũng nhiều cách nhỉ.
Thêm 1 củ chuối, anh ndu tham khảo :

    Columns("A:A").Select
    with Selection
           .AutoFilter
           .AutoFilter Field:=1, Criteria1:="<>"
           .End(xlDown).Select
    end with

TDN

 

[cadafi]

Dùng phương thức Find thôi bác ạ:
Tìm trong cột A:

Function LastCell() As String
    On Error Resume Next
    LastCell = ActiveSheet.Columns("A:A").Find(What:="*", _
                  SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Address
End Function

Nếu muốn mở rộng thì có thể tham khảo UDF của hoangdanh.

Chúc vui.

Lệnh Hồ đại ca xuất chiêu có khác! Đa tạ Lệnh Hồ đại ca đã nhắc nhở tiểu đệ vể phương thức Find này (đệ cũng đang mày mò). Be Be.

 

[ndu96081631]

Với cách của Ca_dafi thì:
-Lở như tại dòng cuối A65536 tôi có công thức:

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

thì sao ta?
???
Cách của Po_picachu :
- Lở tại cell cuối cùng cũng đang có dử liệu thì sao ----> SpecialCells(xlLastCell).Row + 1 sẽ trật lất

Xem ra cũng nhiều cách nhỉ.
Thêm 1 củ chuối, anh ndu thao khảo :

    Columns("A:A").Select
    with Selection
           .AutoFilter
           .AutoFilter Field:=1, Criteria1:="<>"
           .End(xlDown).Select
    end with

TDN

Cách này cũng sai luôn trong trường hợp:

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

 

[cadafi]

Với cách của Ca_dafi và Lệnh Hồ Xung thì:
-Lở như tại dòng cuối A65536 tôi có công thức:
A65536 =IF(B65536="","",1)
thì sao ta?

Không biết còn "lở" chỗ nào nữa không?

Function FindLastRng() As String
Application.Volatile
Dim RngTarget As Range
Set RngTarget = Sheets("Sheet1").[B1].End(xlDown).Offset(, -1)
If RngTarget <> "" Or RngTarget.HasFormula Then   ''//Kiểm tra thêm ô cuối có công thức hôn?
    FindLastRng = RngTarget.Address
Else
    FindLastRng = RngTarget.End(xlUp).Address
End If
End Function

 

[ndu96081631]

Không biết còn "lở" chỗ nào nữa không?

Function FindLastRng() As String
Application.Volatile
Dim RngTarget As Range
Set RngTarget = Sheets("Sheet1").[B1].End(xlDown).Offset(, -1)
If RngTarget <> "" Or RngTarget.HasFormula Then   ''//Kiểm tra thêm ô cuối có công thức hôn?
    FindLastRng = RngTarget.Address
Else
    FindLastRng = RngTarget.End(xlUp).Address
End If
End Function

Vẩn còn... "lở"
Lở như công thức tại cell cuối trả về là #NAME? thì sao?
Ẹc... Ẹc...
Tạm thời đến thời điểm này thì cách của Lệnh Hồ Xung đại hiệp đúng nhất
(hồi này tôi test nhầm---> Sorry)

 

[ThuNghi]

Tôi có xào lại cái code này của

'David Chazin -- September 1999.
'Returns the Address (e.g. "$AW$235") of the cell at the
' bottom-right corner of the used area of the currently
' selected worksheet. Returns "" if the worksheet is empty.
' Thanks to Dave Braden for the idea of trapping for an
' empty worksheet. (This is not the way he would implement
' it, but the idea itself is his).

Code như sau:
Cú pháp: LastRow(Tên cột kiểu text): LastRow("X")...

Function LastRow(sCol As String) As String
On Error GoTo LastRow_ErrorHandler
If Range(sCol & "1").SpecialCells(xlLastCell).Value <> "" Then
  LastRow = Range(sCol & "1").SpecialCells(xlLastCell).Row
Else
  LastRow = _
  Columns(sCol).Find("*", Range(sCol & "1").SpecialCells(xlLastCell), , , _
    xlByColumns, xlPrevious).Row
End If
Exit Function
LastRow_ErrorHandler:
  If Err.Number = 91 Then
    'If the worksheet is empty....'
     LastRow = ""
  Else
    Call MsgBox("Error #" & Err.Number & " was generated by " & _
     Err.Source & ": " & Err.Description, vbOKOnly + vbExclamation, _
     "LastRow()", Err.HelpFile, Err.HelpContext)
  End If
End Function

 

[tedaynui]

Lục lạo một hồi, em tìm được File LastCell hay quá.
Nguồn từ : Aaron T. Blood (www.XL-Logic.com)

Mời anh xem thử
TDN

 

[ndu96081631]

Tôi có xào lại cái code này của
Code như sau:
Cú pháp: LastRow(Tên cột kiểu text): LastRow("X")...

Function LastRow(sCol As String) As String
On Error GoTo LastRow_ErrorHandler
If Range(sCol & "1").SpecialCells(xlLastCell).Value <> "" Then
  LastRow = Range(sCol & "1").SpecialCells(xlLastCell).Row
Else
  LastRow = _
  Columns(sCol).Find("*", Range(sCol & "1").SpecialCells(xlLastCell), , , _
    xlByColumns, xlPrevious).Row
End If
Exit Function
LastRow_ErrorHandler:
  If Err.Number = 91 Then
    'If the worksheet is empty....'
     LastRow = ""
  Else
    Call MsgBox("Error #" & Err.Number & " was generated by " & _
     Err.Source & ": " & Err.Description, vbOKOnly + vbExclamation, _
     "LastRow()", Err.HelpFile, Err.HelpContext)
  End If
End Function

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

 

[Po_Pikachu]

Cách của Po_picachu : - Lở tại cell cuối cùng cũng đang có dử liệu thì sao ----> SpecialCells(xlLastCell).Row + 1 sẽ trật lất

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.

 

[ndu96081631]

Tôi cũng có 1 code (dự phòng) nhưng vẩn chưa tính được trường hợp có Comment:

Sub LastCell()
 Dim Rng As Range, Cot As Range, k As Boolean
 Set Cot = Application.InputBox(Prompt:="Hay chon 1 cell bat ky", Type:=8)
 Set Rng = Cells(Cells.Rows.Count, Cot.Column)
 k = IsEmpty(Rng)
 Cells(-k * Rng.End(xlUp).Row - (Not (k)) * Rng.Row, Rng.Column).Select
End Sub

Nhờ các bạn Test dùm xem có chổ nào không ổn