Bài 6. Range, Cells

[befaint]

Bài 6. Range, Cells

(Danh sách các bài viết về VBA xem ở đây Index - Các bài viết về VBA)

1. Đối tượng Range
+ Cấu trúc:

Worksheets.Range(Cell1, [Cell2])

Cell1 là cell đầu tiên trong vùng được gọi tới, tham số bắt buộc.
Cell2 là cell cuối cùng trong vùng được gọi tới, tham số không bắt buộc.
- Range có thể gồm một hoặc nhiều cells.
- Range có thể gồm một hoặc nhiều dòng/ cột, vùng không liên tục:
Ví dụ:

Set Rng1 = Sheet1.Range("A5", "B10")
Set Rng2 = Sheet1.Columns("B:D")
Set Rng3 = Sheet1.Rows("2:5")

+ Các cách viết gọi một range trong VBA:

Range("A5", "B10")
'Hoặc:'
Range("A5:B10")
'Hoặc:'
[A5:B10]

*Lưu ý:
- Khi gọi một range ta viết chỉ định cho một worksheet nào đó: Sheet1.Range("A5:B10")
Nếu không viết chỉ định Range("A5:B10") cho một worksheet cụ thể thì sẽ được hiểu Range đang gán cho worksheet hiện hành ActiveSheet.Range("A5:B10"). Khi gọi một Sub (thủ tục) nào đó trong cửa sổ VBA phải đảm bảo worksheet muốn thực hiện Sub đó phải đang được Active, nếu không sẽ chạy nhầm worksheet; hoặc ta có thể gán Sub đó vào nút lệnh trên worksheet muốn thực hiện và kích hoạt Sub bằng cách click vào nút lệnh.

2. Đối tượng Cells
+ Cấu trúc:

Worksheets.Cells([RowIndex], [ColumnIndex])
Range.Cells([RowIndex], [ColumnIndex])

- RowIndex: Địa chỉ dòng của cell cần gọi, nếu bỏ qua thì mặc định tham số nhận giá trị là 1.
- ColumnIndex: Địa chỉ cột của cell cần gọi, nếu bỏ qua thì Cells trả về có chỉ số trong Range được tính từ trái sang phải, từ trên xuống dưới.
- Nếu bỏ qua cả 2 tham số thì trả về toàn bộ Cells trong Range.
Ví dụ:

Sub RngObject()
    Dim Rng As Range, Cll As Range, Cll1 As Range
    Set Rng = Sheet1.Range("A5:B10")
    Set Cll = Sheet1.Cells(1, 1)
    Set Cll1 = Rng.Cells(1, 1)
    MsgBox Rng.Address & vbNewLine & Cll.Address & vbNewLine & Cll1.Address
End Sub

3. Một số thuộc tính, phương thức thường dùng
3.1. Thuộc tính:
Value
Value2
Text
Xét ví dụ cụ thể bên dưới:

Formula / Hasformula

Sub Set_Formula()
    Dim Cll As Range, i As Long
    Set Cll = Sheet1.Range("C5")
    For i = 0 To 5
        Cll.Offset(i, 0).Value = i + 10
    Next i
    Cll.Offset(6, 0).Formula = "=Sum(C5:C10)"
    MsgBox Cll.Offset(6, 0).HasFormula
End Sub

Offset
Cú pháp:

Range.Offset([RowOffset],[ColumnOffset])

RowOffset: Số dòng giữa vùng tham chiếu gốc và vùng đích, hướng về phía trên Range thì RowOffset mang giá trị âm.
Nếu không nhập thì RowOffset=0, cú pháp khi đó:

Range.Offset(, ColumnOffset)

ColumnOffset: Số cột giữa vùng gốc và vùng đích, hướng về bên trái Range thì ColumnOffset mang giá trị âm.
Nếu không nhập thì ColumnOffset=0, cú pháp khi đó:

Range.Offset(RowOffset)

Ví dụ:

Sub Offset()
    'Range.Offset([RowOffset],[ColumnOffset])'
    Dim Cll As Range, Rng As Range
    Set Cll = Sheet1.Range("B2")
    Set Rng = Sheet1.Range("A5:A10")
    MsgBox Cll.Offset(1, 1).Address     '$C$3'
    MsgBox Cll.Offset(, 2).Address      '$D$2'
    MsgBox Cll.Offset(2).Address        '$B$4'
    MsgBox Rng.Offset(1, 1).Address     '$B$6:$B$11'
    MsgBox Rng.Offset(, 1).Address      '$B$5:$B$10'
    MsgBox Rng.Offset(2).Address        '$A$7:$A$12'
End Sub

Resize
Cú pháp:

Range.Resize([RowSize],[ColumnSize])

RowSize: Số dòng dãn ra so với cell đầu tiên của vùng tham chiếu gốc.
- Nếu không nhập thì RowSize=Range.Rows.Count, cú pháp khi đó:

Range.Resize(, ColumnSize)

ColumnSize: Số cột dãn ra so với cell đầu tiên của vùng tham chiếu gốc.
- Nếu không nhập thì ColumnSize=Range.Columns.Count, cú pháp khi đó:

Range.Offset(RowSize)

Ví dụ:

