Thuộc tính Resize của đối tượng Range trong VBA (2 người xem)

[SA_DQ]

Thuộc tính Resize​

Định lại kích thước của 1 vùng đặc trưng. (Thuộc tính) sẽ trả về là đối tượng vùng (range) thay cho vùng trước đó, khi thuộc tính chưa tác động.

Cú pháp của thuộc tính là
expression.Resize(RowSize, ColumnSize)

Expression - Đây là đối số bắt buột và là đối tượng range chịu tác động của thuộc tính Resize

RowSize : Số dòng – là biến tùy chọn và là số dòng của vùng mới.Nếu đối số bị bỏ qua, thì hai vùng trước & sau thuộc tính tác động có số dòng như nhau.

ColumnSize: số lượng cột – là biến tùy chọn và là số cột của vùng mới.
Nếu đối số bị bỏ qua, thì hai vùng trước & sau tác động của thuộc tính có số cột như nhau

Ví dụ ta có một macro với nội dung như sau:

Sub Resize1()
MsgBox Range("A2:B6").Resize(, 3).Address, , _
"Range('A2:B6').Resize(, 3).Address"
End Sub

Macro này sẽ hiện cho ta biết địa chỉ vùng ‘A2:B6’ sau khi bị tác động bỡi thuộc tính resize, với số hàng như của vùng ‘A2:B6’ & số cột là 3;
(Trong hình 1, vùng vàng nhạt là vùng chưa tác động bỡi thuộc tính; Còn vùng sau tác động của thuộc tính là 2 vùng có màu vàng gộp chung)

H1 Thuộc tính chỉ mở rọng cột không mở rọng hàng.

Ví dụ
Ví dụ 2 Đoạn mã sau đây định lại kích cỡ vùng đã chọn (Selection) trong sheet1 bằng cách mở rọng thêm 1 cột & 1 hàng so với vùng ban đầu

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select

 

[SA_DQ]

Ví dụ 3

Ví dụ này mô phỏng việc chúng ta có bảng dữ liệu trong sheet2 gồm cả phần tiêu đề.
Nếu trước đó ta đã chọn toàn bộ bảng dữ liệu (gồm cả phần tiêu đề).
Chúng ta có thể dùng thuộc tính Resize để kích hoạt riêng phần dữ liệu của bảng
(mà không gồm phần tiêu đề), như sau

Sub resize2()
   Dim Rng As Range   
   Set Rng = [a1].CurrentRegion
‘2  MsgBox Rng.Address
   Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1).Select
   MsgBox Selection.Address, , MsgBox Rng. _
             Offset(1, 0).Resize(Rng.Rows.Count - 1).Address
End Sub

Hình 2 Dùng Resize để chọn chỉ vùng chứa dữ liệu​

Hộp thoại phía phải màn hình sẽ xuất hiện khi ta
không vô hiệu hóa dòng lệnh thứ 2 (bằng cách bỏ dấu nháy đơn).
Hộp thoại này cho ta biết vùng được chọn trước khi thuộc tính tác động.

Hộp thoại phía dưới cho ta biết địa chỉ vùng mới,
sau khi tác động của thuộc tính.

Ví dụ 4:

Ta xét thêm một ví dụ nữa, nhằm phân biệt giữa thuộc tính Resize & thuộc tính Offset .
Macro có nội dung sau:

Sub Resize3() 
  Range("B2").Resize(1, 2).Select
 MsgBox Selection.Address 
 [b5].Offset(1, 1).Resize(1, 2).Select
  MsgBox Range("B5").Offset(1, 1).Resize(1, 2).Address 
End Sub

Hình 3 Phân biệt giữa hai thuộc tính Offset & Resize​

