Bài viết: Phương thức SpecialCells trong Excel VBA (P1)

[levanduyet]

Phương thức SpecialCells trong Excel VBA

Tác giả: SA_DQ
Tham khảo tại đây.

Một trong những phương thức có ích nhất (theo kinh nghiệm bản thân) trong Excel là phương thức SpecialCells.
Trong Excel bạn hãy gõ Ctrl + G (hoặc F5) bạn sẽ thấy hộp thoại Go To sau:

Sau đó bạn tiếp tục nhấn nút Special... bạn sẽ được hộp thoại sau:

Hộp thoại này giúp bạn một số thao tác rất hữu ích như: tìm ô cuối cùng trong worksheet, hoặc tất cả các ô là số mà thôi,…hầu hết các thao tác này bạn đều có thể thực hiện bằng VBA, đó chính là việc dùng phương thức SpecialCells.
Khi được gọi, phương thức SpecialCells sẽ trả về một Đối tượng Range đặc trưng cho kiểu của những ô ta chỉ định. Ví dụ: ta có thể dùng phương thức SpecialCells để trả về một đối tượng Range chỉ chứa công thức. Và nếu muốn ta có thể giới hạn để đối tượng Range (chỉ chứa công thức) trả về công thức có lỗi (Tương ứng với hộp thoại Go To Special > Formulas > Errors).

Cú pháp của phương thức SpecialCells là:

expression.SpecialCells(Type, Value)

Trong đó expression là một biến đại diện cho một đối tượng Range.
Type: Kiểu dữ liệu là XlCellType.
XlCellType có thể là một trong những hằng số sau:

Chú ý: Không thể dùng kết hợp nhiều hơn một kiểu XlCellType cho tham số này.

Value: đối số này không bắt buộc, có kiểu là variant. Nếu Type có giá trị là xlCellTypeConstants hoặc xlCellTypeFormulas thì đối số này được dùng để xác định kiểu nào của các ô được trả về.
Các hằng của XlSpecialCellsValue, được thể hiện trong bảng sau:

Ví dụ sau sẽ chọn ô cuối cùng trong vùng đã được dùng trong Sheet1:

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate

Có thể kết hợp nhiều từ khóa XlSpecialCellsValue với nhau cho tham số này.
Phương thức SpecialCells có thể được sử dụng trong rất nhiều tình huống, khi ta chỉ cần thao tác với một kiểu dữ liệu đặc trưng của các ô trong vùng.
Ví dụ: câu lệnh dưới sẽ trả về một vùng đối tượng (Range) chỉ chứa đặc trưng là loại công thức trong trang tính hiện hành.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

Nếu muốn, ta có thể giới hạn để phương thức trả về đối tượng Range mang đặc trưng là các công thức trả về của chúng chỉ là giá trị số.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,xlNumbers)

Một khi ta đã chỉ định kiểu của đối tượng Range trả về, ta có thể thao tác với chỉ những ô đó. Điều này thường có thể được thực hiện chỉ bằng một dòng lệnh, hoặc ta thực hiện một vòng lặp Loop cho Range. Mời xem các ví dụ dưới đây để hiểu rõ hơn về phương thức này:

Ví dụ 1

Sub SpeacialCells0()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas). _
          Interior.ColorIndex = 35
End Sub