Sub Resize()
    'Range.Resize([RowSize],[ColumnSize])'
    Dim Cll As Range, Rng As Range
    Set Cll = Sheet1.Range("B2")
    Set Rng = Sheet1.Range("A5:A10")
    MsgBox Cll.Resize(2, 2).Address     '$B$2:$C$3'
    MsgBox Cll.Resize(, 2).Address      '$B$2:$C$2'
    MsgBox Cll.Resize(2).Address        '$B$2:$B$3'
    MsgBox Rng.Resize(3, 3).Address     '$A$5:$C$7'
    MsgBox Rng.Resize(, 2).Address      '$A$5:$B$10'
    MsgBox Rng.Resize(2).Address        '$A$5:$A$6'
End Sub

Count: Đếm các ô có trong range

Sub CountCells()
    Dim Rng As Range
    Set Rng = Sheet1.Range("A1:B10")
    MsgBox Rng.Count
    ''20
End Sub

Cell.Row: Trả về địa chỉ dòng của ô được gọi.
Cell.Column: Trả về địa chỉ cột của ô được gọi.

Sub RowColumnOfCell()
    MsgBox Sheet1.Range("D20").Row      '20'
    MsgBox Sheet1.Range("D20").Column   '4'
End Sub

Address: Trả về địa chỉ của vùng được gọi

Sub RngAddress()
    MsgBox Sheet1.Range("A2:B9").Address
    '"$A$2:$B$9"  '
End Sub

Numberformat: Định dạng dữ liệu vùng tham chiếu tới.

Sub FormatRange()
    Sheet1.Range("A15").Value = 1000000
    Sheet1.Range("A15").NumberFormat = "#,0"
    Sheet1.Range("A16").Value = Date
    Sheet1.Range("A16").NumberFormat = "dd/mm/yyyy"
End Sub

End: Trả về cell cuối cùng theo hướng chỉ định.
(Nó tương ứng với việc nhấn Ctrl + Up/Down/Left/Right Arrow)
Cú pháp:

Range.End(XlDirection)

Với XlDirection có 4 tham số: xlDown, xlToLeft, xlToRight, xlUp

Sub EndDirection()
    Range("G20").End(xlDown).Select
    'Tra vê cell duoi cung côt G có du liêu liên tuc, tinh tu G20'
    'Press: Ctrl + Arrow Down from cell G20'
    Range("G20").End(xlUp).Select
    'Tra vê cell trên cung côt G có du liêu dâu tiên, tinh tu G20'
    'Press: Ctrl + Arrow Up from cell G20'
    Range("G20").End(xlToLeft).Select
    'Tra vê cell dâu tiên bên trái trên dòng 20 có du liêu, tinh tu G20'
    'Press: Ctrl + Arrow Left from cell G20'
    Range("G20").End(xlToRight).Select
    'Tra vê cell ngoài cùng bên phai trên dòng 20 có du liêu liên tuc, tinh tu G20'
    'Press: Ctrl + Arrow Right from cell G20'
End Sub

Ứng dụng thuộc tính End() để xác định dòng cuối cùng có dữ liệu:

Sub LastRow1()
    'Tra vê dòng trên cùng có du liêu dâu tiên, xet tu cell duoi cùng bang tinh tro lên'
    Dim lRow As Long
    lRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
    'Voi Excel 2003-97: Rows.Count=65,536  '
    'Voi Excel 2007 tro len: Rows.Count=1,048,576  '
    MsgBox lRow
End Sub

Sub LastRow2()
    'Tra vê dòng duoi cùng có du liêu liên tuc, xet tu cell chi dinh tro xuong'
    Dim lRow As Long
    lRow = Sheet1.Range("A5").End(xlDown).Row
    MsgBox lRow
End Sub

3.2. Phương thức:
Select: Chọn vùng

Range("A2:C10").Select

Activate: Kích hoạt cell chỉ định hiện hành

Cells(2,5).Activate

Copy: Sao chép vùng dữ liệu, bằng với thao tác chọn vùng dữ liệu rồi nhấn Ctrl + C

Range("A2:C10").Copy

Paste: Dán dữ liệu sau khi sao chép, bằng với thao tác nhấn Ctrl + V

Sub CopyRange()
    Range("A1:A3").Select
    Selection.Copy
    Range("C15").Select
    ActiveSheet.Paste
End Sub
'Viêt gon:'
Sub CopyRange2()
    Range("A1:A3").Copy Range("C15")
End Sub

Clear: Xóa nội dung và định dạng của vùng chỉ định
ClearContents: Chỉ xóa nội dung
ClearFormats: Chỉ xóa định dạng

Sub Clear()
    Dim Rng As Range
    Set Rng = Sheet1.Range("A1:C30")
    Rng.Clear           'Xoa nôi dung va dinh dang'
    Rng.ClearContents   'Chi xóa nôi dung'
    Rng.ClearFormats    'Chi xoa dinh dang'
End Sub

Delete: Xóa vùng, xóa dòng/cột, xóa ô (bằng với thao tác nhấn tổ hợp phím Ctrl và dấu trừ -)

Sub Delete()
    Sheet1.Rows("25:30").Delete
    Sheet1.Columns("H:K").Delete
End Sub

Find: Tham khảo các bài viết sau.
http://www.giaiphapexcel.com/dienda...-phương-thức-tìm-kiếm-find-find-method.15116/
http://www.giaiphapexcel.com/diendan/threads/dùng-phương-thức-find.92686/page-2
http://www.giaiphapexcel.com/dienda...ng-thức-find-findnext-trong-hàm-tự-tạo.37436/