Hộp thoại của phần A trên hình là thuộc dòng lệnh thứ 2 &
hộp thoại của phần B thuộc dòng lệnh thứ 4
Khi không có thuộc tính Offset tác động ô xuất phát điểm
sẽ là ‘B2’ đang được kích hoạt;
Ngược lại, khi thuộc tính Offset(1,1) tác động, ô xuất phát điểm
sẽ là ô cách xuống dưới ô đang được kích hoạt 1 dòng & qua phải 1 cột.
(Nghĩa là ô đang kích hoạt là ‘B5’ thì trong trường hợp này Offset(1,1) sẽ là ‘C6’.
Và thuộc tính Resize(1,2) sẽ trả về vùng ‘C66’

Ví dụ 5

Ta xem xét đến sự liên quan của thuộc tính & vùng được đặt tên bằng macro sau:

Sub Resize0()
 If Range("nameRng").Columns.Count = 2 Then
    Range("nameRng").Resize(Range("nameRng").Rows.Count, 1).Name = "NameRng"
 End If
End Sub

Macro sẽ kiểm tra vùng được gán tên trước đó;
Nếu nó gồm 2 cột thì tên này sẽ gán cho vùng mới.
So với vùng gán tên cũ, vùng có tên mới có số dòng như trước,
nhưng số cột sẽ chỉ còn là 1 mà thôi

 

[SA_DQ]

Phần 2: Các ứng dụng có liên quan đến thuỗc tính Resize​

Thuộc tính Resize ứng dụng rất nhiều nơi, một khi chúng ta cần khảo sát về Ranges & Cells.
Sau đây xin giới thiệu một số trong chúng

1./ Dùng để chép tên vật tư, đơn vị tính & đơn giá vào hóa đơn bán hàng:
Đơn vị kia thường lên bảng kê danh mục hàng hóa gồm các trường chính:
Mã hàng hóa ([Ma]), tên hàng ([TenHH]), đơn vị tính ([DVT]) & đơn giá ([Dgia]) trên trang tính có tên là ‘DMHH’.
Còn trên trang ‘Hdon’ là biểu mẫu hóa đơn bán hàng (Xem hình)
Ta có macro sau để thực hiện việc chép các nội dung về tên hàng,
đơn vị tính & đơn giá một khi ta biết mã hàng cụ thể nào đó, như sau:

Option Explicit
Sub Resize4
 Dim lRow As Long, Ww As Long 
 lRow = Sheets("DMHH").[a65000].End(xlUp).Row 
'Biến lRow Sẽ Chứa Giá Trị Dòng Cuối Có Dữ Liệu Cùa Sheet DMHH'
 2 For Ww = 2 To lRow   
 With Cells(Ww, 1)4    
     If .Value = "V001" Or .Value = "V007" Or .Value = "V017" Then     
        .Offset(, 1).Resize(1, 3).Copy Destination:=Sheets("HDon"). _         
               Range("B" & [B99].End(xlUp).Row + 1)6    
     End If  
 End With
8 Next Ww 
End Sub

​

Hình 4: Chép dữ liệu từ danh mục vô hóa đơn bán hàng.​

Xin giải thích thêm về dòng lệnh số 4:
Trong khi dùng vòng lặp (từ dòng lệnh (2) đến dòng (8))
nếu gặp 1 trong 3 mã hàng ‘V001’, ‘V007’ & ‘V017’ nêu trên,
thì thực hiện việc copy giá trị chứa trong 3 ô bên phải kề nó
sang ô cần thiết của cột ‘B’ của trang mẫu hóa đơn.
Chỉ số dòng của ô cần thiết này được xác định bỡi thuộc tính End()
mà ta sẽ hay đã gặp trong eBook này.
Thuộc tính này giúp ta xác định dòng cuối chứa dữ liệu của 1 cột
(hay cũng có thể dùng để xác định cột cuối chứa dữ liệu của 1 hàng, . . .)

 

[SA_DQ]

2. Copy một vùng gồm là mảng dữ liệu hai chiều​

Chúng ta có macro, như sau:

 Dim iJ As Byte, iW As Byte, iZ As Byte, jJ As Byte 
Option Explicit
Sub Resize5()
[left]Dim Clls As Range 
Range("B9:F13").Clear: Range("B2:F6").Clear
Randomize : iJ = 10 - Int(9 * Rnd()):
iW = 20 - Int(iJ * Rnd()): iZ = 25 - Int(iW * Rnd()) 
For Each Clls In Range("B2:F6") 
jJ = 1 + jJ 
If jJ = iJ Or jJ = iW Or jJ = iZ Then 
Clls.Interior.ColorIndex = 5 
Next 
Clls [b2].Resize(5, 5).Copy Destination:=[b9] [/left]
 
End Sub

Đầu tiên macro sẽ tạo ra 3 số ngẫu nhiên phân bố từ 1 đến 25.
Tiếp theo ta khảo sát lần lượt 25 ô liên tục trong vùng ‘B2:F6’.
Trong quá trình duyệt lần lượt đó,
các ô có thứ tự trùng với 3 số ngẫu sẽ được tô nền màu xanh

Dòng lệnh cuối cùng trong macro sẽ Copy vùng chứa 3 ô tô
màu xanh ngẫu nhiên này xuống vùng dưới tương ứng. (Xem hình 5, bên trái)​

​

H. 5 Tô màu các ô trong vùng​

3. Tô màu 1 ô phụ thuộc vô màu hiện hữu của 8 ô xung quanh và của nó​

Macro dưới đây sẽ tô màu nền cho 9 ô trung tâm của hình các ô vuông trong
hình 5, phía phải . Màu nền được tô theo quy luật sau :​

Nếu 8 ô xung quanh nó & cả nó có số màu xanh là lẻ thì bản thân ô ​

đó sẽ được tô màu xanh, còn không thì để trắng.​

Để thực hiện đúng quy luật trên, chúng ta phải trải qua hai công đoạn :

a./ Lần lượt tính cho từng ô trung tâm, & ghi lưu kết quả vô biến mảng.

Ta phải ghi lại kết quả, chứ không thể thay ngay.
Vì nếu ghi ngay thì có thể ô sắp tới sẽ không còn được tính đúng
b./ Tô màu lần lượt ứng với giá trị đã lưu trong mảng ứng với ô đó​

Option Explicit:          Option Base 1
 
Sub Resize6()
2 Dim Rng As Range, Clls As Range, Rg0 As Range
Dim bW As Byte, bColr As Byte4 
ReDim MMau(9) As Byte 
For Each Clls In Range("C3:E5") 
6 bColr = bColr + 1 
For Each Rg0 In Clls.Offset(-1, -1).Resize(3, 3)
8 If Rg0.Interior.ColorIndex = 5 Then bW = 1 + bW 
Next Rg0
10 MMau(bColr) = bW Mod 2: bW = 0 
Next Clls12 
bColr = 0 
For Each Clls In Range("C3:E5")
14 bColr = bColr + 1 
If MMau(bColr) = 1 Then Clls.Interior.ColorIndex = 5
16 Next Clls
End Sub

Để giúp cho một số các bạn rõ hơn tác động của các dòng lệnh,
tôi xin phép lược dịch ra ngôn ngữ đời thường macro này:​

Dòng lệnh 2 & 3 ta khai báo một số biến cho phù hợp để xài lúc cần;​

Dòng 4 ta khai báo 1 biến mảng gồm 9 phần tử.

Mỗi phần tử sau này sẽ lưu giữ giá trị thể hiện tổng hợp số

màu nền của 9 ô thuộc nó (kể cả nó)​

Tiếp theo ta tạo ra vòng lặp (từ dòng 5 đến dòng 11)

để lưu giữ tổng số ô có màu nền xanh trong 9 ô của vùng trung tâm​

Dòng 6 ta tạo biến đếm để biết chúng ta đang khảo sát ô thứ bao nhiêu

trong 9 ô kết trên;​

Để liệt kê đúng theo quy tắc trên, ta phải lập vòng lặp (từ dòng 7 đến dòng 9)

nhằm duyệt lần lươt màu nền của 8 ô xung quanh & bản thân nó.
Khoanh vùng 9 ô này, không gì tốt hơn là thuộc tính Resize,
có sự hỗ trợ chút đĩnh của thuộc tính Offset.
Nhờ thuộc tính Offset(-1,-1), ta xác định ngay ô trái trên nhất của 9 ô này;
Ô này cách ô hiện hữu trên 1 hàng & cột kề nhất bên trái
Sau đó ta chỉ việc dùng ô này làm mốc,lấy xuống 3 hàng & lấy qua
phải 3 cột là được vùng theo quy định.
Dòng 8 trong vòng lặp thứ 2 này sẽ soát xét 9 lần với lần lượt 9 ô,
cứ ô nào có màu nền xanh sẽ cộng vô biến đếm màu nền bW 1 đơn vị;
Dòng lệnh thứ nhất của dòng 10 sẽ lưu vô biến mảng giá trị 1,
nếu tổng trong 9 ô là lẽ & 0 nếu ngược lại – là chẵn
(nhờ sự trợ giúp của hàm MOD trong VBA)​

Vòng lặp từ dòng 13 đến dòng 16 sẽ tô màu nền màu xanh,
ứng với giá trị lưu trong biến mảng thành phần của nó.
(Các bạn xem & đối chiếu trong hình 5)​

 

[SA_DQ]

4. Mở rọng vùng chọn cho tới dòng cuối

Option Explicit
Sub SelectExpandSelectionToLastUsedRow() 
 Dim lRow As Long, Rng As Range    
 If WorksheetFunction.CountA(Cells) > 0 Then 
    Set Rng = Selection 
    lRow = Cells.Find(What:="*", After:=Cells(1, 1), SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious).Row 
    Set Rng = Rng.Resize(lRow, Rng.Columns.Count)             
    lRow = Rng.Find(What:="*", After:=Rng.Cells(1, 1), SearchOrder:=xlByRows, _ 
        SearchDirection:=xlPrevious).Row 
    Rng.Resize(lRow - Rng.Cells(1, 1).Row + 1, Rng.Columns.Count).Select 
 End If 
End Sub

Giả dụ ta có trang tính như hình 6 (phía trái). Trước khi cho macro chạy,
ta dùng chuột chọn vùng ‘B33’. Sau khi thực thi macro ta sẽ có kết quả là vùng chọn mới.
Vùng chọn bây giờ có địa chỉ ‘B311”Dòng thứ 11 được chọn là do trong cột ‘C’,
đây đang là dòng cuối chứa dữ liệu. Cũng chính nó là dòng cuối nhất chứa dữ liệu trong 3
cột liên tiếp kể từ cột ‘B’ được chọn.

Hình 6 Mở rọng vùng chọn tới dòng cuối​

5 Chép nội dung dữ liệu từ trang tính này sang trang tính khác có cùng cấu trúc

Giả sử chúng ta có hai trang tính liệt kê nhân sự của hai đơn vị khác nhau trong công ty.
Một ngày đẹp trời nào đó, sếp ra lệnh lập danh sách hai đơn vị này giao cho sếp;
Nếu ta chưa biết rành về thuộc tính Resize, ta sẽ phải thực hiện các công đoạn sau:
· Tô chọn toàn bộ vùng dữ liệu của trang tính thứ nhất (không gồm phần tiêu đề)
(tham khảo hình 6 sẽ là vùng ‘A211’
· Thực hiện động tác copy
· Chuyển sang sheet thứ hai, tìm đến dòng cuối của dữ liệu
· Thực hiện động tác dán vô ô đầu dòng của hàng dưới hàng vừa tìm được
Nếu ta đã làm chủ thuộc tính đang xét, chúng ta viết 1 macro thực hiện tất thảy
các hành động này một cách lẹ làng, như sau:

Sub Resize8() 
 Dim Rng  As Range  
 Set Rng = Sheets("Sheet2").[a1].CurrentRegion 
 Rng.Offset(1).Resize(Rng.Rows.Count - 1).Copy _   
        Destination:=Sheets("Sheet1").Range("A" & [A65432].End(xlUp).Row + 1) 
 Set Rng = Nothing
End Sub

 

[SA_DQ]

Tiếp theo & hết.

6. Thuộc tính Resize làm việc với mảng dữ liệu

Chúng ta sẽ khảo sát mảng dữ liệu trong vùng ô từ ‘A9:E13’
trong hình 7 (vùng bên trái của hình). Nhiệm vụ đề ra là dựng 1 macro
để tách & chuyển mảng dữ liệu này sang mảng mới bên phải theo
một quy luật nhất định.
Quy luật này được thể hiện rõ trong hình với các mũi tên màu xanh.
Macro có nội dung sau:

Option Explicit
Sub Resize9() 
 Dim lRow As Long, jZ As Long, NumRow As Long 
 With Sheet1
     lRow = [A65432].End(xlUp).Row
     .Columns("M:Q").Clear
     .Cells(9, 13).Resize(, 5) = Array([a9], [b9], [c9], [d9], [e9])
     For jZ = 10 To lRow
           NumRow = WorksheetFunction.Sum(.Cells(jZ, 2).Resize(, 4))
          .Cells(65432, 13).End(xlUp).Offset(1).Resize(NumRow, 1) = .Cells(jZ, 1)
           If .Cells(jZ, 2) > 0 Then .Cells(65432, 14).End(xlUp) _ 
               .Offset(1).Resize(.Cells(jZ, 2), 4) = [{1,0,0,0}]
          If .Cells(jZ, 3) > 0 Then .Cells(65432, 14).End(xlUp) _ 
               .Offset(1).Resize(.Cells(jZ, 3), 4) = [{0,1,0,0}]
          If .Cells(jZ, 4) > 0 Then .Cells(65432, 14).End(xlUp) _ 
              .Offset(1).Resize(.Cells(jZ, 4), 4) = [{0,0,1,0}]
          If .Cells(jZ, 5) > 0 Then .Cells(65432, 14).End(xlUp) _
              .Offset(1).Resize(.Cells(jZ, 5), 4) = [{0,0,0,1}]
      Next jZ 
   End With
End Sub

Xin giải thích một số dòng lệnh trong macro sau:(a)

[COLOR=purple][FONT=Arial Unicode MS].Cells(9, 13).Resize(, 5) = Array([a9], [b9], [c9], [d9], [e9])                                 (1)[/FONT][/COLOR]

Chúng ta cũng có thể thay bằng lệnh:

[FONT=Arial Unicode MS].Cells(1, 12).Resize(, 5) = [{"Tên","W","X","Y","Z"}]        [/FONT] (2)

Nhưng ta cũng cần biết 1 điều rằng font chữ tiếng Việt không được hỗ trợ trong VBA;
Với từ ‘Tên’ có thể vẫn đúng, như với những từ khác chưa chắc đã hiện đúng,
mà thay vô đó sẽ là những từ mà ta tưởng excel lấy từ ngôn ngữ Ả rập!
Ngoài ra ta còn có thể dùng 1 lệnh thứ 3 khác nữa nhưng vẫn đạt kết quả tương tự.
Lệnh thứ ba này xin trân trọng nhường cho đọc giả vậy.(b)

[COLOR=purple][FONT=Arial Unicode MS]NumRow = WorksheetFunction.Sum(.Cells(jZ, 2).Resize(, 4))[/FONT][/COLOR]

Chúng ta thấy thêm một cách viết về hàm SUM() trong VBA có sử dụng thuộc tính Resize.
Câu lệnh này được diễn dịch như sau:
Tính tổng số liệu chứa trong 4 ô liên tiếp cùng hàng jZ kể từ cột ‘B’ đem gán vô biến NumRow

Hình 7 Resize & mảng dữ liệu

​

(c)

[COLOR=purple][FONT=Arial Unicode MS].Cells(65432, 13).End(xlUp).Offset(1).Resize(NumRow, 1) = .Cells(jZ, 1)[/FONT][/COLOR]

Lấy giá trị trong ô cột A có dòng jZ gán vô các ô tại cột thứ 13 (M) có dòng tăng





hơn dòng cuối chứa dữ liệu của cột này 1 dòng.

Số ô được gán thêm trị sẽ bằng với trị có trong biến NumRow.​

Cụ thể:
khi jZ=10 hay 11 thì NumRow chỉ là 1;
Khi jZ=12 & 13 thì NumRow =3 & 4 tương ứng(d)

[COLOR=purple][FONT=Arial Unicode MS]If .Cells(jZ, 2) > 0 Then .Cells(65432, 14).End(xlUp) _[/FONT][/COLOR]

[COLOR=purple][FONT=Arial Unicode MS]       [COLOR=purple][FONT=Arial Unicode MS].Offset(1).Resize(.Cells(jZ, 2), 4) = [{1,0,0,0}][/FONT][/COLOR][/FONT][/COLOR]

Nếu ô thuộc dòng jZ, cột ‘B’ hơn 0 thì các ô trong cột 14 bắt đầu từ dòng cuối
chứa dữ liệu của cột này tăng thêm 1 đơn vị,
mở rọng xuống n dòng (n – giá trị đang trong [jZ,2] & mở rọng sang phải 4 cột.
Cụ thể: Khi jZ= 12, thì mảng {1, 0, 0, 0} sẽ được chép 3 dòng.
Trên hình sẽ là các ô ‘N12:Q14’(e)
Ba dòng lệnh If tiếp sau (d) cũng tương tư như (d),
chỉ có điều khác là, thay vì cột ‘B’ sẽ là các cột ‘C’, ‘D’ & ‘E’ tương ứng.(f).
Cũng không thừa, một khi ta nói về cú pháp With .. . End With
Mà cụ thể ở đây là With Sheet1,
khi đó các dấu ‘.’ đứng trước các ký tự biểu diễn ô địa chỉ là đại diện
của đối tượng Sheet1 trong troàn bộ các dòng lệnh sau With Sheet1 & trước dòng End With