Vậy là macro chỉ gồm 1 câu lệnh đã tô màu cho cột chứa số thứ tự của hồ sơ, nơi mà ai đó đã nhập vô công thức tại A2 như sau:

 =IF(A1 = "TT”; 1; A1 + 1)

và công thức này đã được chép xuống đến ô 16. Như trong hình dẫn ra công thức tại ô ‘A13’.
Cần nói thêm rằng thuộc tính UsedRange mà trong câu lệnh có đề cập đến, chúng ta sẽ hay đã gặp trong Ebook này. Nếu chúng ta chưa gặp, thì sẽ được gặp & hiểu kỹ về nó sau, còn bây giờ ta tạm hiểu, thuộc tính sẽ trả về toàn bộ các ô mà ta đã dùng trên trang tính.

Ví dụ 2

Sub SpeacialCells1()
 Dim rRng As Range, rClls As Range
 Set rRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
 For Each rClls In rRng
       rClls = -1 * rClls.Value
 Next rClls
End Sub

Trong macro thứ hai ta đã dùng một vòng lặp để mọi giá trị số trên trang tính được chuyển trả về số đối âm của chúng.
Ta có thể dùng phương thức PasteSpecial để thực hiện điều này mà không cần dùng vòng lặp, khi đó các công thức được giữ nguyên trong các ô của chúng. Macro để thực hiện việc như vậy có nội dung như sau:

Sub SpeacialCells_()
  With Range("IV65536")
       .Value = -1:     .Copy
            ActiveSheet.UsedRange.SpecialCells _
                (xlCellTypeFormulas, xlNumbers).PasteSpecial _
                xlPasteValues, xlPasteSpecialOperationMultiply
        .Clear
   End With
End Sub

Nếu quen thuộc với Excel và những tính năng của nó, như SpecialCells, ta sẽ biết khi ta chỉ định chỉ một ô (bằng Selection hay Range) Excel sẽ cho rằng ta muốn thao tác với toàn bộ các ô của trang tính.

Ví dụ3 :
Hai macro dưới đây sẽ chọn toàn bộ các ô trống trong trang tính.

Sub ChonToanORong()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Select
End Sub

Sub ChonToanORong2()
    Range("A1").SpecialCells(xlCellTypeBlanks).Select  
End Sub

Ta có thể thấy khi chỉ định Range ở trường hợp sau có lúc sẽ cho kết quả không như ý.

Ví dụ 4:
Phương thức SpecialCells với các ô công thức và ô chứa dữ liệu
Chúng ta không thể kết hợp nhiều hơn một XlCellType (ví dụ xlCellTypeConstants + xlCellTypeFormulas). Ta có thể dùng phương thức SpecialCells để chỉ trả về một loại trong các ô đã dùng (công thức hay hằng số) và bỏ đi những ô chứa dữ liệu dạng văn bản (công thức & hằng số).

Sub AllNummericCells()
Dim ConClls As Range, ForClls As Range
Dim AllRng As Range
    Set AllRng = ActiveSheet.UsedRange
    On Error Resume Next   'Trường hợp không có công thức hay hằng số dạng số
     ' Mọi ô chứa số liệu dạng số được gán vô biến:
    Set ConClls = AllRng.SpecialCells(xlCellTypeConstants, xlNumbers)
    ' Các ô công thức dạng số được gán vô biến đã khai báo:
    Set ForClls = AllRng.SpecialCells(xlCellTypeFormulas, xlNumbers)
    ' Xác định kiểu của dữ liệu dạng số (công thức, hằng số hay khác):
    If ConClls Is Nothing And ForClls Is Nothing Then
       MsgBox "Trang Tinh Cua Ban Khong Chua Du Lieu So"
       End
    ElseIf ConClls Is Nothing Then
       Set AllRng = ForClls     'Công thức
    ElseIf ForClls Is Nothing Then
       Set AllRng = ConClls     'Số liệu thuần
    Else
       Set AllRng = Application.Union(ForClls, ConClls)     ' Cả hai
    End If
    On Error GoTo 0
    AllRng.Select
End Sub

Ta luôn nên ghi dòng lệnh

On Error Resume Next

như trong đoạn mã trên. Điều này rất cần thiết khi điều kiện của phương thức SpecialCells không thỏa và gây lỗi. Để bãy lỗi này ta kiểm tra biến kiểu Range mà ta đã gán có tồn tại hay không. Trong đoạn mã trên câu lệnh If (với 2 ElseIf) kiểm tra điều này.

Ví dụ 5:
Tiện lợi rất nhiều về thời gian một khi ta dùng phương thức này thay cho cách dùng vòng lặp như thông thường
Ta xét 2 macro sau:

Sub DoLoop()
Dim Blclls As Range
   For Each Blclls In Range("b2:F350")
    If IsEmpty(Blclls) Then Blclls = "Rong"
   Next Bcell
End Sub

Macro này sẽ phải lần lượt duyệt qua toàn bộ các ô trong vùng chọn ("B2:F350"). Khi gặp ô nào không chứa dữ liệu (rỗng) thì nó điền thay vô đó từ "Rong”.

Còn macro dưới đây thì tiếp cận với các ô rỗng theo phương thức chọn toàn bộ các ô rỗng cùng một lúc & chỉ bằng 1 dòng lệnh duy nhất.

Sub SpecialCells5()
    If WorksheetFunction.CountA(Range("b2:F350")) = 0 Then
       MsgBox "TOAN BO O LA RONG", vbOKOnly, "GPE.COM"
       Exit Sub
    End If
    On Error Resume Next
    Range("b2:F350").SpecialCells(xlCellTypeBlanks) = "Rong"
End Sub

Ví dụ 6:
Tô màu các ô chứa giá trị & các ô công thức cùng nhỏ hơn mộtr giá trị nào đó.

Macro mà chúng ta dùng để tô màu nền các ô chứa các trị bế hơn 10 & kể cả các ô chứa công thức, mà kết quả công thức trả về cũng là những giá trị bé hơn 10. Nó gồm các dòng lệnh sau:

Sub BackColor()
Dim ConClls As Range, ForClls As Range, AllClls As Range, Clls As Range
Set AllClls = ActiveSheet.UsedRange:    AllClls.Select
On Error Resume Next
Set ConClls = AllClls.SpecialCells(xlCellTypeConstants, xlNumbers)
Set ForClls = AllClls.SpecialCells(xlCellTypeFormulas, xlNumbers)
    If ConClls Is Nothing And ForClls Is Nothing Then
       MsgBox "Trang Tinh Không Chứa Số":    End
    ElseIf ConClls Is Nothing Then
       Set AllClls = ForClls     'Công thức
    ElseIf ForClls Is Nothing Then
       Set AllClls = ConClls     ' Số Trị
    Else
       Set AllClls = Application.Union(ForClls, ConClls)     'Cả Hai
    End If
    On Error GoTo 0
    For Each Clls In AllClls
      With Clls
          If .Value < 10 Then   .Interior.ColorIndex = 36
       End With    
 Next Clls
End Sub

Những gì mà macro vừa thực hiện, được hiển thị trên hình 2:

Tại cột ‘A’, là những giá trị do các công thức tại A(j+1) = IF(A(j) = "TT”; 1; A(j)1 + 1).
Tại cột ‘E’, những giá trị bé hơn 10 đều được tô màu vàng nhạt

Ví dụ 7:
Về ô cuối cùng chứa dữ liệu
Nếu ta viết macro có nội dung sau:

Sub LastCells()
          Range("A1").SpecialCells(xlCellTypeLastCell).Select
    MsgBox Selection.Address
End Sub

Thì hộp thoại sẽ đưa ra địa chỉ ô, mà ô này chưa chắc phải là ô cuối cùng chứa dữ liệu trong vùng sử dụng.
Để trả lời vấn đề này, ta đọc lại phần đầu của đề mục viết về tham số xlCellTypeLastCell.
Vậy khi ta muốn tìm tới ô cuối cùng chứa dữ liệu của trang tính thì làm sao đây? Lúc đó ta phải dùng macro như sau để đạt mục đích:

Sub FindLastCell()
Dim LastColumn As Integer:    
Dim LastRow As Long
Dim LastCell As Range
   If WorksheetFunction.CountA(Cells) > 0 Then
      LastRow = Cells.Find(What:="*", After:=[A1], _
               SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastColumn = Cells.Find(What:="*", After:=[A1], _
               SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            MsgBox Cells(LastRow, LastColumn).Address
   End If
End Sub

Với cơ sở dữ liệu như trong h.3, ta sẽ thấy hộp thoại của macro đưa ra địa chỉ như trong hình đã dẫn.

Phần 2: Các ứng dụng có liên quan đến phương thức SpecialCells

1. Xóa các dòng rỗng trong cơ sở dữ liệu

Sub DeleteBlankRows1()
'Xóa dòng rỗng trong vùng đã chọn
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub DeleteBlankRows2()
'Cũng xóa dòng rỗng trong vùng đã chọn
Dim BlRng As Range
If WorksheetFunction.CountA(Selection) = 0 Then
   MsgBox "Khong Tim Ra Du Lieu", vbOKOnly, "GPE.com"
   Exit Sub
End If
With Application
   .Calculation = xlCalculationManual
   .ScreenUpdating = False
   Selection.SpecialCells(xlCellTypeBlanks).Select
   For Each BlRng In Selection.Rows
      If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
         Selection.EntireRow.Delete
      End If
   Next BlRng
   .Calculation = xlCalculationAutomatic
   .ScreenUpdating = True
 End With
End Sub

2. Cẩn thận với xlCellTypeVisible khi viết trong hàm người dùng

Nếu chúng ta có ý tưởng viết hàm người dùng để nó đếm cho ta số ô trong vùng nào đó, ví dụ bằng cách thức sau:

Public Function KhongHieuNoi(Rng As Range)
    KhongHieuNoi = Rng.SpecialCells(xlCellTypeVisible).Cells.Count
End Function

Thì hàm này hoàn toàn đúng về cú pháp, cũng như biên dịch ra ngôn ngữ máy, . . . Thậm chí trong cửa sổ Immediate, ta nhập câu lệnh sau:

?khonghieunoi(sheet3.UsedRange)

Sau khi {ENTER}, chúng ta sẽ nhận được 1 kết quả rất chi là chính xác nữa là đằng khác.
Nhưng vấn đề sẽ khác, một khi ta xét hàm người dùng này & được đối chiếu với 1 macro có những câu lệnh tương tư. Muốn vậy, ta tạo 1 vùng nào đó trên trang tính & gán cho nó cái tên ‘DSach’ (Trong hình 4 là vùng ‘A1:B21’). Tại ô ‘D3’ ta nhập công thức:

=KhongHieuNoi(DSach)

Sau khi {ENTER} ta được giá trị 42. Giá trị này vẫn tồn tại cho dù bạn có ẩn vài hàng hay ẩn đi 1 cột, hoặc thậm chí ẩn cả hai cột ‘A’ & ‘B’ đi chăng nữa.
Sự thật phủ phàng này (vẫn như cũ, luôn xuất hiện số 42) nên ghi nhớ mãi, một khi làm việc với những ô ‘trông thấy’ trong các hàm tự tạo trong excel.
Ngược lại với hàm người dùng như trên, với macro HieuKhong() sau đây vấn đề sẽ hoàn toàn khác

Sub HieuKhong()
 On Error Resume Next
 [D4]= Sheet3.[DSach].SpecialCells(xlCellTypeVisible).Cells.Count
End Sub

Cặp mắt của macro này giống với cặp mắt của chúng ta, và kết quả của hàm hay của macro ghi ra không trùng nhau, như hình dưới đây:

Một số bài viết có liên quan:
1/ Làm cách nào để ghi chú hiệu quả trong VBA?
2/ Conditional Formatting cho biểu đồ bằng VBA
3/ Khi nào nên sử dụng Msgbox, Inputbox và Userform?
4/ 8 thủ thuật trong VBE bạn nên biết
5/ Kích hoạt macro từ nút bấm ngoài bảng tính
6/ Làm thế nào để thay thế các chữ OK, CANCEL,... nhàm chán của Msgbox
7/ Giới thiệu VBA trong Excel
8/ 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
9/ 4 cách sử dụng Immediate Window trong VBA hiệu quả hơn
10/ 3 gợi ý nhỏ mang lại thành công trong khai báo biến trong VBA
http://www.giaiphapexcel.com/vbb/content.php?561