Bài viết: Thuộc tính End() trong Excel - giới thiệu tổng quan (P1)

[SA_DQ]

Thuộc tính End​

Trả về một đối tượng range đặc trưng cho ô ở cuối miền chứa range nguồn. Kết quả tương đương đạt được bằng việc nhấn END+ các phím mũi tên: Xuống dưới, qua phải, sang trái hay lên trên. Thuộc tính đối tượng range này thuộc loại chỉ đọc. Cú pháp của thuộc tính:
expression.End(Direction)
expression Biến thuộc loại bắt buộc. Một biểu thức trả về một trong các thuộc tính Range, Cells hay thuộc tính Offset() hoặc phương thức Union(). . . .
Direction Giá trị XlDirection thuộc loại bắt buộc. Hướng di chuyển, có thể là một trong các hằng số XlDirection sau:
xlDown; xlToRight
xlToLeft; xlUp
Ví dụ
Câu lệnh dưới đây chọn ô ở phía dưới cùng cột B trong vùng dữ liệu chứa ô B4.
Range("B4").End(xlUp).Select

Còn ví dụ này chọn cột ô cuối phải chứa dữ liệu của dòng 4, xuất phát từ ‘B4’
Range("B4").End(xlToRight).Select

Còn ví dụ này mở rộng vùng lựa chọn từ ô B4 đến ô cuối cùng của hàng 4 chứa dữ liệu.
Worksheets("Sheet1").Activate
Range("B4", Range("B4").End(xlToRight)).Select


Ví dụ 1: Tìm ô chứa dữ liệu cuối cùng trong cột trước ô trống:

Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

Ví dụ 2: Tìm ô tận cùng trong cột có chứa dữ liệu:

Sub LastCellInColumn()
Range("A65536").End(xlup).Select
End Sub

Ví dụ 3: Tìm ô cuối cùng trước ô trống trong hàng:

Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub

Ví dụ 4: Tìm ô cuối cùng trong hàng có chứa dữ liệu:

Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub

Ví dụ 5: Tìm ô cuối cùng đang sử dụng của trang tínht:

Sub Demo()
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub

Các ứng dụng của thuộc tính End trong excel​

1 Tìm dòng, cột hay ô cuối cùng của trang tính.

Chúng ta hoàn toàn có thể vô menu Edit ->Go to -> Special -> Last cell tìm đến ô cuối của trang tính đang được kích hoạt. Nhưng những kết quả này hoàn toàn không mấy tin cậy. Nguyên nhân nằm ở hai vấn đề sau:

a. Ô cuối ta vừa thiết đặt sau khi lưu dữ liệu: Chúng ta mường tượng như sau: Chúng ta có dữ liệu số & chuỗi trong vùng B10 đến C20 của 1 trang tính mới. Chúng ta thực hiện xóa dữ liệu trong ô C20 đi; Sau đó thực hiện lệnh từ menu Edit ->Go to -> Special -> Last cell chúng ta sẽ vẫn được đưa tời ô C20 vì rằng dữ liệu chưa được lưu từ thời điểm ta xóa dữ liệu đến lúc thực hiện lệnh Go to.

b. Những ô tình cờ bị format. Giả dụ, chúng ta nhập giá trị chuỗi hay sô nào đó vô D9; Sau đó ta chọn D18 của 1 trang tính mới & nhập giá trị chỉ ngày vô đó. Sau khi ta xóa dữ liệu trong D18 này đi & tiến hành lưu bảng tính. Thực hiện lệnh Edit -> Go to -> Special -> Last cell Chúng ta vẫn được đưa về ô D18. Vì rằng, khi ta nhập giá trị ngày vô D18 đồng nghĩa với việc excel tự động định dạng ô này là dữ liệu kiểu ngày tháng thuộc loại "General". Để excel không đưa ta về lối xưa này nữa, ta phải chọn D18 & thực hiện các lệnh trong menu Edit -> Clear -> All và tiến hành công đoạn lưu giữ nữa!
Cũng sẽ gặp trường hợp như vậy, nếu ta dùng dòng lệnh VBA sau:

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

Từ đó ta có ba phương án để tìm dòng cuối, cột cuối & ô chưa dữ liệu cuối trang tính, như sau

Tìm đến dòng cuối chứa dữ liệu của trang tính:

Sub FindLastRow()
Dim LRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Tìm các dòng có chứa dữ liệu.
LRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _ 
SearchDirection:=xlPrevious).Row
MsgBox LRow
End If
End Sub

Tìm đến cột cuối chứa dữ liệu của trang tính:

Sub FindLastColumn()
Dim LColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
' Tìm các cột có chứa dữ liệu.
LColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LColumn
End If 
End Sub

Tìm đến ô cuối cùng chứa dữ liệu của trang tính:

Sub FindLastCell()
Dim LColumn As Integer: Dim LRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then
Tìm dòng cuối có chứa dữ liệu.
LRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
' Tìm cột cuối có chứa dữ liệu.
LColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox Cells(LRow, LColumn).Address
End If 
End Sub

2 Vùng động được đặt tên với VBA

