Home » VBA Excel » VBA Excel Range, Cells

VBA Excel Range, Cells

viết bởi Lê Quốc Thái
  1. Loạt các bài viết có liên quan chuyên đề VBA:
  2. VBA Excel Hộp thoại thông báo
  3. VBA Excel Biến trong VBA Excel
  4. VBA Excel sử dụng Scripting Dictionary
  5. VBA Excel Biến trong VBA Excel
  6. VBA Excel Workbook, worksheet
  7. VBA Excel Range, Cells
  8. VBA Excel Hàm trong Excel VBA
  9. VBA Excel Events, Application Object
  10. VBA Excel Function and Sub Excel VBA
  11. VBA Excel Array Mảng trong Excel VBA
  12. VBA Excel FileSystemObject trong Excel VBA
  13. VBA Excel Collection trong Excel VBA
  14. VBA Excel Hashtable trong Excel VBA
  15. VBA Excel Stack trong Excel VBA
  16. VBA Excel Queue trong Excel VBA
  17. VBA Excel SortedList Excel VBA
  18. VBA Excel ArrayList Excel VBA
  19. VBA Excel Ví dụ về Scripting Dictionary
  20. Sách VBA Excel 2016 power programming with vba (pdf)

Range, cells là 2 đối tượng của worksheets (cells cũng là đối tượng của Range).

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") 
OR: 
Range("A5:B10") 
OR: 
[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 Run (khởi chạy) 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
    'Result 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      'Rusult 20
    MsgBox Sheet1.Range("D20").Column   'Rusult 4
End Sub

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

Sub RngAddress()
    MsgBox Sheet1.Range("A2:B9").Address
    Result "$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 
Cells(2,5).Select

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

Tải file ví dụ về Range, Cells

Bài viết liên quan

Viết ý kiến của bạn