Để chuyển macro có nội dung như dưới đây trở thành macro của bảng tính:
Tại ứng dụng excel, chúng ta có thể kích chuột phải lên biểu tượng trái trên nhất của màn hình excel (bên trái mục "File" của thanh menu) và chọn "View Code". Tiếp theo, ta copy & dán đoạn mã này vô cửa sổ VBA

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim SheetName As String, NameAddress As String
With Sheet1 ' CodeName
'Xác định tên của Sheet1 tại ngăn SheetNames & vùng hiện hành mở từ A1.
SheetName = "=" & .Name & "!"
NameAddress = .Range("A1").CurrentRegion.Address
'Thêm tên MyRange cho vùng đã mở xuất pháttừ A1
ActiveWorkbook.Names.Add _
Name:="MyRange",RefersTo:=SheetName & NameAddress
End With
End Sub

Một phương pháp khác ta có thể làm là kích chuột phải lên ngăn chứa tên sheet, chọn "View Code” và nhập vào:

Private Sub Worksheet_Calculate()
Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
End Sub

Đoạn mã trên sẽ đặt tên "MyRange” cho mọi ô dữ liệu trong cột A mỗi lần Worksheet được tính lại. Nếu ta cần chọn miền nhiều cột, thì dùng đoạn mã sau:

Private Sub Worksheet_Calculate()
Range(Range("IV1").End(xlToLeft), _
Range("A65536").End(xlUp)).Name = "MyRange"
End Sub

Ta cũng có thể dùng sự kiện Change như dưới đây:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
End If
End Sub

Xác định tên cho cột ‘A’ chứa dữ liệu & có thể có cả các ô trống:

Sub OneColumnDyaRange()
Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
End Sub

Đặt tên cho 1 vùng của cột ‘A’ bắt đầu từ A1 & hoàn toàn không chứa các ô trống:

Sub OneColumnDyaRange()
Range("A1", Range("A1").End(xlDown)).Name = "MyRange"
End Sub

Cả hai đoạn mã ví dụ trên có thể được đặt trong module Workbook để chúng được định nghĩa lại mỗi lần sự kiện này được gọi, chẳng hạng: Workbook_BeforeSave, Workbook_Open, v.v



3. Thêm một hàng khi cột dữ liệu có thay đổi



Giả sử ta có một danh sách dài và ta muốn thêm một hàng mỗi khi cần thay đổi. Thay vì dùng một vòng lặp đơn giản, ta có thể dùng cách nhanh hơn sau đây. (Cách này đòi hỏi dữ liệu phải được sắp xếp thứ tự trước!)

Sub InsertRowAtEachChange()
Dim rRange As Range
'Bảo đảm các cột được chọn
If Selection.Cells.Count <> 65536 Then
MsgBox "You must select an entire column", vbCritical
End
End If
On Error Resume Next
'Gán vô biến Range vùng dữ liệu từ cột được chọn
Set rRange = Range(Selection.Cells(2, 1), _
Selection.Cells(65536, 1).End(xlUp)) 
'Thêm công thức vô cột
With rRange
.EntireColumn.Insert
.Offset(0, -1).FormulaR1C1 = _
"=IF(AND(NOT(ISNA(R[-1]C))," & "R[-1]C[1]<>RC[1]),0,"""")"
'Chuyển từ dạng công thức sang dạng số liệu
.Offset(0, -1) = .Offset(0, -1).Value
'Gán lại biến Range
Set rRange = .Offset(0, -1).SpecialCells _
(xlCellTypeConstants, xlNumbers)
End With
'Thêm dòng nếu cần
If WorksheetFunction.CountIf(rRange, 0) > 0 Then
rRange.EntireRow.Insert
End If
'Gán lại biến Range
Set rRange = Range(Selection.Cells(2, 1), _
Selection.Cells(65536, 1).End(xlUp))
'Thêm công thức vô vùng biến
rRange.FormulaR1C1 = _
"=IF(OR(RC[1]="""",R[-1]C[1]=""""),""""," _
& "IF(RC[1]<>R[-1]C[1],0))"
'Chuyển từ dạng công thức sang dạng số liệu
rRange = rRange.Value
'Gán không cho vùng biến
If WorksheetFunction.CountIf(rRange, 0) > 0 Then
Set rRange = rRange.SpecialCells(xlCellTypeConstants, xlNumbers)
'Thêm dòng
rRange.EntireRow.Insert
End If
'Xóa cột vừa thêm
rRange.Columns(1).EntireColumn.Delete
On Error GoTo 0
Set rRange = Nothing
End Sub

Ta nên sắp xếp thứ tự rồi chọn toàn bộ cột trước khi chạy đoạn mã này.

Một số bài viết có liên quan:
1/ Kích hoạt macro từ nút bấm ngoài bảng tính
2/ Làm thế nào để thay thế các chữ OK, CANCEL,... nhàm chán của Msgbox
3/ Dùng Macro 4 để tạo menu
4/ Giới thiệu VBA trong Excel
5/ Viết code để nhìn thấy ai là người cập nhật bảng tính của bạn lần gần đây nhất
6/ 4 cách sử dụng Immediate Window trong VBA hiệu quả hơn
7/ 3 gợi ý nhỏ mang lại thành công trong khai báo biến trong VBA
8/ Tạm dừng VBA bằng cách sử dụng Wait, Sleep hoặc Vòng lặp
9/ Hiểu biết thêm về phương thức Evaluate - Understanding Evaluate Method
10/ Tham số hình thức và tham số thực sự
http://www.giaiphapexcel.com/vbb/content.php?547