Hướng dẫn và đào tạo Excel và VBA. Tìm hiểu cách sử dụng Microsoft Excel và Visual Basic cho Ứng dụng ngay bây giờ.
Excel VBA xóa các dòng trống hoặc rỗng: 5 ví dụ macro dễ sử dụng
Bạn có thể nhận thức được những phiền nhiễu gây tốn nhiều thời gian khi làm sạch dữ liệu. Theo tờ New York Times cho biết, theo các cuộc phỏng vấn và ước tính của chuyên gia, các nhà khoa học dữ liệu thì việc làm sạch dữ liệu bằng các cách thủ công “chi tiêu từ 50% đến 80% thời gian của họ và họ có thể bị sa thải trong công việc thu thập và chuẩn bị dữ liệu kỹ thuật số” bằng những cách như vậy.
Như bạn có thể biết, quá trình làm sạch dữ liệu với Excel có thể được tự động hóa ở một mức độ nhất định bằng cách sử dụng các công cụ chức năng Văn bản sắn có hoặc macro.
Trong hướng dẫn này, tôi tập trung vào một tình huống rất "Nghiêm trọng", khó chịu và phổ biến khi làm việc với dữ liệu: sự tồn tại của các hàng trống và các ô trong tập hợp dữ liệu. Theo giải thích của Michael Alexander, tác giả của một số cuốn sách về phân tích kinh doanh với Access và Excel, trong 101 Sẵn sàng để sử dụng Excel Macro:
Làm việc với Excel đủ lâu và bạn sẽ thấy rằng các hàng trống có thể gây ra sự cố trên nhiều cấp độ.
Excel Trick's Ankit Kaul đề cập đến cách các hàng trống có thể làm cho cuộc sống của bạn đau đàu khi sử lý công việc trên một tập hợp dữ liệu khổng lồ, ví dụ, bạn dự định nhập một trang tính Excel vào các ứng dụng khác, chẳng hạn như Access.
Điểm chính là: bạn muốn có thể xóa các hàng trống hoặc hàng có ô trống nhanh chóng và dễ dàng. Bạn có thể làm điều này, trong khi đó một số những người khác có thể là không, bằng cách sử dụng macro.
Có một số cách để tạo macro xóa hàng trống hoặc hàng có ô trống. Trong thực tế, bạn có thể tìm thấy nhiều đề xuất khác nhau trên Internet.
Trong hướng dẫn này, tôi chỉ cho bạn 5 ví dụ phổ biến nhất của macro để xóa các hàng hoặc hàng trống với các ô trống một cách nhanh chóng và dễ dàng. Bằng chính mã VBA, mỗi macro được kèm theo một lời giải thích rất cụ thể về cách mỗi ứng dụng được cấu trúc và mục đích của mỗi câu lệnh là gì. Những giải thích kỹ lưỡng này sẽ giúp bạn hiểu những gì vĩ mô thực sự đang làm và, đồng thời, cải thiện sự hiểu biết của bạn về macro và Visual Basic cho các ứng dụng.
5 macro mà tôi giải thích và khám phá trong hướng dẫn này đạt được những điều sau đây:
3 macro đầu tiên xóa một hàng hoàn chỉnh dựa trên việc nó có ô trống hay không. Chính xác hơn:
Macro # 1, có tên là Delete_Blank_Rows, xóa toàn bộ các hàng dựa trên việc một ô của hàng đó trong một phạm vi nhất định có ô trống.
Macro # 2, có tên là Delete_Blank_Rows_2, đạt được mục đích tương tự như Macro # 1 nhưng sử dụng một cấu trúc khác. Trong khi Macro # 1 sử dụng phương thức Select và thuộc tính Selection (cả hai đều giải thích bên dưới), Macro # 2 được cấu trúc xung quanh câu lệnh With… End With (mà tôi cũng giải thích bên dưới).
Macro # 3, có tên là Delete_Blank_Rows_3, xóa toàn bộ các hàng dựa trên việc một ô của hàng đó trong vùng chọn hiện tại có ô trống.
Sự khác biệt chính giữa Macro # 3 so với Macros # 1 và # 2 là Delete_Blank_Rows_3 hoạt động với dải ô mà bạn đã chọn ngay trước khi thực thi macro. Delete_Blank_Rows và Delete_Blank_Rows_2 hoạt động trên một phạm vi ô được cố định trong mã VBA. Về cơ bản, Macros # 1 và # 2 làm việc với các tham chiếu tuyệt đối trong khi Macro # 3 làm việc với các tham chiếu tương đối.
2 macro cuối cùng xóa các hàng khi toàn bộ hàng trống. Sự khác biệt chính giữa Macro # 4 (có tên là Delete_Empty_Rows) và Macro # 5 (có tên là Delete_Empty_Rows_2) là cách thức chúng được cấu trúc.
Bạn có thể sử dụng phác thảo sau để bỏ qua macro (hoặc phần) mà bạn quan tâm:
Vì có một số cách để bạn có thể cấu trúc macro để xóa các hàng hoặc hàng trống với các ô trống, ở cuối hướng dẫn này, tôi giới thiệu một số cách mà bạn có thể sửa đổi bất kỳ macro nào xuất hiện ở đây để trở thành một tiện ích tốt hơn để đạt được các mục tiêu cụ thể của bạn. Ngoài ra, vì tôi biết rằng có rất nhiều cách khác để tiếp cận vấn đề, tôi khuyến khích bạn chia sẻ kiến thức và kinh nghiệm của bạn trong phần bình luận ở cuối bài viết này.
Hãy bắt đầu bằng cách xem xét dữ liệu mẫu mà tôi sử dụng để minh họa cách xóa các hàng trống hoặc hàng với các ô trống trong Excel một cách nhanh chóng và dễ dàng bằng cách sử dụng các macro…
Macro để xóa hàng trống hoặc hàng với ô trống: Dữ liệu mẫu
Như thường lệ, mọi thứ tôi giải thích trong hướng dẫn macro Excel này được minh họa bằng một ví dụ thực tế. Trong trường hợp này, tôi sử dụng dữ liệu thị trường chứng khoán.
Chính xác hơn, tôi đã sử dụng Google Finance để lấy dữ liệu cổ phiếu lịch sử cho mỗi ngày giao dịch của năm 2014 của các công ty sau:
Microsoft.
Google.
Apple.
Amazon.
Dữ liệu đã được dán trong một cửa sổ làm việc Excel. Excel VBA này xóa dòng trống hoặc các dòng có ô trống hướng dẫn được kèm theo của sổ làm việc Excel chứa dữ liệu và các macro mà tôi sử dụng. Bạn có thể truy cập miễn phí ngay lập tức vào các file làm việc mẫu này bằng cách đăng ký Bản tin trên trang.
Bảng gốc với tất cả các thông tin trông gần như sau (đây chỉ là một ảnh chụp màn hình một phần):
Như bạn có thể thấy, đây là một bảng khá lớn:
Có 4 công ty.
Đối với mỗi công ty, có 252 ngày dữ liệu.
Đối với mỗi ngày và công ty, có 5 mẩu dữ liệu: 4 giá cổ phiếu (mở, cao, thấp, đóng) và khối lượng.
Hãy tưởng tượng rằng bạn đang làm việc với tập dữ liệu này và tìm ra rằng có thể có một số ô trống hoặc các hàng trống và bạn cần xóa những hàng bị ảnh hưởng. Như bạn có thể hình dung, việc làm sạch bộ dữ liệu đó theo cách thủ công có thể vô cùng tẻ nhạt và có một nguy cơ đáng kể về việc mắc lỗi, ví dụ, thiếu một số ô trống.
May mắn thay, bằng cách sử dụng macro tương đối đơn giản, bạn có thể lấy các tập dữ liệu rất lớn và xóa toàn bộ các hàng có ô trống trong chúng hoặc hoàn toàn trống. Hãy bắt đầu xem xét các macro này…
Macro # 1: Xóa hàng dựa trên bất kỳ ô nào trong phạm vi đã cho là trống (tùy chọn 1)
Giả sử bạn đang sẵn sàng phân tích hiệu suất của các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014. Ông chủ của bạn cần có ý tưởng sơ bộ về cách những cổ phiếu này thực hiện trong năm 2014 trước một cuộc họp mà anh ấy có trong nửa giờ .
Bạn đổ xô vào máy tính của bạn, mở sổ làm việc Excel có chứa tất cả thông tin nhưng…
Bạn nhận thấy rằng ai đó trong văn phòng của bạn đã nhận được dữ liệu trước khi bạn và vô tình xóa một số giá đóng cửa của giá cổ phiếu của Microsoft. Để chính xác, thay vì 252 ngày dữ liệu, bạn chỉ có 230 ngày dữ liệu trong cột cụ thể này.
Trong khi đó kết nối Internet của bạn không hoạt động, do đó bạn sẽ không thể thay thế dữ liệu (bằng cách nhận nó từ Google Finance) vào thời điểm trước cuộc họp của ông chủ.
Sau khi đi đến bàn của đồng nghiệp, người vô tình xóa dữ liệu và la mắng anh ta, bạn quay trở lại căn phòng của bạn. Bạn không thực sự chắc chắn làm thế nào để giải thích cho ông chủ của bạn rằng bạn sẽ không thể cung cấp các dữ liệu cần thiết trước khi cuộc họp của mình diễn ra.
Nhưng sau đó…
Bạn bắt đầu hỏi một số câu hỏi hay hơn. Ví dụ, bạn tự hỏi mình:
Tôi có thể dọn sạch dữ liệu và sửa sổ làm việc để dữ liệu bị thiếu không gây ra sự cố không?
Dữ liệu bị thiếu có thực sự quan trọng không? Ông chủ của tôi chỉ muốn có được một ý tưởng thô về hiệu suất chia sẻ trong năm 2014. Tôi có thể cung cấp chỉ sử dụng dữ liệu có sẵn không?
Tôi không thực sự ủng hộ rằng bạn làm điều này thường xuyên. Nhưng trong trường hợp đặc biệt này, nó có thể hoạt động và chắc chắn tốt hơn là cung cấp cho sếp của bạn không có dữ liệu nào cả.
Tôi có thể cung cấp cho sếp của tôi một phân tích xem xét những ngày mà tôi có tất cả thông tin không?
Chắc chắn. Bạn có thể làm điều này bằng cách xóa các hàng mà bạn không có tất cả thông tin và sau đó thực hiện phân tích của bạn.
Làm thế nào tôi có thể xóa các hàng mà tôi không có tất cả thông tin? Chính xác hơn, làm thế nào tôi có thể xóa một hàng đầy đủ tùy thuộc vào việc một ô trong cột mà giá đóng của Microsoft chia sẻ có trống không?
Có một số cách để đạt được mục tiêu này, chẳng hạn như sử dụng 3 macro mà tôi giải thích dưới đây (Macro # 1, Macro # 2 và Macro # 3 của hướng dẫn này).
Nếu bạn biết phạm vi mà bạn muốn tìm kiếm ô trống và, dựa trên kết quả đó, hãy xóa toàn bộ hàng, macro dưới đây (được đề xuất bởi rs2k tại Mr.Excel.com) có thể hữu ích.
Macro cụ thể này xóa toàn bộ hàng nếu có bất kỳ ô trống nào trong phạm vi được chỉ định trong mã VBA. Trong ví dụ này, bạn có thể sử dụng macro để xóa tất cả các hàng mà giá đóng của Microsoft chia sẻ bị thiếu.
Các câu lệnh chính của macro cụ thể này như sau:
Lỗi khi tiếp tục lại
Phạm vi (“E6: E257”).
Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Tôi đặt tên macro là “Delete_Blank_Rows”, gán phím tắt “Ctrl + Shift + D” và thêm mô tả ngắn gọn và lấy mã VBA sau:
Chúng ta hãy xem xét vĩ mô từng dòng để hiểu quá trình.
Dòng # 1: Khi lỗi tiếp tục lại
Theo giải thích của Microsoft ở đây, trên báo cáo lỗi làm như sau:
Bật quy trình xử lý lỗi.
Chỉ định vị trí của thủ tục trong quy trình.
Lý do chính để sử dụng báo cáo Lỗi là để bẫy lỗi, xử lý chúng và cải thiện trải nghiệm người dùng. Nếu xảy ra lỗi thời gian chạy và không có báo cáo Lỗi trên thì lỗi sẽ gây tử vong. Thực thi bạn dừng macro và Excel sẽ hiển thị thông báo lỗi.
Theo giải thích của John Walkenbach, một trong những cơ quan chức năng nổi bật nhất của Excel, trong Lập trình Dummies của Excel VBA:
Sử dụng câu lệnh On Error trong mã VBA cho phép bạn bỏ qua việc xử lý lỗi tích hợp của Excel và sử dụng mã xử lý lỗi của riêng bạn.
Câu lệnh đầy đủ ở trên là trình xử lý lỗi Tiếp tục Lỗi khi tiếp tục. Các loại xử lý lỗi cụ thể này thường được sử dụng, trong số những người khác, khi bạn đang gặp lỗi và biết cách xử lý chúng. Theo giải thích của Microsoft ở đây, trình xử lý lỗi On Error Resume Next hoạt động bằng cách hướng dẫn Excel tiếp tục thực hiện macro với câu lệnh sau đó xảy ra lỗi.
Lý do chính tại sao trình xử lý lỗi On Error Resume Next phải được bao gồm trong macro Delete_Blank_Rows là có, thực sự, lỗi được mong đợi khi thực thi macro. Đặc biệt, nếu phạm vi mà macro tìm kiếm các ô trống không thực sự có bất kỳ ô trống nào, sẽ có lỗi thời gian chạy và Excel sẽ ngừng thực thi macro trừ khi có lỗi xử lý lỗi thích hợp.
Trên các báo cáo lỗi thường áp dụng cho tất cả các câu lệnh tiếp theo trong mã VBA cho đến khi câu lệnh bị vô hiệu hóa bởi câu lệnh On Error GoTo 0. Nếu không có câu lệnh On Error GoTo 0, câu lệnh On Error chỉ bị vô hiệu hóa khi thủ tục được thoát.
Dòng số 2: Phạm vi (“#: ##”).
Phạm vi ("#: ##") trả về một đối tượng đại diện cho một phạm vi ô (hoặc một ô đơn lẻ). “#: ##” là đối số mà bạn sử dụng để đặt tên cho dải ô.
Chọn kích hoạt đối tượng cụ thể để, trong bước tiếp theo, bạn có thể sử dụng thuộc tính Selection.
Trong ví dụ mà bạn cần tìm kiếm các ô trống trong cột nơi giá đóng của cổ phiếu của Microsoft là phạm vi liên quan là từ ô E6 đến ô E257 như được hiển thị trong 2 ảnh chụp màn hình bên dưới. Do đó, câu lệnh VBA có liên quan cho macro Delete_Blank_Rows là “Phạm vi (“ E6: E257 ″). Chọn ”.
Dòng # 3: Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Câu lệnh này dài hơn một chút so với các câu lệnh trước, vì vậy hãy kiểm tra từng phần tử riêng biệt của nó.
Yếu tố # 1: Selection.SpecialCells (xlCellTypeBlanks).
Đây được gọi là phương thức Range.SpecialCells. Mục đích của phương thức Range.SpecialCells là lấy một đối tượng (trong trường hợp này là một phạm vi) biểu diễn các ô phù hợp với kiểu và giá trị được đặt trong dấu ngoặc đơn.
Để hiểu định nghĩa này, chúng ta hãy xem cú pháp thông thường của phương thức Range.SpecialCells: “expression.SpecialCells (Type, Value)”, trong đó:
"Expression" là viết tắt của đối tượng Range. Trong trường hợp này đối tượng dải là Lựa chọn, trả về lựa chọn hiện tại. Trong macro Delete_Blank_Rows, điều này thể hiện phạm vi ô đã được chọn theo câu lệnh trước (Phạm vi (“#: ##”). Chọn).
Trong bảng tính Excel có chứa giá cổ phiếu của Microsoft, Google, Amazon và Apple, Lựa chọn đại diện cho phạm vi giữa các tế bào E6 và E257.
Dấu ngoặc đơn ngay lập tức sau khi SpecialCells bao gồm 2 đối số: XlCellType và giá trị.
XlCellType là đối số bắt buộc chỉ định loại ô nào sẽ được bao gồm. Macro Delete_Blank_Rows yêu cầu các ô được bao gồm trong phạm vi được trả về bởi phương thức là xlCellTypeBlanks. xlCellTypeBlanks, được ngụ ý bởi tên của chúng, các ô trống.
Giá trị là một đối số tùy chọn thường được yêu cầu khi loại là xlCellConstants (ô có chứa hằng số) hoặc xlCellTypeFormulas (các ô có công thức). Macro Delete_Blank_Rows không bao gồm giá trị, có nghĩa là tất cả các ô trống đều được trả về.
Khi áp dụng cho bảng tính Excel với dữ liệu cổ phiếu cho Microsoft, Google, Amazon và Apple, câu lệnh cụ thể này trả về một đối tượng đại diện cho tất cả các ô trống trong phạm vi giữa các ô E6 và E257 (cột nơi giá đóng của cổ phiếu của Microsoft xuất hiện ).
Yếu tố # 2: EntireRow.
Thuộc tính EntireRow trả về một đối tượng, như bạn có thể mong đợi, là toàn bộ một hàng (hoặc một vài hàng).
Cú pháp của thuộc tính EntireRow là “expression.EntireRow”. Trong trường hợp này, "biểu thức" phải cung cấp một đối tượng.
Trong trường hợp của macro Delete_Blank_Rows, biểu thức là Selection.SpecialCells (xlCellTypeBlanks). Kết quả là, trong ví dụ tôi đã sử dụng, thuộc tính EntireRow trả về tất cả các hàng (toàn bộ) chứa các ô trống trong phạm vi ô từ E6 đến E257 (nơi giá đóng của cổ phiếu của Microsoft).
Yếu tố # 3: Xóa.
Phương thức Delete (“Delete”) xóa một đối tượng. Trong ví dụ trên, đối tượng bị xóa là toàn bộ các hàng chứa ô trống trong cột nơi giá đóng của Microsoft được hiển thị.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows
Bây giờ tôi đã cho bạn thấy mỗi câu lệnh trong macro Delete_Blank_Rows làm gì, bạn có một sự hiểu biết tốt hơn về cách Macro # 1 hoạt động. Như bạn có thể biết bây giờ, những gì vĩ mô này làm trong một nutshell là như sau:
Bước # 1: Macro chọn một phạm vi ô mà bạn đã xác định.
Trong ví dụ trên, phạm vi ô là cột mà giá đóng của cổ phiếu của Microsoft xuất hiện hoặc chính xác hơn, các tế bào E6 đến E257.
Bước # 2: Macro đi qua phạm vi được chọn để tìm tất cả các ô trống.
Bước # 3: Bất cứ khi nào macro tìm thấy ô trống, nó sẽ chọn toàn bộ hàng.
Bước 4: Khi macro đã tìm thấy tất cả các ô trống trong phạm vi có liên quan và đã chọn toàn bộ các hàng có các ô đó, nó sẽ xóa các hàng.
Macro bao gồm trình xử lý lỗi Tiếp tục Lỗi khi tiếp tục vì mục đích đảm bảo rằng việc thực thi chương trình không dừng, ví dụ: trong trường hợp macro không tìm thấy bất kỳ ô trống nào trong phạm vi mà bạn đã yêu cầu tìm kiếm trong đó. Lỗi cụ thể này không có tác dụng phụ khi thực hiện macro và do đó, có thể được để lại “không được cố định”.
Đồ họa, quá trình tiếp theo là macro Delete_Blank_Rows trông gần như sau:
Cuối cùng, chúng ta hãy quay trở lại ví dụ nơi bạn đang chuẩn bị một bản tóm tắt về hiệu suất của các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014 cho sếp của bạn. Hãy xem hình ảnh dưới đây để xem cách macro Delete_Blank_Rows hoạt động trong thực tế và quan trọng nhất là cách nó có thể giúp bạn hoàn thành công việc của mình nhanh hơn và giữ cho sếp của bạn vui vẻ 😉. Lưu ý cách các hàng có các ô trống trong cột E (giá đóng của cổ phiếu Microsoft), chẳng hạn như các hàng 11 và 20, sẽ bị xóa.
Macro # 2: Xóa hàng dựa trên bất kỳ ô nào trong phạm vi đã cho là trống (tùy chọn 2)
Chúng ta hãy xem lại mã VBA phía sau Delete_Blank Rows_Macro:
Và cho phép tôi hỏi bạn một câu hỏi:
Bạn sẽ sửa đổi mã VBA như thế nào để tránh sử dụng phương thức Select và thuộc tính Selection?
Nếu bạn xem xét kỹ 2 câu lệnh chính trong mã VBA của macro Delete_Blank_Rows, bạn có thể nhận thấy cả “Chọn” và “Lựa chọn” đều tham chiếu đến cùng một điều: phạm vi ô, trong ví dụ cụ thể này, là “Phạm vi (“ E6: E257 ″) ”.
Do đó, theo gợi ý của erik.van.geit tại Mr.Excel.com, bạn có thể sử dụng câu lệnh With With ... End With. Mục đích của câu lệnh With… End With là đơn giản hóa cú pháp của macro bằng cách thực hiện một số câu lệnh, tất cả đều tham chiếu đến cùng một đối tượng hoặc cấu trúc. Bạn có thể tìm thấy một ví dụ về một câu lệnh With… End With rất đơn giản trong hướng dẫn macro Excel này cho người mới bắt đầu.
Nếu bạn đang tự hỏi tại sao nó có thể được khuyến khích để tránh sử dụng phương pháp Select và tài sản lựa chọn, tôi cung cấp cho bạn một số lý do ở phần cuối của phần này.
Trong trường hợp này, các báo cáo có liên quan nhất của macro là như sau:
Chúng ta hãy xem xét từng dòng mã để hiểu quy trình tiếp theo là macro này là gì.
Dòng # 1: Khi lỗi tiếp tục lại
Bạn đã biết rằng tuyên bố này:
Là một lỗi Tiếp tục xử lý lỗi tiếp theo.
Hướng dẫn Excel tiếp tục thực thi macro với câu lệnh theo sau điểm xảy ra lỗi thời gian chạy.
Có mục đích chính, trong các macro đã được nghiên cứu trong hướng dẫn này, để tránh việc thực hiện macro dừng trong trường hợp không có ô trống trong phạm vi mà bạn đã yêu cầu macro tìm kiếm trong đó.
Dòng 2. Với phạm vi (“#: ##”)
Từ “With” báo hiệu sự bắt đầu của câu lệnh With… End With. Trong các kiểu câu lệnh này, từ With thường được theo sau bởi một đối tượngExpression, được ngụ ý bởi tên của nó, “một biểu thức đánh giá một đối tượng“. Trong trường hợp này, objectExpression là Range (“#: ##”).
Hình ảnh dưới đây cho thấy toàn bộ Với ... Kết thúc với tuyên bố:
Như một hệ quả của các bên trên, Range (“#: ##”) là đối tượng mà mỗi dòng trong câu lệnh With… End With tham chiếu đến. “#: ##” là nơi bạn xác định phạm vi thực tế mà các câu lệnh sẽ hoạt động.
Tôi tiếp tục làm việc với ví dụ nơi bạn đang cố gắng sửa bảng tính Excel có giá hàng ngày cho các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014 và cần xóa một số hàng nhất định tùy thuộc vào việc giá đóng cửa của cổ phiếu của Microsoft có xuất hiện hay không hay không. Trong trường hợp này, bạn biết rằng "#: ##" nên được thay thế bằng "E6: E257".
Dòng số 3: .Value = .Value
Đây là một ví dụ về thuộc tính Range.Value đang hoạt động. Thuộc tính này trả về hoặc đặt giá trị cho một dải ô được chỉ định.
Vì dòng nằm trong câu lệnh With… End With, nó tham chiếu đến phạm vi ô giữa E6 và E257.
bạn có thấy điều gì đang xảy ra ở đây không? Tại sao tuyên bố bao gồm .Value trên cả hai mặt của dấu bằng?
Như bạn có thể hình dung, hiệu ứng của kiểu câu lệnh này là thiết lập các giá trị của phạm vi bằng với các giá trị hiện tại của chúng. Trong Excel, điều này rất hữu ích khi bạn đang tìm kiếm các ô trống hoặc cho các ô xuất hiện trống nhưng thực sự có dấu ngoặc kép (“”). Theo giải thích của Microsoft ở đây, các ô trống vì chúng chứa dấu ngoặc kép (“”) thường hoạt động khác với các ô hoàn toàn trống.
Điều này có nghĩa rằng, khi một macro đang tìm kiếm các ô trống, nó sẽ không nhìn vào các ô có dấu ngoặc kép (“”) là rỗng. Do đó, các tế bào đó không được xem xét trong quá trình xóa. Như được giải thích bởi nguồn macro này, erik.van.geit tại diễn đàn của ông Excel, câu lệnh “.Value = .Value” chuyển đổi các ô có dấu ngoặc kép thành khoảng trống thực tế. Những khoảng trắng sau đó có thể được sử dụng bởi các macro cho mục đích xác định những hàng cần xóa.
Dòng # 4: .SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Vì dòng này nằm bên trong câu lệnh With… End With, nó làm cho tham chiếu đến đối tượngExpression xuất hiện khi khai báo. Do đó, trong trường hợp này, dòng mã này hoạt động với Range (“#: ##”).
Nói cách khác, dòng mã này nói “Range (“ #: ## ”). SpecialCells (xlCellTypeBlanks) .EntireRow.Delete”. Điều này, về bản chất, giống như “Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete” xuất hiện trong Macro # 1. Sự khác biệt duy nhất là trong mục đầu tiên: (Phạm vi (“#: ##”) so với Lựa chọn).
Bạn đã biết điều đó:
Selection.SpecialCells (xlCellTypeBlanks) là một ví dụ về phương thức Range.SpecialCells.
Phạm vi (“#: ##”) SpecialCells (xlCellTypeBlanks) là một ví dụ khác của phương thức như vậy.
Mục đích của phương pháp này là để có được một đối tượng đại diện cho các ô phù hợp với một loại và giá trị nhất định, cả hai đều xuất hiện trong dấu ngoặc đơn.
Trong Macro # 1, hiệu quả của việc sử dụng phương pháp này là để có được tất cả các ô trống trong phạm vi ô đã chọn, với phạm vi ô đó được biểu diễn bằng phép chọn.
Xem xét ở trên, những gì bạn mong đợi là tác dụng của Range ("#: ##"). SpecialCells (xlCellTypeBlanks)?
Nếu bạn trả lời rằng hiệu ứng là như nhau, bạn nói đúng.
“Range (“ #: ## ”). SpecialCells (xlCellTypeBlanks)” trả về tất cả các ô trống trong phạm vi (“#: ##”). Trong ví dụ mà tôi đã sử dụng, điều này có nghĩa là tất cả các ô trống trong cột nơi giá đóng của cổ phiếu của Microsoft xuất hiện.
Dòng số 5. Kết thúc với
Dòng này đơn giản là kết thúc câu lệnh With… End With.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows_2
Như bạn có thể mong đợi, quá trình theo sau bởi macro này (Delete_Blank_Rows_2) là rất giống với macro được sử dụng bởi macro trước, trong đó câu lệnh With… End With không được sử dụng (Delete_Blank_Rows). Tuy nhiên, nó không chính xác cùng một quá trình, vì vậy chúng ta hãy tóm tắt những gì mà macro Delete_Blank_Rows_2 thực sự làm:
Bước # 1: Macro làm rõ rằng một loạt các câu lệnh hoạt động với một phạm vi cụ thể, mà không cần phải lặp lại phạm vi là mỗi lần.
Trong ví dụ mà tôi đã sử dụng đây là phạm vi ô từ E6 đến E257, nơi giá đóng cửa cổ phiếu của Microsoft xuất hiện.
Bước # 2: Macro lấy tất cả các ô trong phạm vi và đặt giá trị của chúng bằng với giá trị hiện tại của chúng. Tác động chính của việc này là nó biến các ô trống rỗng vì chúng có dấu ngoặc kép (“”) vào khoảng trống thực tế.
Bước # 3: Macro đi qua phạm vi cụ thể đó tìm kiếm các ô trống.
Bước # 4: Khi macro tìm thấy ô trống, macro sẽ chọn toàn bộ hàng có các ô trống đó.
Bước # 5: Macro xóa các hàng có liên quan.
Cũng giống như trong trường hợp của macro Delete_Blank_Rows, macro Delete_Blank_Rows_2 bao gồm câu lệnh On Error để bẫy lỗi được dự kiến sẽ xảy ra nếu không có ô trống trong phạm vi macro hoạt động.
Như bạn có thể thấy, hiệu ứng là như nhau và quá trình này rất giống nhau; nhưng nó không giống hệt nhau. Để làm cho điểm này rõ ràng hơn, hãy xem xét quá trình cuối cùng này (Delete_Blank_Rows_2) đồ họa:
Bạn sẽ rất vui khi biết rằng, quay trở lại ví dụ và những thách thức bạn đang gặp phải tại văn phòng khi cố gắng chuẩn bị bản tóm tắt về hiệu suất trong năm 2014 của các cổ phiếu của Microsoft, Google, Apple và Amazon, macro đạt được kết quả cuối cùng giống như Macro # 1. Hãy xem hình ảnh bên dưới để xác nhận rằng các hàng mà giá đóng của Microsoft chia sẻ bị thiếu sẽ bị xóa.
Vì vậy, ông chủ của bạn có thể sẽ hài lòng với cả Delete_Blank_Rows hoặc macro Delete_Blank_Rows_2 😀. Tuy nhiên, bạn có thể muốn làm việc với macro Delete_Blank_Rows_2 để tránh sử dụng phương thức Select và thuộc tính Selection. Có nhiều lý do tại sao .Select được tránh, bao gồm các sự kiện rằng nó có thể làm chậm các macro của bạn và tăng nguy cơ lỗi thời gian chạy.
Macro # 3: Xóa hàng dựa trên các ô có trống hay không
Chúng ta hãy xem xét khả năng thứ ba để xóa các hàng khi một số ô trống. Mã sau được đề xuất tại ozgrid.com:
Tôi đặt tên macro là “Delete_Blank_Rows_3”, gán phím tắt “Ctrl + Shift + B” và thêm mô tả macro. Mã VBA đầy đủ được VBE hiển thị như sau:
Bạn có thể nhận thấy 2 điều khi nhìn vào macro này:
# 1: Không giống như các macro trước, mã VBA không chỉ định phạm vi ô mà nó hoạt động. Nó bắt đầu bằng cách làm cho tham chiếu đến "Lựa chọn" mà, như bạn đã biết, là lựa chọn hiện tại. Do đó, trước khi thực hiện macro Delete_Blank_Rows_3, bạn cần phải chọn phạm vi ô bạn muốn làm việc.
Bên dưới, tôi giải thích cách bạn có thể thực hiện lựa chọn này để đạt được mục tiêu của mình. Bạn sẽ ngạc nhiên bởi thực tế là bạn thực sự có thể chọn một cột không có ô trống.
# 2: Mã này gần giống với macro Delete_Blank_Rows_2. Đặc biệt, bạn có thể nhận thấy rằng câu lệnh "Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete" gần như là các tên như câu lệnh "Range (" E6: E257 ″). SpecialCells (xlCellTypeBlanks) .EntireRow.Delete " xuất hiện trong Macro # 2 dưới dạng câu lệnh With… End With.
Chúng ta hãy xem xét từng dòng mã để hiểu quy trình là gì tiếp theo là macro này, và nó khác với những gì mà chúng ta đã kiểm tra trước đây.
Dòng # 1: Khi lỗi tiếp tục lại
Bạn đã biết rằng đây là một câu lệnh On Error chỉ định rằng, khi một lỗi thời gian chạy xảy ra, Excel sẽ tiếp tục thực thi câu lệnh sau đó xảy ra lỗi.
Trong macro cụ thể này, lý do chính để sử dụng On Error Resume Tiếp theo là dòng "Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete" tạo ra một lỗi thời gian chạy khi phạm vi ô được chọn không chứa bất kỳ sản phẩm nào ô. Để tránh việc thực thi macro bị dừng do lỗi thời gian chạy dự kiến này, On Error Resume Next được sử dụng để cho phép thực thi ứng dụng tiếp tục.
Dòng # 2: Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete
Macro Delete_Blank_Rows bao gồm một câu lệnh rất giống với câu lệnh sau: “Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete”. Tuy nhiên, bạn sẽ nhận thấy một vài sự khác biệt ở đầu hàng. Do đó, chúng ta hãy xem xét từng yếu tố một cách riêng biệt để hiểu những gì đang diễn ra.
Yếu tố # 1: Selection.EntireRow.
Đây là một thể hiện của thuộc tính Range.EntireRow, nó trả về một dãy đại diện cho toàn bộ một hàng hoặc một hàng.
Macro Delete_Blank_Rows_3 không chỉ rõ phạm vi ô mà bạn đang làm việc. Ví dụ, cả macro Delete_Blank_Rows và macro Delete_Blank_Rows_2 đã làm việc với một phạm vi được xác định cụ thể, trong các ví dụ, là từ ô E6 đến ô E257. Delete_Blank_Rows_3 hoạt động với Lựa chọn, nghĩa là nó hoạt động với lựa chọn hiện tại.
Như một hệ quả của những điều trên, những gì Selection.EntireRow làm là:
Thực hiện lựa chọn hiện tại.
Trả về một đối tượng đại diện cho toàn bộ hàng (hoặc hàng) có chứa lựa chọn hiện tại của bạn.
Cách macro Delete_Blank_Rows_3 được cấu trúc có nghĩa là bạn không phải thực sự chọn cột chứa các ô trống cho phạm vi đó để được xem xét. Lấy sổ làm việc Excel có chứa dữ liệu hiệu suất cổ phiếu cho Microsoft, Google, Amazon và Apple làm ví dụ. Bạn biết rằng cột chứa một số ô trống là cột E (hiển thị giá đóng cửa của phần chia sẻ của Microsoft). Tuy nhiên, bạn có thể chọn cột G trước khi thực hiện macro Delete_Blank_Rows_3.
Và macro vẫn xem xét tất cả các ô trong mỗi hàng được chọn. Điều này có nghĩa là cột E (và các ô trống của nó) được đưa vào xem xét bởi Macro # 3.
Yếu tố # 2: SpecialCells (xlBlanks).
Bạn đã biết rằng phương thức này được gọi là phương thức Range.SpecialCells và mục đích của nó là lấy một đối tượng đại diện cho các ô phù hợp với kiểu và giá trị xuất hiện bên trong dấu ngoặc đơn.
Trong trường hợp của macro Delete_Blank_Rows_3, đối tượng được tìm nạp là một dãy đại diện cho các ô có kiểu xlBlanks.
Bạn có thể tự hỏi:
XlBlanks là gì? nó đứng để làm gì? Nó khác với xlCellType xlCellTypeBlanks?
Như được giải thích bởi Tom Ogilvy ở đây, tài liệu xung quanh sự khác biệt giữa xlBlanks và xlCellTypeBlanks đôi khi không rõ ràng như bạn mong muốn. Nói chung và theo giải thích của chuyên gia Excel David McRitchie, xlBlanks là thuật ngữ được sử dụng trong Excel 1995 và các phiên bản trước. xlCellTypeBlanks đã được sử dụng kể từ Excel 1997. Tuy nhiên, cho các mục đích cơ bản của Visual Basic cho các ứng dụng, cả xlBlanks và xlCellTypeBlanks đều đánh giá cùng một giá trị.
Do đó, trong macro Delete_Blank_Rows_3 được hiển thị ở trên, “xlBlanks” đề cập đến các ô trống.
Các ô trống được tìm kiếm ở đâu?
Bạn biết rằng phần đầu tiên của câu lệnh (Selection.EntireRow) lấy các ô đã chọn và trả về một đối tượng đại diện cho toàn bộ các hàng nơi các ô được chọn. Phương thức Range.SpecialCells tìm thấy bất kỳ ô trống nào trong các hàng đó.
Quay lại dữ liệu hiệu suất cổ phiếu lịch sử cho Microsoft, Google, Amazon và Apple, điều này có nghĩa là ngay cả khi bạn đã chọn một cột không có bất kỳ hàng trống nào (chẳng hạn như cột G trong ảnh chụp màn hình bên dưới), Phạm vi. Phương thức SpecialCells có thể tìm các ô trống trong các cột khác của bảng, như cột mà giá đóng của cổ phiếu Microsoft xuất hiện (cột E).
Yếu tố # 3: EntireRow.Delete.
Bạn đã thấy thuộc tính Range.EntireRow và phương thức Delete trong hành động trước trong hướng dẫn này. Do đó, tôi chắc chắn bạn sẽ không gặp bất kỳ vấn đề nào trong việc tìm ra phần cuối của câu lệnh này.
Đoán nào?
Như bạn có thể, và chính xác, tưởng tượng, sự kết hợp của thuộc tính và phương thức này lấy tất cả các hàng có chứa các ô trống được tìm thấy bởi phương thức Range.SpecialCells ở trên và xóa chúng.
Do đó, macro Delete_Blank_Rows_3 có khả năng có hiệu ứng rất giống với hai macro trước đó (Delete_Blank_Rows và Delete_Blank_Rows_2). Ví dụ: nếu bạn chọn cột G trong sổ làm việc Excel ghi lại hiệu suất cổ phiếu lịch sử cho năm 2014 của một số cổ phiếu (Microsoft, Google, Amazon và Apple) trước khi áp dụng macro Delete_Blank_Rows_3, các hàng có chứa ô trống (ngay cả khi ô trống không nằm trong cột G) sẽ bị xóa. Ảnh chụp màn hình bên dưới hiển thị 2 trong số các hàng bị xóa trong trường hợp này.
Dòng # 3: Trên Lỗi GoTo 0
Mục đích của câu lệnh On Error GoTo 0 là vô hiệu hóa bất kỳ trình xử lý lỗi nào. Trong trường hợp của macro Delete_Blank_Rows_3, Error GoTo 0 vô hiệu hóa trình xử lý lỗi Tiếp tục Lỗi Tiếp theo xuất hiện trong dòng đầu tiên của phần thân macro.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows_3
Tóm tắt sau đây giải thích, theo nghĩa đơn giản, ý chính của quá trình tiếp theo là macro Delete_Blank_Rows_3:
Bước # 1: Macro lấy lựa chọn hiện tại của bạn và nhận toàn bộ các hàng có chứa lựa chọn đó.
Bước # 2: Tìm kiếm macro cho các ô trống trong các hàng đã thu được.
Bước # 3: Nếu tìm thấy các ô trống, toàn bộ hàng mà chúng được tìm thấy sẽ thu được.
Bước 4: Các hàng mà các ô trống được tìm thấy sẽ bị xóa.
Biểu đồ dòng chảy có liên quan trông gần như sau. Bạn có thể nhận thấy rằng sơ đồ này rất giống với quá trình tiếp theo là macro Delete_Blank_Rows. Tuy nhiên, nếu bạn so sánh các đoạn mã VBA, bạn sẽ nhận thấy rằng có một số khác biệt quan trọng giữa 2 thủ tục. Cụ thể, macro Delete_Blank_Rows_3 không sử dụng phương thức Chọn (mặc dù nó sử dụng Lựa chọn). Như tôi đã giải thích ở phần cuối của phần trước, đó là lời khuyên phổ biến để tránh sử dụng .Chọn khi có thể.
Hình ảnh bên dưới hiển thị macro Delete_Blank_Rows_3 đang hoạt động. Đối với các mục đích này, và để tiếp tục với cùng một ví dụ được sử dụng ở trên, tôi đã chọn cột G trước khi thực thi macro. Trong sổ làm việc Excel mẫu cho thấy kết quả của việc áp dụng Macro # 3, tôi đã thêm lại 3 hàng để bao gồm tiêu đề mà tôi thường đưa vào sổ làm việc Excel mẫu.
Excel VBA này xóa Blank hoặc Empty Rows Tutorial được kèm theo sổ làm việc Excel chứa dữ liệu và các macro mà tôi sử dụng. Bạn có thể truy cập miễn phí ngay lập tức vào các sổ làm việc mẫu này bằng cách đăng ký Bản tin Bảng tính Power.
Macro # 4: Xóa hàng khi hàng toàn bộ trống (tùy chọn 1)
Macro từ # 1 đến # 3 rất hữu ích nếu bạn muốn xóa toàn bộ các hàng khi một ô cụ thể trong hàng đó trống. Nhưng điều này có thể không làm việc cho bạn trong mọi trường hợp.
Chúng ta hãy xem lại dữ liệu hiệu suất cổ phiếu trong năm 2014 của Microsoft, Google, Apple và Amazon.
Nhưng trước tiên…
Bạn có nhớ đồng nghiệp của bạn? Một trong những người không biết làm thế nào để sử dụng Excel và vô tình xóa một số giá đóng cửa cho cổ phiếu của Microsoft?
Vâng ... Có vẻ như anh ta đã "làm việc" một lần nữa trên sổ làm việc Excel đặc biệt này và, vô tình, thêm một vài hàng trống trong suốt bảng. Sếp của bạn mở tệp này và không thích những gì anh ta thấy:
Vì anh ấy biết bạn là trình hướng dẫn Excel của văn phòng, anh ấy yêu cầu bạn xóa các hàng đó.
Sao bạn có thể làm được vậy?
Với một macro, tất nhiên! Bạn không muốn lãng phí thời gian của bạn đi bằng tay thông qua toàn bộ bảng tìm kiếm các hàng hoàn toàn trống rỗng.
Vì vậy, hãy làm điều đó bằng cách sử dụng mã VBA sau, được đề xuất tại ozgrid.com:
Tương tự như macro Delete_Blank_Rows_3, Delete_Empty_Rows hoạt động trong phạm vi đã chọn. Điều này có nghĩa là nó xóa các hàng trống của phạm vi mà bạn đã chọn và, do đó, bạn phải chọn phạm vi dữ liệu bạn muốn làm việc (xóa các hàng trống từ) trước khi thực hiện macro.
Để hiểu rõ hơn về macro cụ thể này, chúng ta hãy xem xét từng dòng mã nào.
Dòng số 1: Dim iCounter As Long
Câu lệnh Dim được sử dụng để khai báo một biến. Trong trường hợp này, biến được khai báo là iCounter. Từ khóa As được sử dụng để khai báo iCounter như một biến của kiểu dữ liệu Long. Do đó, iCounter giữ số nguyên.
Về mặt lý thuyết, iCounter cũng có thể được khai báo là một biến của kiểu dữ liệu Integer. Lý do tại sao macro cụ thể này sử dụng Long thay vì Integer, như được giải thích ở đây, rằng kiểu dữ liệu Long có thể chứa các số nguyên lớn hơn các số nguyên phù hợp với kiểu dữ liệu Integer. Nói cách khác, Long hữu ích trong trường hợp phạm vi bạn chọn để áp dụng macro có quá nhiều hàng.
Bạn có thể tự hỏi:
Mục đích của việc khai báo biến này là gì?
Biến iCounter, như ngụ ý bởi tên của nó, phục vụ như truy cập. Lý do tại sao macro Delete_Empty_Rows cần một bộ đếm là nó được cấu trúc như một vòng lặp. Vòng lặp này được thực hiện bằng cách sử dụng câu lệnh For… Next (mà tôi giải thích dưới đây) lặp lại một nhóm các câu lệnh cụ thể với một số lần nhất định và do đó cần một biến điều khiển (trong trường hợp này là iCounter) để theo dõi bao nhiêu lần vòng lặp đã được lặp lại.
Nếu bạn không hoàn toàn hiểu được lời giải thích này, đừng lo lắng ... Chỉ cần tiếp tục đọc; vào cuối phần này, bạn sẽ hiểu hoàn toàn tất cả những điều trên.
Dòng số 2: Với ứng dụng
Bạn đã biết rằng từ “With” là một tín hiệu cho thấy câu lệnh With… End With đang bắt đầu. Bạn cũng biết rằng cú pháp thông thường cho dòng đầu tiên của câu lệnh như vậy là “Với objectExpression”, trong đó “objectExpression” là một biểu thức đánh giá một đối tượng.
Đối tượng Application là chính Excel hoặc, được giải thích bởi Microsoft, “toàn bộ ứng dụng Microsoft Excel”.
Kết quả của việc trên, bạn biết rằng một loạt các câu lệnh xuất hiện trong câu lệnh With… End With đều làm việc và tham chiếu đến chính ứng dụng Excel.
Ảnh chụp màn hình sau đây cho thấy toàn bộ Với ... Kết thúc với tuyên bố.
Dòng # 3: .Calculation = xlCalculationManual
Dòng này nằm trong câu lệnh With… End With để tham chiếu đến ứng dụng Excel. Vì vậy, bạn có thể nghĩ về nó như là "Application.Calculation = xlCalculationManual = xlCalculationManual".
Thuộc tính Application.Calculation được sử dụng để thiết lập chế độ tính toán mà Excel áp dụng cho sổ làm việc. Trong tuyên bố cụ thể này, chế độ được chọn là xlCalculationManual, có nghĩa là Excel không tính toán lại sổ làm việc một cách tự động, mà đúng hơn là khi bạn yêu cầu nó.
Nói cách khác, câu lệnh này tắt tính toán bảng tính tự động. Mục đích của việc này, như được giải thích tại ozgrid.com, là "tăng tốc độ vĩ mô".
Dòng # 4: .ScreenUpdating = False
Cũng như với câu lệnh trước, dòng này làm việc với ứng dụng Excel và, do đó bạn có thể nghĩ nó là “Application.ScreenUpdating = False”.
Thuộc tính Application.ScreenUpdating được sử dụng để tắt cập nhật màn hình. Mục đích của việc này tương tự như việc tắt tính toán bảng tính tự động: để tăng tốc mã macro.
Dòng # 5: Dành cho iCounter = Selection.Rows.Count To 1 Step –1
Dòng mã này là sự khởi đầu của một câu lệnh Tiếp theo…, là một cách phổ biến để cấu trúc một vòng lặp trong Visual Basic cho các ứng dụng. Câu lệnh này yêu cầu Excel thực hiện một nhóm các câu lệnh liên tục một số lần nhất định.
Cú pháp thông thường cho dòng đầu tiên của câu lệnh Tiếp theo… là “Đối với bộ đếm [Theo kiểu dữ liệu] = bắt đầu Để kết thúc [Bước bước]”. Dòng này, về cơ bản, cho Excel biết bao nhiêu lần để thực hiện vòng lặp bằng cách tạo một bộ đếm đếm số lần lặp lại, cho biết giá trị bắt đầu và kết thúc của bộ đếm đó là gì và tùy ý số lượng truy cập tăng hoặc giảm mỗi lần lặp lại.
Trong macro Delete_Empty_Rows, bộ đếm là biến iCounter. Giá trị khởi đầu của nó là số hàng trong phạm vi bạn đã chọn để làm việc với trước khi thực thi macro, giá trị kết thúc của nó là 1 và nó giảm 1 lần mỗi khi vòng lặp được thực hiện. Nói cách khác, vòng lặp được lặp lại một số lần bằng số hàng trong phạm vi bạn đang làm việc. Điều này có ý nghĩa vì bạn muốn Excel kiểm tra từng hàng của phạm vi đó để xác nhận xem hàng đó có trống hay không và do đó, nên bị xóa.
Nhưng chúng ta hãy xem xét kỹ hơn từng phần tử của dòng mã VBA này trong macro Delete_Empty_Rows để hiểu cách cấu trúc của nó:
“ICounter” là đối số Counter, là một mục được yêu cầu của câu lệnh For… Next. Đối số này là một biến điều khiển, có nghĩa là nó theo dõi số lần lặp lại vòng lặp của cơ thể.
Kiểu dữ liệu không được xác định trong phần thân của vòng lặp này, vì iCounter đã được định nghĩa trước đó (trong câu lệnh đầu tiên của Macro # 4) dưới dạng biến kiểu dữ liệu dài.
“Selection.Rows.Count” (thay cho vị trí “bắt đầu” trong cú pháp câu lệnh) xác định giá trị ban đầu của bộ đếm. Giá trị ban đầu này bằng số hàng trong phạm vi mà bạn đã chọn để làm việc trước khi thực thi macro.
Chúng ta hãy xem xét chính xác cách mà câu lệnh này cho Excel biết giá trị bắt đầu cho bộ đếm nên là gì.
# 1: Selection.Rows là một ví dụ về thuộc tính Range.Rows, nó trả về một đối tượng đại diện cho các hàng trong một phạm vi cụ thể. Bạn đã biết rằng lựa chọn trả về lựa chọn hiện tại. Nói cách khác, Selection.Rows trả về các hàng trong bảng (hoặc phạm vi dữ liệu) mà bạn đã chọn để áp dụng macro.
# 2: Thuộc tính Range.Count, như bạn có thể tưởng tượng, đếm số hàng trong phạm vi bạn đang làm việc và trả về giá trị đó.
“To 1” cho Excel biết giá trị cuối cùng của bộ đếm là gì, rõ ràng là 1. Như bạn có thể thấy, số 1 sẽ thay thế “kết thúc” trong cú pháp chung của câu lệnh Tiếp theo….
“Bước –1” quyết định giá trị mà bộ đếm thay đổi mỗi khi Excel đi qua vòng lặp. Trong trường hợp này, biến điều khiển iCounter giảm 1 mỗi lần Excel lặp lại phần thân của vòng lặp. Trong trường hợp của macro Delete_Empty_Rows, giá trị -1 lấy vị trí của mục tùy chọn “bước” trong cú pháp chung của câu lệnh Tiếp theo….
Bạn có thể xem toàn bộ Đối với ... Tiếp theo tuyên bố của macro Delete_Empty_Rows trong hình dưới đây:
Dòng # 6: Nếu WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0 Sau đó
Đây là hàng đầu tiên của câu lệnh If… Then… Khác. Các câu lệnh này là một trong những cách phổ biến nhất để triển khai một điều kiện trong Visual Basic cho các ứng dụng.
Theo giải thích của Microsoft ở đây, cú pháp của dòng đầu tiên của câu lệnh If… Then… Khác là “If condition [Then]”. Trong dòng mã VBA xuất hiện ở trên, điều kiện được kiểm tra là liệu WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0. Nếu điều kiện này được đánh giá là đúng, Excel thực hiện câu lệnh xuất hiện ngay bên dưới (tôi giải thích điều này trong bước tiếp theo).
Tuy nhiên, câu hỏi là:
Điều kiện nào WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0 có nghĩa là gì?
Câu hỏi được hỏi bởi phép thử logic này là liệu tất cả các ô trong một hàng cụ thể đều trống không. Chúng ta hãy xem xét từng yếu tố của nó một cách riêng biệt để hiểu cách câu hỏi được cấu trúc theo các thuật ngữ VBA:
Đối tượng WorksheetFunction là một vùng chứa cho các hàm trang tính thông thường. Nói cách khác, bạn sử dụng đối tượng WorksheetFunction để gọi các hàm trang tính.
Phương thức WorksheetFunction.CountA hoạt động tương tự như hàm COUNTA Excel. Nói cách khác, phương pháp này tính số lượng ô trong một phạm vi cụ thể không trống.
Cú pháp của phương thức cụ thể này là "expression.CountA (arguments)", trong đó "expression" là một biến cụ thể đại diện cho một WorksheetFunction và "arguments" là các đối số đại diện cho các giá trị cần đếm. Trong macro Delete_Empty_Rows, các đối số được tính là Selection.Rows (iCounter).
Trong Macro # 4, Selection.Rows (iCounter) đại diện cho một hàng trong phạm vi dữ liệu bạn đang áp dụng macro. Hàng chính xác được đại diện bởi đối số này phụ thuộc vào giá trị hiện tại của biến iCounter. Như bạn đã biết, giá trị của iCounter thay đổi theo các điều kiện có trong phần đầu của câu lệnh Tiếp theo….
Ví dụ, lần đầu tiên Excel chạy phần thân của vòng lặp, hàng được sử dụng để xác định xem điều kiện đánh giá là đúng hay sai là hàng cuối cùng trong phạm vi mà macro đang làm việc với (Row (Selection.Rows.Count) ). Lần cuối Excel lặp lại vòng lặp, Selection.Rows (iCounter) tham chiếu đến hàng đầu tiên trong phạm vi (Hàng (1)).
Phần cuối của câu lệnh (“= 0”) đặt điều kiện là biểu thức bên trái dấu bằng (WorksheetFunction.CountA (Selection.Rows (iCounter))) bằng 0.
Phương thức WorksheetFunction.CountA chỉ bằng 0 nếu tất cả các ô trong hàng đang được kiểm tra (Selection.Rows (iCounter)) rỗng. Nếu có ít nhất một ô không trống, phương thức trả về một giá trị khác và điều kiện đánh giá là sai.
Dòng # 7: Selection.Rows (iCounter) .EntireRow.Delete
Đây là đoạn mã VBA duy nhất trong câu lệnh If… Then… Khác và, do đó, là câu lệnh duy nhất được thực thi nếu điều kiện trên đánh giá là đúng (tất cả các ô trong một hàng cụ thể đều trống).
Bạn có biết những gì tuyên bố này không?
Bạn đã biết tất cả các phần tử trong hàng này và, do đó, có thể đã trả lời rằng đoạn mã VBA này xóa toàn bộ hàng. Trong mọi trường hợp, chúng ta hãy đi nhanh qua từng mục:
Selection.Rows (iCounter) đại diện cho một hàng trong phạm vi mà Delete_Empty_Rows đang làm việc.
.EntireRow chọn toàn bộ hàng.
.Xóa xóa đối tượng đã chọn, trong trường hợp này, là hàng đã chọn.
Dòng số 8: Kết thúc nếu
Dòng này đơn giản chấm dứt câu lệnh If… Then… Khác.
Tóm lại, nếu… thì… khối khác:
Hỏi xem một hàng trong phạm vi đã chọn có trống không.
Nếu hàng trống, nó sẽ chọn toàn bộ hàng và xóa nó.
Dòng số 9: Tiếp theo iCounter
Đây là dòng cuối cùng của câu lệnh Tiếp theo ....
Khi Excel đã thực hiện các hoạt động được chỉ định trong vòng lặp, nó sẽ chuyển sang phần tử tiếp theo, trong trường hợp này, là hàng tiếp theo của phạm vi đã chọn.
Dòng # 10: .Calculation = xlCalculationAutomatic
Bạn đã biết rằng câu lệnh .Calculation = xlCalculationManual xuất hiện sớm trong câu lệnh With… End With của macro này sẽ tắt tính toán bảng tính tự động.
Bạn có thể hình dung câu lệnh .Calculation = xlCalculationAutomatic không?
Vâng. Như bạn đã có thể trả lời, nó chỉ đơn giản là quay trở lại tính toán bảng tính hoặc, chính xác hơn, cho phép Excel kiểm soát tính toán lại.
Dòng # 11: .ScreenUpdating = True
Tôi chắc chắn rằng bạn biết những gì tuyên bố này không, nhưng tôi sẽ hỏi câu hỏi anyway:
Bạn có biết những gì .ScreenUpdating = Đúng không?
Vì bạn biết rằng .ScreenUpdating = False tắt cập nhật màn hình, bạn có thể tưởng tượng rằng .ScreenUpdating = True biến màn hình cập nhật trở lại. Làm điều này (bật màn hình cập nhật trở lại) là khá quan trọng như vậy, như Microsoft nói, đừng quên đặt thuộc tính ScreenUpdating trở lại True sau khi macro bạn đang viết kết thúc.
Dòng # 12: Kết thúc bằng
Hàng này chấm dứt câu lệnh With… End With.
Tóm tắt quá trình theo sau bởi Delete_Empty_Rows Macro
Tóm lại, macro Delete_Empty_Rows tiến hành như sau:
Bước # 1: Macro chuyển đến hàng cuối cùng của phạm vi bạn đã chọn.
Bước # 2: Macro kiểm tra xem hàng cụ thể đó có hoàn toàn trống không.
Bước # 3: Nếu hàng hoàn toàn trống, macro sẽ xóa hàng. Nếu hàng không hoàn toàn trống, nó không làm gì cả.
Bước 4: Macro di chuyển đến hàng ngay trên hàng nó vừa phân tích và lặp lại quá trình tương tự, bắt đầu với bước # 2 ở trên.
Bước # 5: Macro tiếp tục di chuyển lên qua phạm vi được chọn cho đến khi nó đạt đến hàng đầu tiên (tại điểm kết thúc).
Ngoài các bước trên, Delete_Empty_Rows chuyển các tính toán sổ làm việc tự động và cập nhật màn hình cho các mục đích tăng tốc macro. Sau khi hoàn tất quá trình xóa các hàng trống, tính toán sổ làm việc tự động và cập nhật màn hình sẽ được bật trở lại.
Đồ họa, quá trình này trông gần như sau:
Hình ảnh dưới đây cho thấy cách macro Delete_Empty_Rows hoạt động trong thực tế:
Macro # 5: Xóa hàng khi hàng toàn bộ trống (tùy chọn 2)
Một thay thế cho macro Delete_Empty_Rows cũng xóa toàn bộ hàng khi nó hoàn toàn trống rỗng được đề xuất bởi anhn tại VBForums. Phần thân của mã VBA như sau:
Tôi đặt tên cho macro này là Delete_Empty_Rows_2, gán phím tắt “Ctrl + Shift + P” và thêm mô tả. Toàn bộ mã macro trông như sau trong trình soạn thảo Visual Basic:
Như thường lệ, chúng ta hãy xem từng dòng mã để hiểu cách macro tiến hành. Bây giờ, tôi phải cảnh báo bạn rằng cấu trúc của macro này hơi phức tạp hơn so với các macro khác mà chúng tôi đã khám phá trong hướng dẫn này, vì vậy, nếu bạn quyết định tiếp tục, hãy chịu theo tôi. Đến cuối, bạn sẽ có thể hiểu được quá trình tiếp theo là macro này
Dòng # 1 và dòng # 2: Dim aRow As Range | Dim BlankRows As Range
Bạn đã biết điều đó:
Câu lệnh Dim được sử dụng cho mục đích khai báo một biến.
Từ khóa As được sử dụng để xác định kiểu dữ liệu của một biến trong câu lệnh khai báo.
Trong macro Delete_Empty_Rows_2, các biến được khai báo là “aRow” và “BlankRows”. Cả hai đều được khai báo là đối tượng Phạm vi.
Trong Macro # 5, các biến này được sử dụng cho các mục đích biểu diễn các hàng. Cụ thể hơn, BlankRows được sử dụng để lưu trữ và đại diện cho tất cả các hàng trống trong phạm vi ô bạn đã chọn.
Dòng # 3: Dành cho mỗi aRow Trong lựa chọn.Rows.EntireRow
Hàng này báo hiệu sự khởi đầu của câu lệnh For Each… Next, mục đích của nó là thực hiện lặp lại một nhóm các câu lệnh cho mỗi thành phần của một bộ sưu tập hoặc mảng đối tượng.
Hình ảnh này hiển thị toàn bộ Đối với mỗi câu lệnh tiếp theo trong macro Delete_Empty_Rows_2:
Cú pháp cơ bản của dòng đầu tiên của câu lệnh For Each… Next là “Đối với mỗi phần tử [như datatype] trong nhóm”, trong đó:
"Phần tử" là một biến được yêu cầu được sử dụng cho mục đích lặp qua các phần tử khác nhau của bộ sưu tập hoặc mảng. Tôi đề cập đến chủ đề của mảng chi tiết hơn trong bài đăng trên blog này.
Trong macro Delete_Empty_Rows_2, "phần tử" là biến aRow đã được khai báo ở đầu macro là một dải ô. Trong trường hợp này, vì phần tử đã được khai báo, nên không cần phải nêu rõ kiểu dữ liệu của nó trong câu lệnh For Each… Next. Khi phần tử chưa được khai báo trước đó, nó cần thiết để bao gồm kiểu dữ liệu của phần tử trong câu lệnh For Each… Next.
"Nhóm" là một đối tượng được yêu cầu đề cập đến bộ sưu tập hoặc mảng mà trên đó các câu lệnh trong câu lệnh For Each… Next được lặp lại.
Đối với Delete_Empty_Rows_2, nhóm là Selection.Rows.EntireRow. Có lẽ bạn nhớ rằng Selection.Rows là một sự thực thi có thể có của thuộc tính Range.Rows. Bạn cũng có thể nhớ rằng, khi phạm vi là Lựa chọn, mục này trả về các hàng trong phạm vi dữ liệu hiện đang được chọn. Cuối cùng, thuộc tính EntireRow làm cho tham chiếu đến một hoặc nhiều hàng.
Do đó, dòng này nói rằng các câu lệnh trong câu lệnh For Each… Next sẽ được thực hiện và lặp lại cho mỗi hàng trong phạm vi dữ liệu (hoặc bảng) mà bạn đã chọn trước khi thực thi macro Delete_Empty_Rows_2.
Lưu ý rằng, trên thực tế, hiệu ứng của dòng này rất giống với câu lệnh mở của câu lệnh Tiếp theo ... được sử dụng trong macro Delete_Empty_Rows (Đối với iCounter = Selection.Rows.Count To 1 Step –1). Lý do chính cấu trúc cú pháp của chúng khác nhau là bởi vì một là câu lệnh For… Next (lặp lại một nhóm các câu lệnh một số lần nhất định) và cái kia là câu lệnh For Each… Next (lặp lại một nhóm các câu lệnh cho mỗi phần tử). của một bộ sưu tập đối tượng).
Dòng # 4: Nếu WorksheetFunction.CountA (aRow) = 0 Sau đó
Bạn có thể nhận ra rằng đây là hàng đầu tiên của câu lệnh If… Then… Khác và, do đó, biết rằng mục đích của câu lệnh này là thiết lập một điều kiện trong VBA.
Hình ảnh sau đây cho thấy toàn bộ Nếu… Sau đó… Tuyên bố khác:
Thông thường, các loại câu lệnh này sử dụng cú pháp “Nếu điều kiện [Sau đó]”. Do đó, bạn có thể xác định rằng điều kiện được kiểm tra ở trên là liệu “WorksheetFunction.CountA (aRow) = 0”.
Điều kiện này có nghĩa là gì? Nó hỏi cái gì?
Bạn có thể nhớ rằng macro Delete_Empty_Rows cũng bao gồm một câu lệnh If… Then… Khác. Trong cơ hội đó, câu hỏi tương tự. Chính xác hơn, điều kiện kiểm tra là liệu “WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0”. Điều này đã đặt câu hỏi liệu tất cả các ô trong một hàng cụ thể có trống không.
Dựa trên điều này, bạn có thể tưởng tượng điều kiện nào đang được thử nghiệm bởi "WorksheetFunction.CountA (aRow) = 0"?
Bạn đã biết rằng đối tượng Function Worksheet là một container cho các hàm bảng tính và phương thức WorksheetFunction.CountA đếm số ô trong một phạm vi cụ thể không rỗng. Do đó, bạn có thể đã nhận thấy rằng câu hỏi được hỏi bởi macro Delete_Empty_Rows_2 là liệu tất cả các ô trong một hàng cụ thể có trống không.
Dòng # 5: Nếu không BlankRows không có gì thì
Bạn có thể nhận ra rằng đây là lần thứ hai Nếu… Vậy… Tuyên bố khác. Hình ảnh dưới đây cho thấy nó:
Cấu trúc của điều kiện trong câu lệnh cụ thể này, tuy nhiên, khác với câu lệnh của cái kia Nếu… Sau đó… Các câu lệnh khác mà chúng ta đã thấy trong suốt hướng dẫn này. Trong trường hợp này, câu hỏi được đặt ra là liệu "Không BlankRows là không có gì".
Chúng ta hãy xem từng mục riêng lẻ để hiểu ý nghĩa của dòng này:
Như đã giải thích trong tính toán tài chính chuyên nghiệp Sử dụng Excel và VBA, Toán tử không trả về “kết quả lôgic ngược lại của biểu thức được đánh giá”.
BlankRows là một trong các biến được khai báo ở đầu macro.
Toán tử Is so sánh 2 biến tham chiếu đối tượng. Nói cách khác, "Is" xác định liệu 2 tham chiếu đối tượng có tham chiếu đến cùng một đối tượng mà không thực hiện so sánh giá trị hay không. Điều quan trọng là liệu cả hai đều đề cập đến cùng một đối tượng.
Không có gì là giá trị mặc định cho một loại dữ liệu cụ thể. Ví dụ, trong trường hợp kiểu dữ liệu Object, giá trị mặc định là Nothing (một tham chiếu null).
Vậy tuyên bố này đang làm gì?
Toán tử Is được sử dụng để xác định xem đối tượng tham chiếu "Không BlankRows" có tham chiếu đến cùng một đối tượng là Không có gì không. “Không phải BlankRows”, vì bạn có thể kết luận ngay bây giờ, đơn giản là ngược lại với biến BlankRows.
Do đó, câu hỏi được hỏi bởi phép kiểm tra logic này là liệu đối lập của BlankRows có tham chiếu đến cùng một đối tượng như Không. Nói cách khác, nó hỏi: BlankRows có tham chiếu đến cái gì đó không?
Câu hỏi bạn có thể có là:
Khi nào ngược lại với biến BlankRows (Not BlankRows) làm cho tham chiếu đến cùng một đối tượng như Không có gì?
Câu hỏi là phức tạp hơn có vẻ như vì hàng chúng ta đang xem ngay bây giờ nằm trong vòng lặp (được thực hiện với câu lệnh For Each… Next), như hình dưới đây:
Để làm phức tạp hơn nữa, câu lệnh If… Then… Khác mà chúng ta đang xem được sử dụng để gán các giá trị cho biến BlankRows. Tôi giải thích điều này xảy ra như thế nào bên dưới.
Kết quả của việc trên, giá trị của BlankRows thay đổi mỗi lần macro tìm thấy một hàng trống mới. Chúng ta hãy xem xét chi tiết hơn về cách thay đổi này xảy ra:
Lần đầu tiên (s) vòng lặp chạy, BlankRows không được gán cho bất cứ điều gì. Vì vậy, nó không có gì và làm cho tham chiếu đến cùng một đối tượng như Không có gì. Điều này có nghĩa là "Không BlankRows" không làm cho tham chiếu đến cùng một đối tượng như Không có gì và điều kiện "Không BlankRows là không có gì" đánh giá sai.
Hậu quả của việc này là, nếu có bất kỳ hàng trống nào trong phạm vi bạn đã chọn trước khi thực thi macro, giá trị aRow được lưu trữ trong biến BlankRows khi macro tìm thấy hàng trống đầu tiên. Tôi giải thích làm thế nào điều này được thực hiện thêm dưới đây.
Khi hàng trống đầu tiên được tìm thấy, BlankRows lưu trữ giá trị aRow. Vì vậy, bây giờ nó là cái gì đó và làm cho tham chiếu đến đối diện của Không có gì. Điều này có nghĩa là "Không BlankRows" làm cho tham chiếu đến cùng một đối tượng như Không có gì và điều kiện "Không BlankRows là không có gì" đánh giá là đúng.
Hậu quả của việc này là giá trị Union (BlankRows, aRow), có nghĩa là tôi giải thích dưới đây, được lưu trữ trong BlankRows.
Tất cả các lần sau vòng lặp chạy, BlankRows lưu trữ giá trị của Liên minh trước đó (BlankRows, aRow). Do đó, điều kiện tiếp tục đánh giá đúng và giá trị được lưu trữ trong BlankRows được cập nhật tương ứng.
Nếu điều này không có ý nghĩa với bạn, hãy giữ cho đến khi kết thúc vòng lặp. Bởi sau đó bạn sẽ hiểu rõ hơn những gì tập hợp các báo cáo này.
Dòng # 6: Đặt BlankRows = Union (BlankRows, aRow)
Hàng này nằm trong câu lệnh If… Then… Khác. Đây là câu lệnh mà Excel thực hiện nếu kiểm tra lôgic xuất hiện trong hàng trước (Nếu không có BlankRows là Không có gì) sẽ đánh giá đúng.
Chúng ta hãy xem từng mục riêng lẻ:
Câu lệnh Set gán một tham chiếu đối tượng.
BlankRows là một trong các biến được khai báo ở đầu Macro # 5.
Liên minh trả về công đoàn của một số phạm vi.
Như đã giải thích tại Stat Trek, sự kết hợp của 2 bộ là “tập các phần tử thuộc về một hoặc cả hai bộ”. Câu lệnh mà chúng ta đang xem xét tham chiếu đến liên kết giữa BlankRows và aRow, có nghĩa là union bao gồm tất cả các phần tử thuộc về (i) cả BlankRows và aRow, (ii) chỉ BlankRows và (iii) aRow. Đồ họa:
Do đó, câu lệnh này lấy biến BlankRows và gán cho nó sự kết hợp của các biến BlankRows và aRow.
Câu hỏi bạn có thể có là, điều này có ý nghĩa gì trong các thuật ngữ thực tế cho macro Delete_Empty_Rows_2?
Để trả lời câu hỏi này, bạn cần nhớ 2 điều:
Câu lệnh If… Then… Khác mà chúng ta đang xem xét nằm trong câu lệnh For Each… Next (Đối với mỗi aRow In Selection.Rows.EntireRow) đi qua từng hàng riêng biệt của bảng hoặc dải mà bạn đang làm việc. Đối với mỗi hàng trong số các hàng này, macro hỏi xem toàn bộ hàng có trống không (Nếu WorksheetFunction.CountA (aRow) = 0).
Lần đầu tiên vòng lặp chạy, BlankRows là không có gì và phép thử logic “If Not BlankRows Is Nothing” đánh giá sai. Điều này tiếp tục là trường hợp cho đến khi hàng trống đầu tiên được tìm thấy và được gán cho biến BlankRows. Sau đó, điều kiện đánh giá là đúng và phép gán được thực hiện bởi hàng này (Set BlankRows = Union (BlankRows, aRow)) chỉ xảy ra sau đó.
Ngoài các điều trên, khi điều kiện đánh giá sai (chỉ xảy ra cho đến khi vòng lặp tìm thấy hàng trống), câu lệnh được thực thi (Set BlankRows = aRow) đặt BlankRows thành aRow. Nói cách khác, ngay sau lần đầu tiên nhóm các lệnh trong vòng lặp tìm thấy một hàng trống, BlankRows bằng aRow. Điều này có vẻ như sau trong Excel:
Vào lần tiếp theo các lệnh trong vòng lặp được thực hiện, BlankRows đã bằng với hàng trống đầu tiên (mà là aRow). Ngoài ra, macro chọn hàng trống thứ hai (hiện tại là aRow). Do đó, BlankRows được đặt thành liên minh của BlankRows và aRow. Trong Excel, điều này trông như sau:
Thời gian sau khi vòng lặp chạy, BlankRows bằng với liên kết của BlankRows và aRow (tại thời điểm đó) là 2 hàng trống. Thêm vào đó, macro chọn hàng trống thứ ba (aRow). BlankRows sau đó được thiết lập để liên minh mới này của BlankRows và aRow.
Bởi bây giờ bạn có thể có được ý tưởng. Bất kỳ hàng trống nào Excel tìm thấy đều được thêm vào biến BlankRows. Khi vòng lặp đã chạy nhiều lần theo yêu cầu, BlankRows đại diện cho tất cả các hàng trống trong bảng hoặc dải ô.
Dòng # 7: Khác
Hàng này là một phần của câu lệnh If… Then… Khác. Nó báo hiệu rằng các hàng theo sau nó (các câu lệnh khác) chỉ được thực thi nếu không có điều kiện nào trước đây được đánh giá là đúng.
Trong macro Delete_Blank_Rows_2, các câu lệnh khác được thực hiện nếu điều kiện "Nếu không có BlankRows là không có gì" được đánh giá là false. Điều này xảy ra cho đến khi các lệnh trong vòng lặp được báo hiệu bởi câu lệnh “For Each aRow In Selection.Rows.EntireRow” tìm hàng trống đầu tiên.
Nói cách khác, các tín hiệu “Khác” mà các câu lệnh theo sau là các lệnh được thực hiện trong lần đầu tiên vòng lặp trong Macro # 5 chạy. Chính xác hơn, những câu lệnh này được thực hiện cho đến khi macro tìm thấy hàng trống đầu tiên.
Dòng # 8: Đặt BlankRows = aRow
Bạn có thể đã biết những gì tuyên bố này không:
Nó đặt biến BlankRows thành aRow.
Vui lòng tham khảo phần giải thích của câu lệnh “Đặt BlankRows = Union (BlankRows, aRow)” ở trên để có giải thích kỹ lưỡng về cách Macro # 5 quản lý biến BlankRows.
Dòng # 9 và dòng # 10: Kết thúc nếu | Kết thúc nếu
Như bạn đã biết, các câu lệnh này chỉ đơn giản là chấm dứt các câu lệnh If… Then… Khác. Lý do có 2 câu lệnh If End, đơn giản là, 2 riêng biệt Nếu… Sau đó… Các câu lệnh khác đang bị chấm dứt.
Dòng số 11: Tiếp theo
Bạn đã biết rằng hàng này chấm dứt vòng lặp For Each… Next.
Dòng # 12: Nếu không BlankRows là gì thì
Chúng tôi đã thấy tuyên bố này chính xác hơn nữa ở trên. Bạn biết rằng nó báo hiệu sự khởi đầu của một câu lệnh If… Then… Khác.
Hơn nữa, bạn biết rằng câu hỏi được hỏi bởi bài kiểm tra logic là liệu BlankRows có ám chỉ đến điều gì đó không.
Dòng # 13: Application.ScreenUpdating = False
Chúng tôi đã nhìn thấy một tuyên bố tương tự về cơ bản cho điều này trong macro Delete_Empty_Rows.
Do đó, bạn biết rằng thuộc tính Application.ScreenUpdating chuyển lượt cập nhật màn hình cho các mục đích tăng tốc macro.
Dòng # 14: BlankRows.Delete
Các macro có trong hướng dẫn này đã áp dụng phương thức Delete trong một vài trường hợp. Ví dụ, macro Delete_Blank_Rows_3 kết hợp phương thức Delete với một thể hiện của thuộc tính Range.EntireRow để xóa toàn bộ các hàng dựa trên việc một ô trong hàng có trống hay không. Trong trường hợp đó, toàn bộ câu lệnh là “Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete”.
Xem xét ở trên, bạn có thể đoán những gì tuyên bố này không?
Bạn có thể trả lời (chính xác) rằng câu lệnh này lấy tất cả các hàng trống được biểu diễn bằng biến BlankRows và xóa chúng.
Vì hàng cụ thể này nằm trong một câu lệnh If… Then… Khác, nó chỉ được áp dụng nếu biến BlankRows tham chiếu đến một thứ gì đó. Điều này luôn luôn xảy ra trừ khi bạn đang làm việc với một bảng hoặc dải ô không có hàng trống.
Dòng # 15: Application.ScreenUpdating = True
Bạn đã biết rằng điều quan trọng là biến thuộc tính ScreenUpdating trở lại khi macro kết thúc và đó chính là điều mà dòng này thực hiện.
Dòng # 16: Kết thúc nếu
Câu lệnh này kết thúc câu lệnh thứ ba và cuối cùng, nếu… Sau đó… Câu lệnh khác của macro Delete_Empty_Rows_2.
Tóm tắt quá trình theo sau bởi macro Delete_Empty_Rows_2
Như thường lệ, chúng ta hãy kết thúc bằng cách tóm tắt quá trình tiếp theo là macro Delete_Empty_Rows_2.
Bước # 1: Macro đi qua mỗi hàng trong phạm vi mà bạn đã chọn và hỏi xem nó có hoàn toàn trống không.
Bước # 2: Khi macro đã tìm thấy hàng trống đầu tiên trong phạm vi, nó gán nó cho biến BlankRows.
Bước # 3: Khi macro tìm thấy hàng trống thứ hai trong phạm vi được chọn, macro sẽ thêm nó vào biến BlankRows. Do đó, BlankRows giờ đây đại diện cho 2 hàng trống đầu tiên trong phạm vi ô tương ứng.
Bước 4: Macro lặp lại các bước # 1 và # 3 cho đến khi nó tìm thấy tất cả các hàng trống trong phạm vi bạn đang làm việc. Mỗi hàng được thêm vào biến BlankRows như vậy, cuối cùng, BlankRows đại diện cho tất cả các hàng trống.
Bước # 5: Tất cả các hàng trống, được biểu thị bằng biến BlankRows, sẽ bị xóa.
Ngoài các mục trên và cho mục đích tăng tốc macro, Delete_Empty_Rows_2 tắt cập nhật màn hình trước bước # 5. Cập nhật màn hình được bật lại sau khi các hàng trống đã bị xóa.
Đồ họa, toàn bộ quá trình này trông như sau:
Hình ảnh sau đây cho bạn thấy macro Delete_Empty_Rows_2 tại nơi làm việc:
Kết luận và các sửa đổi có thể có khác cho Macros trong hướng dẫn này
5 macro xuất hiện trong hướng dẫn này, không có nghĩa là, một danh sách đầy đủ tất cả các cách có thể mà bạn có thể mã macro để xóa các hàng trống hoặc các hàng có các ô trống trong Excel.
Khi bạn trở nên thành thạo hơn trong Visual Basic cho các ứng dụng và cải thiện kiến thức của bạn về macro, bạn sẽ tìm cách để tinh chỉnh và cải thiện các macro mà tôi đã giải thích ở trên. Có lẽ quan trọng hơn, bạn sẽ bắt đầu hiểu tại sao một cấu trúc cụ thể có thể phù hợp hơn một cấu trúc khác xem xét bối cảnh bạn đang làm việc và các mục tiêu bạn muốn đạt được.
Sau đây là danh sách rất ngắn gọn các cách có thể để sửa đổi các macro xuất hiện trong hướng dẫn này. Mục đích chỉ nhằm cung cấp cho bạn ý tưởng về các câu hỏi mà bạn có thể bắt đầu xem xét trong tương lai khi tạo macro để xóa các hàng hoặc hàng trống với các ô trống trong Excel.
Macro Delete_Blank_Rows_2 sử dụng câu lệnh With… End With.
Cấu trúc này có cần thiết không? Làm thế nào về việc sử dụng một dòng mã như trong macro Delete_Blank_Rows_3 hoặc macro được đề xuất bởi VoG tại diễn đàn của Mr.Excel?
Có bất kỳ macro nào xuất hiện trong lợi ích của hướng dẫn này không bao gồm các câu lệnh On Error cho các mục đích xử lý các lỗi thời gian chạy không?
Sẽ hữu ích khi tạo các hộp thoại bổ sung cho bất kỳ macro nào không?
Làm thế nào về một hộp thoại xác nhận rằng các hàng trống đã được gỡ bỏ, chẳng hạn như một xuất hiện trong vĩ mô ở cuối bài đăng này (đáng kể giống như macro Delete_Empty_Rows_2) được đề xuất bởi công nghệ tự xưng Geek Ankit Kaul? Làm thế nào về việc thêm hộp thoại nơi người dùng của sổ làm việc Excel của bạn có thể nhập, ví dụ, phạm vi ô mà họ muốn một macro cụ thể để tìm kiếm ô trống trong?
Tôi đã giải thích tại sao một số chuyên gia Excel khuyên bạn tránh sử dụng phương thức Select và thuộc tính Selection. Một số macro xuất hiện trong hướng dẫn này sử dụng các cấu trúc khác để đạt được mục tiêu xóa các hàng trống hoặc các hàng có các ô trống mà không sử dụng Select hoặc Selection.
Bạn có thể nghĩ ra các lựa chọn thay thế khác mà bạn có thể sử dụng để tránh sử dụng phương pháp Chọn hoặc thuộc tính Lựa chọn không?
Làm thế nào về việc sử dụng các biến và gán phạm vi trong mã VBA, như đề xuất tại TheSpreadsheetGuru hoặc bởi nhà phân tích kinh doanh PNRao ở đây?
Có điều gì bạn có thể làm để xử lý các ô có khoảng trống được tạo bởi dấu ngoặc kép (“”) không?
Tôi sẽ đề cập sâu hơn về chủ đề này trong các hướng dẫn trong tương lai nhưng, trong khi đó, cách sử dụng “.Value = .Value” theo gợi ý của erik.van.geit tại Mr.Excel.com và được triển khai trong macro Delete_Blank_Rows_2?
Cấu trúc macro Delete_Empty_Rows_2 một bài kiểm tra có điều kiện là "Nếu không BlankRows là không có gì".
Bạn có thể hỏi một câu hỏi tương tự đáng kể bằng cách sử dụng một cấu trúc khác không? Làm thế nào về việc sử dụng “Nếu BlankRows là không có gì” theo đề xuất của chuyên gia Excel John Walkenbach tại Office Archive?
Excel VBA xóa các dòng trống hoặc rỗng: 5 ví dụ macro dễ sử dụng
Bạn có thể nhận thức được những phiền nhiễu gây tốn nhiều thời gian khi làm sạch dữ liệu. Theo tờ New York Times cho biết, theo các cuộc phỏng vấn và ước tính của chuyên gia, các nhà khoa học dữ liệu thì việc làm sạch dữ liệu bằng các cách thủ công “chi tiêu từ 50% đến 80% thời gian của họ và họ có thể bị sa thải trong công việc thu thập và chuẩn bị dữ liệu kỹ thuật số” bằng những cách như vậy.
Như bạn có thể biết, quá trình làm sạch dữ liệu với Excel có thể được tự động hóa ở một mức độ nhất định bằng cách sử dụng các công cụ chức năng Văn bản sắn có hoặc macro.
Trong hướng dẫn này, tôi tập trung vào một tình huống rất "Nghiêm trọng", khó chịu và phổ biến khi làm việc với dữ liệu: sự tồn tại của các hàng trống và các ô trong tập hợp dữ liệu. Theo giải thích của Michael Alexander, tác giả của một số cuốn sách về phân tích kinh doanh với Access và Excel, trong 101 Sẵn sàng để sử dụng Excel Macro:
Làm việc với Excel đủ lâu và bạn sẽ thấy rằng các hàng trống có thể gây ra sự cố trên nhiều cấp độ.
Excel Trick's Ankit Kaul đề cập đến cách các hàng trống có thể làm cho cuộc sống của bạn đau đàu khi sử lý công việc trên một tập hợp dữ liệu khổng lồ, ví dụ, bạn dự định nhập một trang tính Excel vào các ứng dụng khác, chẳng hạn như Access.
Điểm chính là: bạn muốn có thể xóa các hàng trống hoặc hàng có ô trống nhanh chóng và dễ dàng. Bạn có thể làm điều này, trong khi đó một số những người khác có thể là không, bằng cách sử dụng macro.
Có một số cách để tạo macro xóa hàng trống hoặc hàng có ô trống. Trong thực tế, bạn có thể tìm thấy nhiều đề xuất khác nhau trên Internet.
Trong hướng dẫn này, tôi chỉ cho bạn 5 ví dụ phổ biến nhất của macro để xóa các hàng hoặc hàng trống với các ô trống một cách nhanh chóng và dễ dàng. Bằng chính mã VBA, mỗi macro được kèm theo một lời giải thích rất cụ thể về cách mỗi ứng dụng được cấu trúc và mục đích của mỗi câu lệnh là gì. Những giải thích kỹ lưỡng này sẽ giúp bạn hiểu những gì vĩ mô thực sự đang làm và, đồng thời, cải thiện sự hiểu biết của bạn về macro và Visual Basic cho các ứng dụng.
5 macro mà tôi giải thích và khám phá trong hướng dẫn này đạt được những điều sau đây:
3 macro đầu tiên xóa một hàng hoàn chỉnh dựa trên việc nó có ô trống hay không. Chính xác hơn:
Macro # 1, có tên là Delete_Blank_Rows, xóa toàn bộ các hàng dựa trên việc một ô của hàng đó trong một phạm vi nhất định có ô trống.
Macro # 2, có tên là Delete_Blank_Rows_2, đạt được mục đích tương tự như Macro # 1 nhưng sử dụng một cấu trúc khác. Trong khi Macro # 1 sử dụng phương thức Select và thuộc tính Selection (cả hai đều giải thích bên dưới), Macro # 2 được cấu trúc xung quanh câu lệnh With… End With (mà tôi cũng giải thích bên dưới).
Macro # 3, có tên là Delete_Blank_Rows_3, xóa toàn bộ các hàng dựa trên việc một ô của hàng đó trong vùng chọn hiện tại có ô trống.
Sự khác biệt chính giữa Macro # 3 so với Macros # 1 và # 2 là Delete_Blank_Rows_3 hoạt động với dải ô mà bạn đã chọn ngay trước khi thực thi macro. Delete_Blank_Rows và Delete_Blank_Rows_2 hoạt động trên một phạm vi ô được cố định trong mã VBA. Về cơ bản, Macros # 1 và # 2 làm việc với các tham chiếu tuyệt đối trong khi Macro # 3 làm việc với các tham chiếu tương đối.
2 macro cuối cùng xóa các hàng khi toàn bộ hàng trống. Sự khác biệt chính giữa Macro # 4 (có tên là Delete_Empty_Rows) và Macro # 5 (có tên là Delete_Empty_Rows_2) là cách thức chúng được cấu trúc.
Bạn có thể sử dụng phác thảo sau để bỏ qua macro (hoặc phần) mà bạn quan tâm:
Vì có một số cách để bạn có thể cấu trúc macro để xóa các hàng hoặc hàng trống với các ô trống, ở cuối hướng dẫn này, tôi giới thiệu một số cách mà bạn có thể sửa đổi bất kỳ macro nào xuất hiện ở đây để trở thành một tiện ích tốt hơn để đạt được các mục tiêu cụ thể của bạn. Ngoài ra, vì tôi biết rằng có rất nhiều cách khác để tiếp cận vấn đề, tôi khuyến khích bạn chia sẻ kiến thức và kinh nghiệm của bạn trong phần bình luận ở cuối bài viết này.
Hãy bắt đầu bằng cách xem xét dữ liệu mẫu mà tôi sử dụng để minh họa cách xóa các hàng trống hoặc hàng với các ô trống trong Excel một cách nhanh chóng và dễ dàng bằng cách sử dụng các macro…
Macro để xóa hàng trống hoặc hàng với ô trống: Dữ liệu mẫu
Như thường lệ, mọi thứ tôi giải thích trong hướng dẫn macro Excel này được minh họa bằng một ví dụ thực tế. Trong trường hợp này, tôi sử dụng dữ liệu thị trường chứng khoán.
Chính xác hơn, tôi đã sử dụng Google Finance để lấy dữ liệu cổ phiếu lịch sử cho mỗi ngày giao dịch của năm 2014 của các công ty sau:
Microsoft.
Google.
Apple.
Amazon.
Dữ liệu đã được dán trong một cửa sổ làm việc Excel. Excel VBA này xóa dòng trống hoặc các dòng có ô trống hướng dẫn được kèm theo của sổ làm việc Excel chứa dữ liệu và các macro mà tôi sử dụng. Bạn có thể truy cập miễn phí ngay lập tức vào các file làm việc mẫu này bằng cách đăng ký Bản tin trên trang.
Bảng gốc với tất cả các thông tin trông gần như sau (đây chỉ là một ảnh chụp màn hình một phần):
Như bạn có thể thấy, đây là một bảng khá lớn:
Có 4 công ty.
Đối với mỗi công ty, có 252 ngày dữ liệu.
Đối với mỗi ngày và công ty, có 5 mẩu dữ liệu: 4 giá cổ phiếu (mở, cao, thấp, đóng) và khối lượng.
Hãy tưởng tượng rằng bạn đang làm việc với tập dữ liệu này và tìm ra rằng có thể có một số ô trống hoặc các hàng trống và bạn cần xóa những hàng bị ảnh hưởng. Như bạn có thể hình dung, việc làm sạch bộ dữ liệu đó theo cách thủ công có thể vô cùng tẻ nhạt và có một nguy cơ đáng kể về việc mắc lỗi, ví dụ, thiếu một số ô trống.
May mắn thay, bằng cách sử dụng macro tương đối đơn giản, bạn có thể lấy các tập dữ liệu rất lớn và xóa toàn bộ các hàng có ô trống trong chúng hoặc hoàn toàn trống. Hãy bắt đầu xem xét các macro này…
Macro # 1: Xóa hàng dựa trên bất kỳ ô nào trong phạm vi đã cho là trống (tùy chọn 1)
Giả sử bạn đang sẵn sàng phân tích hiệu suất của các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014. Ông chủ của bạn cần có ý tưởng sơ bộ về cách những cổ phiếu này thực hiện trong năm 2014 trước một cuộc họp mà anh ấy có trong nửa giờ .
Bạn đổ xô vào máy tính của bạn, mở sổ làm việc Excel có chứa tất cả thông tin nhưng…
Bạn nhận thấy rằng ai đó trong văn phòng của bạn đã nhận được dữ liệu trước khi bạn và vô tình xóa một số giá đóng cửa của giá cổ phiếu của Microsoft. Để chính xác, thay vì 252 ngày dữ liệu, bạn chỉ có 230 ngày dữ liệu trong cột cụ thể này.
Trong khi đó kết nối Internet của bạn không hoạt động, do đó bạn sẽ không thể thay thế dữ liệu (bằng cách nhận nó từ Google Finance) vào thời điểm trước cuộc họp của ông chủ.
Sau khi đi đến bàn của đồng nghiệp, người vô tình xóa dữ liệu và la mắng anh ta, bạn quay trở lại căn phòng của bạn. Bạn không thực sự chắc chắn làm thế nào để giải thích cho ông chủ của bạn rằng bạn sẽ không thể cung cấp các dữ liệu cần thiết trước khi cuộc họp của mình diễn ra.
Nhưng sau đó…
Bạn bắt đầu hỏi một số câu hỏi hay hơn. Ví dụ, bạn tự hỏi mình:
Tôi có thể dọn sạch dữ liệu và sửa sổ làm việc để dữ liệu bị thiếu không gây ra sự cố không?
Dữ liệu bị thiếu có thực sự quan trọng không? Ông chủ của tôi chỉ muốn có được một ý tưởng thô về hiệu suất chia sẻ trong năm 2014. Tôi có thể cung cấp chỉ sử dụng dữ liệu có sẵn không?
Tôi không thực sự ủng hộ rằng bạn làm điều này thường xuyên. Nhưng trong trường hợp đặc biệt này, nó có thể hoạt động và chắc chắn tốt hơn là cung cấp cho sếp của bạn không có dữ liệu nào cả.
Tôi có thể cung cấp cho sếp của tôi một phân tích xem xét những ngày mà tôi có tất cả thông tin không?
Chắc chắn. Bạn có thể làm điều này bằng cách xóa các hàng mà bạn không có tất cả thông tin và sau đó thực hiện phân tích của bạn.
Làm thế nào tôi có thể xóa các hàng mà tôi không có tất cả thông tin? Chính xác hơn, làm thế nào tôi có thể xóa một hàng đầy đủ tùy thuộc vào việc một ô trong cột mà giá đóng của Microsoft chia sẻ có trống không?
Có một số cách để đạt được mục tiêu này, chẳng hạn như sử dụng 3 macro mà tôi giải thích dưới đây (Macro # 1, Macro # 2 và Macro # 3 của hướng dẫn này).
Nếu bạn biết phạm vi mà bạn muốn tìm kiếm ô trống và, dựa trên kết quả đó, hãy xóa toàn bộ hàng, macro dưới đây (được đề xuất bởi rs2k tại Mr.Excel.com) có thể hữu ích.
Macro cụ thể này xóa toàn bộ hàng nếu có bất kỳ ô trống nào trong phạm vi được chỉ định trong mã VBA. Trong ví dụ này, bạn có thể sử dụng macro để xóa tất cả các hàng mà giá đóng của Microsoft chia sẻ bị thiếu.
Lỗi khi tiếp tục lại
Phạm vi (“E6: E257”).
Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Tôi đặt tên macro là “Delete_Blank_Rows”, gán phím tắt “Ctrl + Shift + D” và thêm mô tả ngắn gọn và lấy mã VBA sau:
Chúng ta hãy xem xét vĩ mô từng dòng để hiểu quá trình.
Dòng # 1: Khi lỗi tiếp tục lại
Theo giải thích của Microsoft ở đây, trên báo cáo lỗi làm như sau:
Bật quy trình xử lý lỗi.
Chỉ định vị trí của thủ tục trong quy trình.
Lý do chính để sử dụng báo cáo Lỗi là để bẫy lỗi, xử lý chúng và cải thiện trải nghiệm người dùng. Nếu xảy ra lỗi thời gian chạy và không có báo cáo Lỗi trên thì lỗi sẽ gây tử vong. Thực thi bạn dừng macro và Excel sẽ hiển thị thông báo lỗi.
Theo giải thích của John Walkenbach, một trong những cơ quan chức năng nổi bật nhất của Excel, trong Lập trình Dummies của Excel VBA:
Sử dụng câu lệnh On Error trong mã VBA cho phép bạn bỏ qua việc xử lý lỗi tích hợp của Excel và sử dụng mã xử lý lỗi của riêng bạn.
Câu lệnh đầy đủ ở trên là trình xử lý lỗi Tiếp tục Lỗi khi tiếp tục. Các loại xử lý lỗi cụ thể này thường được sử dụng, trong số những người khác, khi bạn đang gặp lỗi và biết cách xử lý chúng. Theo giải thích của Microsoft ở đây, trình xử lý lỗi On Error Resume Next hoạt động bằng cách hướng dẫn Excel tiếp tục thực hiện macro với câu lệnh sau đó xảy ra lỗi.
Lý do chính tại sao trình xử lý lỗi On Error Resume Next phải được bao gồm trong macro Delete_Blank_Rows là có, thực sự, lỗi được mong đợi khi thực thi macro. Đặc biệt, nếu phạm vi mà macro tìm kiếm các ô trống không thực sự có bất kỳ ô trống nào, sẽ có lỗi thời gian chạy và Excel sẽ ngừng thực thi macro trừ khi có lỗi xử lý lỗi thích hợp.
Trên các báo cáo lỗi thường áp dụng cho tất cả các câu lệnh tiếp theo trong mã VBA cho đến khi câu lệnh bị vô hiệu hóa bởi câu lệnh On Error GoTo 0. Nếu không có câu lệnh On Error GoTo 0, câu lệnh On Error chỉ bị vô hiệu hóa khi thủ tục được thoát.
Dòng số 2: Phạm vi (“#: ##”).
Phạm vi ("#: ##") trả về một đối tượng đại diện cho một phạm vi ô (hoặc một ô đơn lẻ). “#: ##” là đối số mà bạn sử dụng để đặt tên cho dải ô.
Chọn kích hoạt đối tượng cụ thể để, trong bước tiếp theo, bạn có thể sử dụng thuộc tính Selection.
Trong ví dụ mà bạn cần tìm kiếm các ô trống trong cột nơi giá đóng của cổ phiếu của Microsoft là phạm vi liên quan là từ ô E6 đến ô E257 như được hiển thị trong 2 ảnh chụp màn hình bên dưới. Do đó, câu lệnh VBA có liên quan cho macro Delete_Blank_Rows là “Phạm vi (“ E6: E257 ″). Chọn ”.
Câu lệnh này dài hơn một chút so với các câu lệnh trước, vì vậy hãy kiểm tra từng phần tử riêng biệt của nó.
Yếu tố # 1: Selection.SpecialCells (xlCellTypeBlanks).
Đây được gọi là phương thức Range.SpecialCells. Mục đích của phương thức Range.SpecialCells là lấy một đối tượng (trong trường hợp này là một phạm vi) biểu diễn các ô phù hợp với kiểu và giá trị được đặt trong dấu ngoặc đơn.
Để hiểu định nghĩa này, chúng ta hãy xem cú pháp thông thường của phương thức Range.SpecialCells: “expression.SpecialCells (Type, Value)”, trong đó:
"Expression" là viết tắt của đối tượng Range. Trong trường hợp này đối tượng dải là Lựa chọn, trả về lựa chọn hiện tại. Trong macro Delete_Blank_Rows, điều này thể hiện phạm vi ô đã được chọn theo câu lệnh trước (Phạm vi (“#: ##”). Chọn).
Trong bảng tính Excel có chứa giá cổ phiếu của Microsoft, Google, Amazon và Apple, Lựa chọn đại diện cho phạm vi giữa các tế bào E6 và E257.
Dấu ngoặc đơn ngay lập tức sau khi SpecialCells bao gồm 2 đối số: XlCellType và giá trị.
XlCellType là đối số bắt buộc chỉ định loại ô nào sẽ được bao gồm. Macro Delete_Blank_Rows yêu cầu các ô được bao gồm trong phạm vi được trả về bởi phương thức là xlCellTypeBlanks. xlCellTypeBlanks, được ngụ ý bởi tên của chúng, các ô trống.
Giá trị là một đối số tùy chọn thường được yêu cầu khi loại là xlCellConstants (ô có chứa hằng số) hoặc xlCellTypeFormulas (các ô có công thức). Macro Delete_Blank_Rows không bao gồm giá trị, có nghĩa là tất cả các ô trống đều được trả về.
Khi áp dụng cho bảng tính Excel với dữ liệu cổ phiếu cho Microsoft, Google, Amazon và Apple, câu lệnh cụ thể này trả về một đối tượng đại diện cho tất cả các ô trống trong phạm vi giữa các ô E6 và E257 (cột nơi giá đóng của cổ phiếu của Microsoft xuất hiện ).
Thuộc tính EntireRow trả về một đối tượng, như bạn có thể mong đợi, là toàn bộ một hàng (hoặc một vài hàng).
Cú pháp của thuộc tính EntireRow là “expression.EntireRow”. Trong trường hợp này, "biểu thức" phải cung cấp một đối tượng.
Trong trường hợp của macro Delete_Blank_Rows, biểu thức là Selection.SpecialCells (xlCellTypeBlanks). Kết quả là, trong ví dụ tôi đã sử dụng, thuộc tính EntireRow trả về tất cả các hàng (toàn bộ) chứa các ô trống trong phạm vi ô từ E6 đến E257 (nơi giá đóng của cổ phiếu của Microsoft).
Yếu tố # 3: Xóa.
Phương thức Delete (“Delete”) xóa một đối tượng. Trong ví dụ trên, đối tượng bị xóa là toàn bộ các hàng chứa ô trống trong cột nơi giá đóng của Microsoft được hiển thị.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows
Bây giờ tôi đã cho bạn thấy mỗi câu lệnh trong macro Delete_Blank_Rows làm gì, bạn có một sự hiểu biết tốt hơn về cách Macro # 1 hoạt động. Như bạn có thể biết bây giờ, những gì vĩ mô này làm trong một nutshell là như sau:
Bước # 1: Macro chọn một phạm vi ô mà bạn đã xác định.
Trong ví dụ trên, phạm vi ô là cột mà giá đóng của cổ phiếu của Microsoft xuất hiện hoặc chính xác hơn, các tế bào E6 đến E257.
Bước # 2: Macro đi qua phạm vi được chọn để tìm tất cả các ô trống.
Bước # 3: Bất cứ khi nào macro tìm thấy ô trống, nó sẽ chọn toàn bộ hàng.
Bước 4: Khi macro đã tìm thấy tất cả các ô trống trong phạm vi có liên quan và đã chọn toàn bộ các hàng có các ô đó, nó sẽ xóa các hàng.
Macro bao gồm trình xử lý lỗi Tiếp tục Lỗi khi tiếp tục vì mục đích đảm bảo rằng việc thực thi chương trình không dừng, ví dụ: trong trường hợp macro không tìm thấy bất kỳ ô trống nào trong phạm vi mà bạn đã yêu cầu tìm kiếm trong đó. Lỗi cụ thể này không có tác dụng phụ khi thực hiện macro và do đó, có thể được để lại “không được cố định”.
Đồ họa, quá trình tiếp theo là macro Delete_Blank_Rows trông gần như sau:
Cuối cùng, chúng ta hãy quay trở lại ví dụ nơi bạn đang chuẩn bị một bản tóm tắt về hiệu suất của các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014 cho sếp của bạn. Hãy xem hình ảnh dưới đây để xem cách macro Delete_Blank_Rows hoạt động trong thực tế và quan trọng nhất là cách nó có thể giúp bạn hoàn thành công việc của mình nhanh hơn và giữ cho sếp của bạn vui vẻ 😉. Lưu ý cách các hàng có các ô trống trong cột E (giá đóng của cổ phiếu Microsoft), chẳng hạn như các hàng 11 và 20, sẽ bị xóa.
Macro # 2: Xóa hàng dựa trên bất kỳ ô nào trong phạm vi đã cho là trống (tùy chọn 2)
Chúng ta hãy xem lại mã VBA phía sau Delete_Blank Rows_Macro:
Và cho phép tôi hỏi bạn một câu hỏi:
Bạn sẽ sửa đổi mã VBA như thế nào để tránh sử dụng phương thức Select và thuộc tính Selection?
Nếu bạn xem xét kỹ 2 câu lệnh chính trong mã VBA của macro Delete_Blank_Rows, bạn có thể nhận thấy cả “Chọn” và “Lựa chọn” đều tham chiếu đến cùng một điều: phạm vi ô, trong ví dụ cụ thể này, là “Phạm vi (“ E6: E257 ″) ”.
Do đó, theo gợi ý của erik.van.geit tại Mr.Excel.com, bạn có thể sử dụng câu lệnh With With ... End With. Mục đích của câu lệnh With… End With là đơn giản hóa cú pháp của macro bằng cách thực hiện một số câu lệnh, tất cả đều tham chiếu đến cùng một đối tượng hoặc cấu trúc. Bạn có thể tìm thấy một ví dụ về một câu lệnh With… End With rất đơn giản trong hướng dẫn macro Excel này cho người mới bắt đầu.
Nếu bạn đang tự hỏi tại sao nó có thể được khuyến khích để tránh sử dụng phương pháp Select và tài sản lựa chọn, tôi cung cấp cho bạn một số lý do ở phần cuối của phần này.
Trong trường hợp này, các báo cáo có liên quan nhất của macro là như sau:
Tôi đặt tên macro là "Delete_Blank_Rows_2", gán phím tắt "Ctrl + Shift + E" và thêm mô tả ngắn về macro và mã VBA đầy đủ trông như sau:On Error Resume NextWith Range(“E6:E257”).Value = .Value.SpecialCells(xlCellTypeBlanks).EntireRow.DeleteEnd With
Chúng ta hãy xem xét từng dòng mã để hiểu quy trình tiếp theo là macro này là gì.
Dòng # 1: Khi lỗi tiếp tục lại
Bạn đã biết rằng tuyên bố này:
Là một lỗi Tiếp tục xử lý lỗi tiếp theo.
Hướng dẫn Excel tiếp tục thực thi macro với câu lệnh theo sau điểm xảy ra lỗi thời gian chạy.
Có mục đích chính, trong các macro đã được nghiên cứu trong hướng dẫn này, để tránh việc thực hiện macro dừng trong trường hợp không có ô trống trong phạm vi mà bạn đã yêu cầu macro tìm kiếm trong đó.
Dòng 2. Với phạm vi (“#: ##”)
Từ “With” báo hiệu sự bắt đầu của câu lệnh With… End With. Trong các kiểu câu lệnh này, từ With thường được theo sau bởi một đối tượngExpression, được ngụ ý bởi tên của nó, “một biểu thức đánh giá một đối tượng“. Trong trường hợp này, objectExpression là Range (“#: ##”).
Hình ảnh dưới đây cho thấy toàn bộ Với ... Kết thúc với tuyên bố:
Như một hệ quả của các bên trên, Range (“#: ##”) là đối tượng mà mỗi dòng trong câu lệnh With… End With tham chiếu đến. “#: ##” là nơi bạn xác định phạm vi thực tế mà các câu lệnh sẽ hoạt động.
Tôi tiếp tục làm việc với ví dụ nơi bạn đang cố gắng sửa bảng tính Excel có giá hàng ngày cho các cổ phiếu của Microsoft, Google, Apple và Amazon trong năm 2014 và cần xóa một số hàng nhất định tùy thuộc vào việc giá đóng cửa của cổ phiếu của Microsoft có xuất hiện hay không hay không. Trong trường hợp này, bạn biết rằng "#: ##" nên được thay thế bằng "E6: E257".
Dòng số 3: .Value = .Value
Đây là một ví dụ về thuộc tính Range.Value đang hoạt động. Thuộc tính này trả về hoặc đặt giá trị cho một dải ô được chỉ định.
Vì dòng nằm trong câu lệnh With… End With, nó tham chiếu đến phạm vi ô giữa E6 và E257.
bạn có thấy điều gì đang xảy ra ở đây không? Tại sao tuyên bố bao gồm .Value trên cả hai mặt của dấu bằng?
Như bạn có thể hình dung, hiệu ứng của kiểu câu lệnh này là thiết lập các giá trị của phạm vi bằng với các giá trị hiện tại của chúng. Trong Excel, điều này rất hữu ích khi bạn đang tìm kiếm các ô trống hoặc cho các ô xuất hiện trống nhưng thực sự có dấu ngoặc kép (“”). Theo giải thích của Microsoft ở đây, các ô trống vì chúng chứa dấu ngoặc kép (“”) thường hoạt động khác với các ô hoàn toàn trống.
Điều này có nghĩa rằng, khi một macro đang tìm kiếm các ô trống, nó sẽ không nhìn vào các ô có dấu ngoặc kép (“”) là rỗng. Do đó, các tế bào đó không được xem xét trong quá trình xóa. Như được giải thích bởi nguồn macro này, erik.van.geit tại diễn đàn của ông Excel, câu lệnh “.Value = .Value” chuyển đổi các ô có dấu ngoặc kép thành khoảng trống thực tế. Những khoảng trắng sau đó có thể được sử dụng bởi các macro cho mục đích xác định những hàng cần xóa.
Dòng # 4: .SpecialCells (xlCellTypeBlanks) .EntireRow.Delete
Vì dòng này nằm bên trong câu lệnh With… End With, nó làm cho tham chiếu đến đối tượngExpression xuất hiện khi khai báo. Do đó, trong trường hợp này, dòng mã này hoạt động với Range (“#: ##”).
Nói cách khác, dòng mã này nói “Range (“ #: ## ”). SpecialCells (xlCellTypeBlanks) .EntireRow.Delete”. Điều này, về bản chất, giống như “Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete” xuất hiện trong Macro # 1. Sự khác biệt duy nhất là trong mục đầu tiên: (Phạm vi (“#: ##”) so với Lựa chọn).
Bạn đã biết điều đó:
Selection.SpecialCells (xlCellTypeBlanks) là một ví dụ về phương thức Range.SpecialCells.
Phạm vi (“#: ##”) SpecialCells (xlCellTypeBlanks) là một ví dụ khác của phương thức như vậy.
Mục đích của phương pháp này là để có được một đối tượng đại diện cho các ô phù hợp với một loại và giá trị nhất định, cả hai đều xuất hiện trong dấu ngoặc đơn.
Trong Macro # 1, hiệu quả của việc sử dụng phương pháp này là để có được tất cả các ô trống trong phạm vi ô đã chọn, với phạm vi ô đó được biểu diễn bằng phép chọn.
Xem xét ở trên, những gì bạn mong đợi là tác dụng của Range ("#: ##"). SpecialCells (xlCellTypeBlanks)?
Nếu bạn trả lời rằng hiệu ứng là như nhau, bạn nói đúng.
“Range (“ #: ## ”). SpecialCells (xlCellTypeBlanks)” trả về tất cả các ô trống trong phạm vi (“#: ##”). Trong ví dụ mà tôi đã sử dụng, điều này có nghĩa là tất cả các ô trống trong cột nơi giá đóng của cổ phiếu của Microsoft xuất hiện.
Dòng này đơn giản là kết thúc câu lệnh With… End With.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows_2
Như bạn có thể mong đợi, quá trình theo sau bởi macro này (Delete_Blank_Rows_2) là rất giống với macro được sử dụng bởi macro trước, trong đó câu lệnh With… End With không được sử dụng (Delete_Blank_Rows). Tuy nhiên, nó không chính xác cùng một quá trình, vì vậy chúng ta hãy tóm tắt những gì mà macro Delete_Blank_Rows_2 thực sự làm:
Bước # 1: Macro làm rõ rằng một loạt các câu lệnh hoạt động với một phạm vi cụ thể, mà không cần phải lặp lại phạm vi là mỗi lần.
Trong ví dụ mà tôi đã sử dụng đây là phạm vi ô từ E6 đến E257, nơi giá đóng cửa cổ phiếu của Microsoft xuất hiện.
Bước # 2: Macro lấy tất cả các ô trong phạm vi và đặt giá trị của chúng bằng với giá trị hiện tại của chúng. Tác động chính của việc này là nó biến các ô trống rỗng vì chúng có dấu ngoặc kép (“”) vào khoảng trống thực tế.
Bước # 3: Macro đi qua phạm vi cụ thể đó tìm kiếm các ô trống.
Bước # 4: Khi macro tìm thấy ô trống, macro sẽ chọn toàn bộ hàng có các ô trống đó.
Bước # 5: Macro xóa các hàng có liên quan.
Cũng giống như trong trường hợp của macro Delete_Blank_Rows, macro Delete_Blank_Rows_2 bao gồm câu lệnh On Error để bẫy lỗi được dự kiến sẽ xảy ra nếu không có ô trống trong phạm vi macro hoạt động.
Như bạn có thể thấy, hiệu ứng là như nhau và quá trình này rất giống nhau; nhưng nó không giống hệt nhau. Để làm cho điểm này rõ ràng hơn, hãy xem xét quá trình cuối cùng này (Delete_Blank_Rows_2) đồ họa:
Bạn sẽ rất vui khi biết rằng, quay trở lại ví dụ và những thách thức bạn đang gặp phải tại văn phòng khi cố gắng chuẩn bị bản tóm tắt về hiệu suất trong năm 2014 của các cổ phiếu của Microsoft, Google, Apple và Amazon, macro đạt được kết quả cuối cùng giống như Macro # 1. Hãy xem hình ảnh bên dưới để xác nhận rằng các hàng mà giá đóng của Microsoft chia sẻ bị thiếu sẽ bị xóa.
Macro # 3: Xóa hàng dựa trên các ô có trống hay không
Chúng ta hãy xem xét khả năng thứ ba để xóa các hàng khi một số ô trống. Mã sau được đề xuất tại ozgrid.com:
On Error Resume NextSelection.EntireRow.SpecialCells(xlBlanks).EntireRow.DeleteOn Error GoTo 0
Tôi đặt tên macro là “Delete_Blank_Rows_3”, gán phím tắt “Ctrl + Shift + B” và thêm mô tả macro. Mã VBA đầy đủ được VBE hiển thị như sau:
Bạn có thể nhận thấy 2 điều khi nhìn vào macro này:
# 1: Không giống như các macro trước, mã VBA không chỉ định phạm vi ô mà nó hoạt động. Nó bắt đầu bằng cách làm cho tham chiếu đến "Lựa chọn" mà, như bạn đã biết, là lựa chọn hiện tại. Do đó, trước khi thực hiện macro Delete_Blank_Rows_3, bạn cần phải chọn phạm vi ô bạn muốn làm việc.
Bên dưới, tôi giải thích cách bạn có thể thực hiện lựa chọn này để đạt được mục tiêu của mình. Bạn sẽ ngạc nhiên bởi thực tế là bạn thực sự có thể chọn một cột không có ô trống.
# 2: Mã này gần giống với macro Delete_Blank_Rows_2. Đặc biệt, bạn có thể nhận thấy rằng câu lệnh "Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete" gần như là các tên như câu lệnh "Range (" E6: E257 ″). SpecialCells (xlCellTypeBlanks) .EntireRow.Delete " xuất hiện trong Macro # 2 dưới dạng câu lệnh With… End With.
Chúng ta hãy xem xét từng dòng mã để hiểu quy trình là gì tiếp theo là macro này, và nó khác với những gì mà chúng ta đã kiểm tra trước đây.
Dòng # 1: Khi lỗi tiếp tục lại
Bạn đã biết rằng đây là một câu lệnh On Error chỉ định rằng, khi một lỗi thời gian chạy xảy ra, Excel sẽ tiếp tục thực thi câu lệnh sau đó xảy ra lỗi.
Trong macro cụ thể này, lý do chính để sử dụng On Error Resume Tiếp theo là dòng "Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete" tạo ra một lỗi thời gian chạy khi phạm vi ô được chọn không chứa bất kỳ sản phẩm nào ô. Để tránh việc thực thi macro bị dừng do lỗi thời gian chạy dự kiến này, On Error Resume Next được sử dụng để cho phép thực thi ứng dụng tiếp tục.
Dòng # 2: Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete
Macro Delete_Blank_Rows bao gồm một câu lệnh rất giống với câu lệnh sau: “Selection.SpecialCells (xlCellTypeBlanks) .EntireRow.Delete”. Tuy nhiên, bạn sẽ nhận thấy một vài sự khác biệt ở đầu hàng. Do đó, chúng ta hãy xem xét từng yếu tố một cách riêng biệt để hiểu những gì đang diễn ra.
Yếu tố # 1: Selection.EntireRow.
Đây là một thể hiện của thuộc tính Range.EntireRow, nó trả về một dãy đại diện cho toàn bộ một hàng hoặc một hàng.
Macro Delete_Blank_Rows_3 không chỉ rõ phạm vi ô mà bạn đang làm việc. Ví dụ, cả macro Delete_Blank_Rows và macro Delete_Blank_Rows_2 đã làm việc với một phạm vi được xác định cụ thể, trong các ví dụ, là từ ô E6 đến ô E257. Delete_Blank_Rows_3 hoạt động với Lựa chọn, nghĩa là nó hoạt động với lựa chọn hiện tại.
Như một hệ quả của những điều trên, những gì Selection.EntireRow làm là:
Thực hiện lựa chọn hiện tại.
Trả về một đối tượng đại diện cho toàn bộ hàng (hoặc hàng) có chứa lựa chọn hiện tại của bạn.
Cách macro Delete_Blank_Rows_3 được cấu trúc có nghĩa là bạn không phải thực sự chọn cột chứa các ô trống cho phạm vi đó để được xem xét. Lấy sổ làm việc Excel có chứa dữ liệu hiệu suất cổ phiếu cho Microsoft, Google, Amazon và Apple làm ví dụ. Bạn biết rằng cột chứa một số ô trống là cột E (hiển thị giá đóng cửa của phần chia sẻ của Microsoft). Tuy nhiên, bạn có thể chọn cột G trước khi thực hiện macro Delete_Blank_Rows_3.
Yếu tố # 2: SpecialCells (xlBlanks).
Bạn đã biết rằng phương thức này được gọi là phương thức Range.SpecialCells và mục đích của nó là lấy một đối tượng đại diện cho các ô phù hợp với kiểu và giá trị xuất hiện bên trong dấu ngoặc đơn.
Trong trường hợp của macro Delete_Blank_Rows_3, đối tượng được tìm nạp là một dãy đại diện cho các ô có kiểu xlBlanks.
Bạn có thể tự hỏi:
XlBlanks là gì? nó đứng để làm gì? Nó khác với xlCellType xlCellTypeBlanks?
Như được giải thích bởi Tom Ogilvy ở đây, tài liệu xung quanh sự khác biệt giữa xlBlanks và xlCellTypeBlanks đôi khi không rõ ràng như bạn mong muốn. Nói chung và theo giải thích của chuyên gia Excel David McRitchie, xlBlanks là thuật ngữ được sử dụng trong Excel 1995 và các phiên bản trước. xlCellTypeBlanks đã được sử dụng kể từ Excel 1997. Tuy nhiên, cho các mục đích cơ bản của Visual Basic cho các ứng dụng, cả xlBlanks và xlCellTypeBlanks đều đánh giá cùng một giá trị.
Do đó, trong macro Delete_Blank_Rows_3 được hiển thị ở trên, “xlBlanks” đề cập đến các ô trống.
Các ô trống được tìm kiếm ở đâu?
Bạn biết rằng phần đầu tiên của câu lệnh (Selection.EntireRow) lấy các ô đã chọn và trả về một đối tượng đại diện cho toàn bộ các hàng nơi các ô được chọn. Phương thức Range.SpecialCells tìm thấy bất kỳ ô trống nào trong các hàng đó.
Quay lại dữ liệu hiệu suất cổ phiếu lịch sử cho Microsoft, Google, Amazon và Apple, điều này có nghĩa là ngay cả khi bạn đã chọn một cột không có bất kỳ hàng trống nào (chẳng hạn như cột G trong ảnh chụp màn hình bên dưới), Phạm vi. Phương thức SpecialCells có thể tìm các ô trống trong các cột khác của bảng, như cột mà giá đóng của cổ phiếu Microsoft xuất hiện (cột E).
Bạn đã thấy thuộc tính Range.EntireRow và phương thức Delete trong hành động trước trong hướng dẫn này. Do đó, tôi chắc chắn bạn sẽ không gặp bất kỳ vấn đề nào trong việc tìm ra phần cuối của câu lệnh này.
Đoán nào?
Như bạn có thể, và chính xác, tưởng tượng, sự kết hợp của thuộc tính và phương thức này lấy tất cả các hàng có chứa các ô trống được tìm thấy bởi phương thức Range.SpecialCells ở trên và xóa chúng.
Do đó, macro Delete_Blank_Rows_3 có khả năng có hiệu ứng rất giống với hai macro trước đó (Delete_Blank_Rows và Delete_Blank_Rows_2). Ví dụ: nếu bạn chọn cột G trong sổ làm việc Excel ghi lại hiệu suất cổ phiếu lịch sử cho năm 2014 của một số cổ phiếu (Microsoft, Google, Amazon và Apple) trước khi áp dụng macro Delete_Blank_Rows_3, các hàng có chứa ô trống (ngay cả khi ô trống không nằm trong cột G) sẽ bị xóa. Ảnh chụp màn hình bên dưới hiển thị 2 trong số các hàng bị xóa trong trường hợp này.
Mục đích của câu lệnh On Error GoTo 0 là vô hiệu hóa bất kỳ trình xử lý lỗi nào. Trong trường hợp của macro Delete_Blank_Rows_3, Error GoTo 0 vô hiệu hóa trình xử lý lỗi Tiếp tục Lỗi Tiếp theo xuất hiện trong dòng đầu tiên của phần thân macro.
Tóm tắt quá trình theo sau bởi macro Delete_Blank_Rows_3
Tóm tắt sau đây giải thích, theo nghĩa đơn giản, ý chính của quá trình tiếp theo là macro Delete_Blank_Rows_3:
Bước # 1: Macro lấy lựa chọn hiện tại của bạn và nhận toàn bộ các hàng có chứa lựa chọn đó.
Bước # 2: Tìm kiếm macro cho các ô trống trong các hàng đã thu được.
Bước # 3: Nếu tìm thấy các ô trống, toàn bộ hàng mà chúng được tìm thấy sẽ thu được.
Bước 4: Các hàng mà các ô trống được tìm thấy sẽ bị xóa.
Biểu đồ dòng chảy có liên quan trông gần như sau. Bạn có thể nhận thấy rằng sơ đồ này rất giống với quá trình tiếp theo là macro Delete_Blank_Rows. Tuy nhiên, nếu bạn so sánh các đoạn mã VBA, bạn sẽ nhận thấy rằng có một số khác biệt quan trọng giữa 2 thủ tục. Cụ thể, macro Delete_Blank_Rows_3 không sử dụng phương thức Chọn (mặc dù nó sử dụng Lựa chọn). Như tôi đã giải thích ở phần cuối của phần trước, đó là lời khuyên phổ biến để tránh sử dụng .Chọn khi có thể.
Hình ảnh bên dưới hiển thị macro Delete_Blank_Rows_3 đang hoạt động. Đối với các mục đích này, và để tiếp tục với cùng một ví dụ được sử dụng ở trên, tôi đã chọn cột G trước khi thực thi macro. Trong sổ làm việc Excel mẫu cho thấy kết quả của việc áp dụng Macro # 3, tôi đã thêm lại 3 hàng để bao gồm tiêu đề mà tôi thường đưa vào sổ làm việc Excel mẫu.
Macro # 4: Xóa hàng khi hàng toàn bộ trống (tùy chọn 1)
Macro từ # 1 đến # 3 rất hữu ích nếu bạn muốn xóa toàn bộ các hàng khi một ô cụ thể trong hàng đó trống. Nhưng điều này có thể không làm việc cho bạn trong mọi trường hợp.
Chúng ta hãy xem lại dữ liệu hiệu suất cổ phiếu trong năm 2014 của Microsoft, Google, Apple và Amazon.
Nhưng trước tiên…
Bạn có nhớ đồng nghiệp của bạn? Một trong những người không biết làm thế nào để sử dụng Excel và vô tình xóa một số giá đóng cửa cho cổ phiếu của Microsoft?
Vâng ... Có vẻ như anh ta đã "làm việc" một lần nữa trên sổ làm việc Excel đặc biệt này và, vô tình, thêm một vài hàng trống trong suốt bảng. Sếp của bạn mở tệp này và không thích những gì anh ta thấy:
Vì anh ấy biết bạn là trình hướng dẫn Excel của văn phòng, anh ấy yêu cầu bạn xóa các hàng đó.
Sao bạn có thể làm được vậy?
Với một macro, tất nhiên! Bạn không muốn lãng phí thời gian của bạn đi bằng tay thông qua toàn bộ bảng tìm kiếm các hàng hoàn toàn trống rỗng.
Vì vậy, hãy làm điều đó bằng cách sử dụng mã VBA sau, được đề xuất tại ozgrid.com:
Tôi đặt tên macro là “Delete_Empty_Rows”, gán phím tắt “Ctrl + Shift + M” và bao gồm một mô tả ngắn gọn. Toàn bộ macro trông như sau trong VBE.Dim iCounter As LongWith Application.Calculation = xlCalculationManual.ScreenUpdating = FalseFor iCounter = Selection.Rows.Count To 1 Step -1If WorksheetFunction.CountA(Selection.Rows(iCounter)) = 0 ThenSelection.Rows(iCounter).EntireRow.DeleteEnd IfNext iCounter.Calculation = xlCalculationAutomatic.ScreenUpdating = TrueEnd With
Tương tự như macro Delete_Blank_Rows_3, Delete_Empty_Rows hoạt động trong phạm vi đã chọn. Điều này có nghĩa là nó xóa các hàng trống của phạm vi mà bạn đã chọn và, do đó, bạn phải chọn phạm vi dữ liệu bạn muốn làm việc (xóa các hàng trống từ) trước khi thực hiện macro.
Để hiểu rõ hơn về macro cụ thể này, chúng ta hãy xem xét từng dòng mã nào.
Dòng số 1: Dim iCounter As Long
Câu lệnh Dim được sử dụng để khai báo một biến. Trong trường hợp này, biến được khai báo là iCounter. Từ khóa As được sử dụng để khai báo iCounter như một biến của kiểu dữ liệu Long. Do đó, iCounter giữ số nguyên.
Về mặt lý thuyết, iCounter cũng có thể được khai báo là một biến của kiểu dữ liệu Integer. Lý do tại sao macro cụ thể này sử dụng Long thay vì Integer, như được giải thích ở đây, rằng kiểu dữ liệu Long có thể chứa các số nguyên lớn hơn các số nguyên phù hợp với kiểu dữ liệu Integer. Nói cách khác, Long hữu ích trong trường hợp phạm vi bạn chọn để áp dụng macro có quá nhiều hàng.
Bạn có thể tự hỏi:
Mục đích của việc khai báo biến này là gì?
Biến iCounter, như ngụ ý bởi tên của nó, phục vụ như truy cập. Lý do tại sao macro Delete_Empty_Rows cần một bộ đếm là nó được cấu trúc như một vòng lặp. Vòng lặp này được thực hiện bằng cách sử dụng câu lệnh For… Next (mà tôi giải thích dưới đây) lặp lại một nhóm các câu lệnh cụ thể với một số lần nhất định và do đó cần một biến điều khiển (trong trường hợp này là iCounter) để theo dõi bao nhiêu lần vòng lặp đã được lặp lại.
Nếu bạn không hoàn toàn hiểu được lời giải thích này, đừng lo lắng ... Chỉ cần tiếp tục đọc; vào cuối phần này, bạn sẽ hiểu hoàn toàn tất cả những điều trên.
Dòng số 2: Với ứng dụng
Bạn đã biết rằng từ “With” là một tín hiệu cho thấy câu lệnh With… End With đang bắt đầu. Bạn cũng biết rằng cú pháp thông thường cho dòng đầu tiên của câu lệnh như vậy là “Với objectExpression”, trong đó “objectExpression” là một biểu thức đánh giá một đối tượng.
Đối tượng Application là chính Excel hoặc, được giải thích bởi Microsoft, “toàn bộ ứng dụng Microsoft Excel”.
Kết quả của việc trên, bạn biết rằng một loạt các câu lệnh xuất hiện trong câu lệnh With… End With đều làm việc và tham chiếu đến chính ứng dụng Excel.
Ảnh chụp màn hình sau đây cho thấy toàn bộ Với ... Kết thúc với tuyên bố.
Dòng # 3: .Calculation = xlCalculationManual
Dòng này nằm trong câu lệnh With… End With để tham chiếu đến ứng dụng Excel. Vì vậy, bạn có thể nghĩ về nó như là "Application.Calculation = xlCalculationManual = xlCalculationManual".
Thuộc tính Application.Calculation được sử dụng để thiết lập chế độ tính toán mà Excel áp dụng cho sổ làm việc. Trong tuyên bố cụ thể này, chế độ được chọn là xlCalculationManual, có nghĩa là Excel không tính toán lại sổ làm việc một cách tự động, mà đúng hơn là khi bạn yêu cầu nó.
Nói cách khác, câu lệnh này tắt tính toán bảng tính tự động. Mục đích của việc này, như được giải thích tại ozgrid.com, là "tăng tốc độ vĩ mô".
Dòng # 4: .ScreenUpdating = False
Cũng như với câu lệnh trước, dòng này làm việc với ứng dụng Excel và, do đó bạn có thể nghĩ nó là “Application.ScreenUpdating = False”.
Thuộc tính Application.ScreenUpdating được sử dụng để tắt cập nhật màn hình. Mục đích của việc này tương tự như việc tắt tính toán bảng tính tự động: để tăng tốc mã macro.
Dòng # 5: Dành cho iCounter = Selection.Rows.Count To 1 Step –1
Dòng mã này là sự khởi đầu của một câu lệnh Tiếp theo…, là một cách phổ biến để cấu trúc một vòng lặp trong Visual Basic cho các ứng dụng. Câu lệnh này yêu cầu Excel thực hiện một nhóm các câu lệnh liên tục một số lần nhất định.
Cú pháp thông thường cho dòng đầu tiên của câu lệnh Tiếp theo… là “Đối với bộ đếm [Theo kiểu dữ liệu] = bắt đầu Để kết thúc [Bước bước]”. Dòng này, về cơ bản, cho Excel biết bao nhiêu lần để thực hiện vòng lặp bằng cách tạo một bộ đếm đếm số lần lặp lại, cho biết giá trị bắt đầu và kết thúc của bộ đếm đó là gì và tùy ý số lượng truy cập tăng hoặc giảm mỗi lần lặp lại.
Trong macro Delete_Empty_Rows, bộ đếm là biến iCounter. Giá trị khởi đầu của nó là số hàng trong phạm vi bạn đã chọn để làm việc với trước khi thực thi macro, giá trị kết thúc của nó là 1 và nó giảm 1 lần mỗi khi vòng lặp được thực hiện. Nói cách khác, vòng lặp được lặp lại một số lần bằng số hàng trong phạm vi bạn đang làm việc. Điều này có ý nghĩa vì bạn muốn Excel kiểm tra từng hàng của phạm vi đó để xác nhận xem hàng đó có trống hay không và do đó, nên bị xóa.
Nhưng chúng ta hãy xem xét kỹ hơn từng phần tử của dòng mã VBA này trong macro Delete_Empty_Rows để hiểu cách cấu trúc của nó:
“ICounter” là đối số Counter, là một mục được yêu cầu của câu lệnh For… Next. Đối số này là một biến điều khiển, có nghĩa là nó theo dõi số lần lặp lại vòng lặp của cơ thể.
Kiểu dữ liệu không được xác định trong phần thân của vòng lặp này, vì iCounter đã được định nghĩa trước đó (trong câu lệnh đầu tiên của Macro # 4) dưới dạng biến kiểu dữ liệu dài.
“Selection.Rows.Count” (thay cho vị trí “bắt đầu” trong cú pháp câu lệnh) xác định giá trị ban đầu của bộ đếm. Giá trị ban đầu này bằng số hàng trong phạm vi mà bạn đã chọn để làm việc trước khi thực thi macro.
Chúng ta hãy xem xét chính xác cách mà câu lệnh này cho Excel biết giá trị bắt đầu cho bộ đếm nên là gì.
# 1: Selection.Rows là một ví dụ về thuộc tính Range.Rows, nó trả về một đối tượng đại diện cho các hàng trong một phạm vi cụ thể. Bạn đã biết rằng lựa chọn trả về lựa chọn hiện tại. Nói cách khác, Selection.Rows trả về các hàng trong bảng (hoặc phạm vi dữ liệu) mà bạn đã chọn để áp dụng macro.
# 2: Thuộc tính Range.Count, như bạn có thể tưởng tượng, đếm số hàng trong phạm vi bạn đang làm việc và trả về giá trị đó.
“To 1” cho Excel biết giá trị cuối cùng của bộ đếm là gì, rõ ràng là 1. Như bạn có thể thấy, số 1 sẽ thay thế “kết thúc” trong cú pháp chung của câu lệnh Tiếp theo….
“Bước –1” quyết định giá trị mà bộ đếm thay đổi mỗi khi Excel đi qua vòng lặp. Trong trường hợp này, biến điều khiển iCounter giảm 1 mỗi lần Excel lặp lại phần thân của vòng lặp. Trong trường hợp của macro Delete_Empty_Rows, giá trị -1 lấy vị trí của mục tùy chọn “bước” trong cú pháp chung của câu lệnh Tiếp theo….
Bạn có thể xem toàn bộ Đối với ... Tiếp theo tuyên bố của macro Delete_Empty_Rows trong hình dưới đây:
Dòng # 6: Nếu WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0 Sau đó
Đây là hàng đầu tiên của câu lệnh If… Then… Khác. Các câu lệnh này là một trong những cách phổ biến nhất để triển khai một điều kiện trong Visual Basic cho các ứng dụng.
Theo giải thích của Microsoft ở đây, cú pháp của dòng đầu tiên của câu lệnh If… Then… Khác là “If condition [Then]”. Trong dòng mã VBA xuất hiện ở trên, điều kiện được kiểm tra là liệu WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0. Nếu điều kiện này được đánh giá là đúng, Excel thực hiện câu lệnh xuất hiện ngay bên dưới (tôi giải thích điều này trong bước tiếp theo).
Tuy nhiên, câu hỏi là:
Điều kiện nào WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0 có nghĩa là gì?
Câu hỏi được hỏi bởi phép thử logic này là liệu tất cả các ô trong một hàng cụ thể đều trống không. Chúng ta hãy xem xét từng yếu tố của nó một cách riêng biệt để hiểu cách câu hỏi được cấu trúc theo các thuật ngữ VBA:
Đối tượng WorksheetFunction là một vùng chứa cho các hàm trang tính thông thường. Nói cách khác, bạn sử dụng đối tượng WorksheetFunction để gọi các hàm trang tính.
Phương thức WorksheetFunction.CountA hoạt động tương tự như hàm COUNTA Excel. Nói cách khác, phương pháp này tính số lượng ô trong một phạm vi cụ thể không trống.
Cú pháp của phương thức cụ thể này là "expression.CountA (arguments)", trong đó "expression" là một biến cụ thể đại diện cho một WorksheetFunction và "arguments" là các đối số đại diện cho các giá trị cần đếm. Trong macro Delete_Empty_Rows, các đối số được tính là Selection.Rows (iCounter).
Trong Macro # 4, Selection.Rows (iCounter) đại diện cho một hàng trong phạm vi dữ liệu bạn đang áp dụng macro. Hàng chính xác được đại diện bởi đối số này phụ thuộc vào giá trị hiện tại của biến iCounter. Như bạn đã biết, giá trị của iCounter thay đổi theo các điều kiện có trong phần đầu của câu lệnh Tiếp theo….
Ví dụ, lần đầu tiên Excel chạy phần thân của vòng lặp, hàng được sử dụng để xác định xem điều kiện đánh giá là đúng hay sai là hàng cuối cùng trong phạm vi mà macro đang làm việc với (Row (Selection.Rows.Count) ). Lần cuối Excel lặp lại vòng lặp, Selection.Rows (iCounter) tham chiếu đến hàng đầu tiên trong phạm vi (Hàng (1)).
Phần cuối của câu lệnh (“= 0”) đặt điều kiện là biểu thức bên trái dấu bằng (WorksheetFunction.CountA (Selection.Rows (iCounter))) bằng 0.
Phương thức WorksheetFunction.CountA chỉ bằng 0 nếu tất cả các ô trong hàng đang được kiểm tra (Selection.Rows (iCounter)) rỗng. Nếu có ít nhất một ô không trống, phương thức trả về một giá trị khác và điều kiện đánh giá là sai.
Dòng # 7: Selection.Rows (iCounter) .EntireRow.Delete
Đây là đoạn mã VBA duy nhất trong câu lệnh If… Then… Khác và, do đó, là câu lệnh duy nhất được thực thi nếu điều kiện trên đánh giá là đúng (tất cả các ô trong một hàng cụ thể đều trống).
Bạn có biết những gì tuyên bố này không?
Bạn đã biết tất cả các phần tử trong hàng này và, do đó, có thể đã trả lời rằng đoạn mã VBA này xóa toàn bộ hàng. Trong mọi trường hợp, chúng ta hãy đi nhanh qua từng mục:
Selection.Rows (iCounter) đại diện cho một hàng trong phạm vi mà Delete_Empty_Rows đang làm việc.
.EntireRow chọn toàn bộ hàng.
.Xóa xóa đối tượng đã chọn, trong trường hợp này, là hàng đã chọn.
Dòng số 8: Kết thúc nếu
Dòng này đơn giản chấm dứt câu lệnh If… Then… Khác.
Tóm lại, nếu… thì… khối khác:
Hỏi xem một hàng trong phạm vi đã chọn có trống không.
Nếu hàng trống, nó sẽ chọn toàn bộ hàng và xóa nó.
Dòng số 9: Tiếp theo iCounter
Đây là dòng cuối cùng của câu lệnh Tiếp theo ....
Khi Excel đã thực hiện các hoạt động được chỉ định trong vòng lặp, nó sẽ chuyển sang phần tử tiếp theo, trong trường hợp này, là hàng tiếp theo của phạm vi đã chọn.
Dòng # 10: .Calculation = xlCalculationAutomatic
Bạn đã biết rằng câu lệnh .Calculation = xlCalculationManual xuất hiện sớm trong câu lệnh With… End With của macro này sẽ tắt tính toán bảng tính tự động.
Bạn có thể hình dung câu lệnh .Calculation = xlCalculationAutomatic không?
Vâng. Như bạn đã có thể trả lời, nó chỉ đơn giản là quay trở lại tính toán bảng tính hoặc, chính xác hơn, cho phép Excel kiểm soát tính toán lại.
Dòng # 11: .ScreenUpdating = True
Tôi chắc chắn rằng bạn biết những gì tuyên bố này không, nhưng tôi sẽ hỏi câu hỏi anyway:
Bạn có biết những gì .ScreenUpdating = Đúng không?
Vì bạn biết rằng .ScreenUpdating = False tắt cập nhật màn hình, bạn có thể tưởng tượng rằng .ScreenUpdating = True biến màn hình cập nhật trở lại. Làm điều này (bật màn hình cập nhật trở lại) là khá quan trọng như vậy, như Microsoft nói, đừng quên đặt thuộc tính ScreenUpdating trở lại True sau khi macro bạn đang viết kết thúc.
Dòng # 12: Kết thúc bằng
Hàng này chấm dứt câu lệnh With… End With.
Tóm tắt quá trình theo sau bởi Delete_Empty_Rows Macro
Tóm lại, macro Delete_Empty_Rows tiến hành như sau:
Bước # 1: Macro chuyển đến hàng cuối cùng của phạm vi bạn đã chọn.
Bước # 2: Macro kiểm tra xem hàng cụ thể đó có hoàn toàn trống không.
Bước # 3: Nếu hàng hoàn toàn trống, macro sẽ xóa hàng. Nếu hàng không hoàn toàn trống, nó không làm gì cả.
Bước 4: Macro di chuyển đến hàng ngay trên hàng nó vừa phân tích và lặp lại quá trình tương tự, bắt đầu với bước # 2 ở trên.
Bước # 5: Macro tiếp tục di chuyển lên qua phạm vi được chọn cho đến khi nó đạt đến hàng đầu tiên (tại điểm kết thúc).
Ngoài các bước trên, Delete_Empty_Rows chuyển các tính toán sổ làm việc tự động và cập nhật màn hình cho các mục đích tăng tốc macro. Sau khi hoàn tất quá trình xóa các hàng trống, tính toán sổ làm việc tự động và cập nhật màn hình sẽ được bật trở lại.
Đồ họa, quá trình này trông gần như sau:
Hình ảnh dưới đây cho thấy cách macro Delete_Empty_Rows hoạt động trong thực tế:
Macro # 5: Xóa hàng khi hàng toàn bộ trống (tùy chọn 2)
Một thay thế cho macro Delete_Empty_Rows cũng xóa toàn bộ hàng khi nó hoàn toàn trống rỗng được đề xuất bởi anhn tại VBForums. Phần thân của mã VBA như sau:
Dim aRow As RangeDim BlankRows As RangeFor Each aRow In Selection.Rows.EntireRowIf WorksheetFunction.CountA(aRow) = 0 ThenIf Not BlankRows Is Nothing ThenSet BlankRows = Union(BlankRows, aRow)ElseSet BlankRows = aRowEnd IfEnd IfNextIf Not BlankRows Is Nothing ThenApplication.ScreenUpdating = FalseBlankRows.DeleteApplication.ScreenUpdating = TrueEnd If
Tôi đặt tên cho macro này là Delete_Empty_Rows_2, gán phím tắt “Ctrl + Shift + P” và thêm mô tả. Toàn bộ mã macro trông như sau trong trình soạn thảo Visual Basic:
Như thường lệ, chúng ta hãy xem từng dòng mã để hiểu cách macro tiến hành. Bây giờ, tôi phải cảnh báo bạn rằng cấu trúc của macro này hơi phức tạp hơn so với các macro khác mà chúng tôi đã khám phá trong hướng dẫn này, vì vậy, nếu bạn quyết định tiếp tục, hãy chịu theo tôi. Đến cuối, bạn sẽ có thể hiểu được quá trình tiếp theo là macro này
Dòng # 1 và dòng # 2: Dim aRow As Range | Dim BlankRows As Range
Bạn đã biết điều đó:
Câu lệnh Dim được sử dụng cho mục đích khai báo một biến.
Từ khóa As được sử dụng để xác định kiểu dữ liệu của một biến trong câu lệnh khai báo.
Trong macro Delete_Empty_Rows_2, các biến được khai báo là “aRow” và “BlankRows”. Cả hai đều được khai báo là đối tượng Phạm vi.
Trong Macro # 5, các biến này được sử dụng cho các mục đích biểu diễn các hàng. Cụ thể hơn, BlankRows được sử dụng để lưu trữ và đại diện cho tất cả các hàng trống trong phạm vi ô bạn đã chọn.
Dòng # 3: Dành cho mỗi aRow Trong lựa chọn.Rows.EntireRow
Hàng này báo hiệu sự khởi đầu của câu lệnh For Each… Next, mục đích của nó là thực hiện lặp lại một nhóm các câu lệnh cho mỗi thành phần của một bộ sưu tập hoặc mảng đối tượng.
Hình ảnh này hiển thị toàn bộ Đối với mỗi câu lệnh tiếp theo trong macro Delete_Empty_Rows_2:
Cú pháp cơ bản của dòng đầu tiên của câu lệnh For Each… Next là “Đối với mỗi phần tử [như datatype] trong nhóm”, trong đó:
"Phần tử" là một biến được yêu cầu được sử dụng cho mục đích lặp qua các phần tử khác nhau của bộ sưu tập hoặc mảng. Tôi đề cập đến chủ đề của mảng chi tiết hơn trong bài đăng trên blog này.
Trong macro Delete_Empty_Rows_2, "phần tử" là biến aRow đã được khai báo ở đầu macro là một dải ô. Trong trường hợp này, vì phần tử đã được khai báo, nên không cần phải nêu rõ kiểu dữ liệu của nó trong câu lệnh For Each… Next. Khi phần tử chưa được khai báo trước đó, nó cần thiết để bao gồm kiểu dữ liệu của phần tử trong câu lệnh For Each… Next.
"Nhóm" là một đối tượng được yêu cầu đề cập đến bộ sưu tập hoặc mảng mà trên đó các câu lệnh trong câu lệnh For Each… Next được lặp lại.
Đối với Delete_Empty_Rows_2, nhóm là Selection.Rows.EntireRow. Có lẽ bạn nhớ rằng Selection.Rows là một sự thực thi có thể có của thuộc tính Range.Rows. Bạn cũng có thể nhớ rằng, khi phạm vi là Lựa chọn, mục này trả về các hàng trong phạm vi dữ liệu hiện đang được chọn. Cuối cùng, thuộc tính EntireRow làm cho tham chiếu đến một hoặc nhiều hàng.
Do đó, dòng này nói rằng các câu lệnh trong câu lệnh For Each… Next sẽ được thực hiện và lặp lại cho mỗi hàng trong phạm vi dữ liệu (hoặc bảng) mà bạn đã chọn trước khi thực thi macro Delete_Empty_Rows_2.
Lưu ý rằng, trên thực tế, hiệu ứng của dòng này rất giống với câu lệnh mở của câu lệnh Tiếp theo ... được sử dụng trong macro Delete_Empty_Rows (Đối với iCounter = Selection.Rows.Count To 1 Step –1). Lý do chính cấu trúc cú pháp của chúng khác nhau là bởi vì một là câu lệnh For… Next (lặp lại một nhóm các câu lệnh một số lần nhất định) và cái kia là câu lệnh For Each… Next (lặp lại một nhóm các câu lệnh cho mỗi phần tử). của một bộ sưu tập đối tượng).
Dòng # 4: Nếu WorksheetFunction.CountA (aRow) = 0 Sau đó
Bạn có thể nhận ra rằng đây là hàng đầu tiên của câu lệnh If… Then… Khác và, do đó, biết rằng mục đích của câu lệnh này là thiết lập một điều kiện trong VBA.
Hình ảnh sau đây cho thấy toàn bộ Nếu… Sau đó… Tuyên bố khác:
Thông thường, các loại câu lệnh này sử dụng cú pháp “Nếu điều kiện [Sau đó]”. Do đó, bạn có thể xác định rằng điều kiện được kiểm tra ở trên là liệu “WorksheetFunction.CountA (aRow) = 0”.
Điều kiện này có nghĩa là gì? Nó hỏi cái gì?
Bạn có thể nhớ rằng macro Delete_Empty_Rows cũng bao gồm một câu lệnh If… Then… Khác. Trong cơ hội đó, câu hỏi tương tự. Chính xác hơn, điều kiện kiểm tra là liệu “WorksheetFunction.CountA (Selection.Rows (iCounter)) = 0”. Điều này đã đặt câu hỏi liệu tất cả các ô trong một hàng cụ thể có trống không.
Dựa trên điều này, bạn có thể tưởng tượng điều kiện nào đang được thử nghiệm bởi "WorksheetFunction.CountA (aRow) = 0"?
Bạn đã biết rằng đối tượng Function Worksheet là một container cho các hàm bảng tính và phương thức WorksheetFunction.CountA đếm số ô trong một phạm vi cụ thể không rỗng. Do đó, bạn có thể đã nhận thấy rằng câu hỏi được hỏi bởi macro Delete_Empty_Rows_2 là liệu tất cả các ô trong một hàng cụ thể có trống không.
Dòng # 5: Nếu không BlankRows không có gì thì
Bạn có thể nhận ra rằng đây là lần thứ hai Nếu… Vậy… Tuyên bố khác. Hình ảnh dưới đây cho thấy nó:
Cấu trúc của điều kiện trong câu lệnh cụ thể này, tuy nhiên, khác với câu lệnh của cái kia Nếu… Sau đó… Các câu lệnh khác mà chúng ta đã thấy trong suốt hướng dẫn này. Trong trường hợp này, câu hỏi được đặt ra là liệu "Không BlankRows là không có gì".
Chúng ta hãy xem từng mục riêng lẻ để hiểu ý nghĩa của dòng này:
Như đã giải thích trong tính toán tài chính chuyên nghiệp Sử dụng Excel và VBA, Toán tử không trả về “kết quả lôgic ngược lại của biểu thức được đánh giá”.
BlankRows là một trong các biến được khai báo ở đầu macro.
Toán tử Is so sánh 2 biến tham chiếu đối tượng. Nói cách khác, "Is" xác định liệu 2 tham chiếu đối tượng có tham chiếu đến cùng một đối tượng mà không thực hiện so sánh giá trị hay không. Điều quan trọng là liệu cả hai đều đề cập đến cùng một đối tượng.
Không có gì là giá trị mặc định cho một loại dữ liệu cụ thể. Ví dụ, trong trường hợp kiểu dữ liệu Object, giá trị mặc định là Nothing (một tham chiếu null).
Vậy tuyên bố này đang làm gì?
Toán tử Is được sử dụng để xác định xem đối tượng tham chiếu "Không BlankRows" có tham chiếu đến cùng một đối tượng là Không có gì không. “Không phải BlankRows”, vì bạn có thể kết luận ngay bây giờ, đơn giản là ngược lại với biến BlankRows.
Do đó, câu hỏi được hỏi bởi phép kiểm tra logic này là liệu đối lập của BlankRows có tham chiếu đến cùng một đối tượng như Không. Nói cách khác, nó hỏi: BlankRows có tham chiếu đến cái gì đó không?
Câu hỏi bạn có thể có là:
Khi nào ngược lại với biến BlankRows (Not BlankRows) làm cho tham chiếu đến cùng một đối tượng như Không có gì?
Câu hỏi là phức tạp hơn có vẻ như vì hàng chúng ta đang xem ngay bây giờ nằm trong vòng lặp (được thực hiện với câu lệnh For Each… Next), như hình dưới đây:
Để làm phức tạp hơn nữa, câu lệnh If… Then… Khác mà chúng ta đang xem được sử dụng để gán các giá trị cho biến BlankRows. Tôi giải thích điều này xảy ra như thế nào bên dưới.
Kết quả của việc trên, giá trị của BlankRows thay đổi mỗi lần macro tìm thấy một hàng trống mới. Chúng ta hãy xem xét chi tiết hơn về cách thay đổi này xảy ra:
Lần đầu tiên (s) vòng lặp chạy, BlankRows không được gán cho bất cứ điều gì. Vì vậy, nó không có gì và làm cho tham chiếu đến cùng một đối tượng như Không có gì. Điều này có nghĩa là "Không BlankRows" không làm cho tham chiếu đến cùng một đối tượng như Không có gì và điều kiện "Không BlankRows là không có gì" đánh giá sai.
Hậu quả của việc này là, nếu có bất kỳ hàng trống nào trong phạm vi bạn đã chọn trước khi thực thi macro, giá trị aRow được lưu trữ trong biến BlankRows khi macro tìm thấy hàng trống đầu tiên. Tôi giải thích làm thế nào điều này được thực hiện thêm dưới đây.
Khi hàng trống đầu tiên được tìm thấy, BlankRows lưu trữ giá trị aRow. Vì vậy, bây giờ nó là cái gì đó và làm cho tham chiếu đến đối diện của Không có gì. Điều này có nghĩa là "Không BlankRows" làm cho tham chiếu đến cùng một đối tượng như Không có gì và điều kiện "Không BlankRows là không có gì" đánh giá là đúng.
Hậu quả của việc này là giá trị Union (BlankRows, aRow), có nghĩa là tôi giải thích dưới đây, được lưu trữ trong BlankRows.
Tất cả các lần sau vòng lặp chạy, BlankRows lưu trữ giá trị của Liên minh trước đó (BlankRows, aRow). Do đó, điều kiện tiếp tục đánh giá đúng và giá trị được lưu trữ trong BlankRows được cập nhật tương ứng.
Nếu điều này không có ý nghĩa với bạn, hãy giữ cho đến khi kết thúc vòng lặp. Bởi sau đó bạn sẽ hiểu rõ hơn những gì tập hợp các báo cáo này.
Dòng # 6: Đặt BlankRows = Union (BlankRows, aRow)
Hàng này nằm trong câu lệnh If… Then… Khác. Đây là câu lệnh mà Excel thực hiện nếu kiểm tra lôgic xuất hiện trong hàng trước (Nếu không có BlankRows là Không có gì) sẽ đánh giá đúng.
Chúng ta hãy xem từng mục riêng lẻ:
Câu lệnh Set gán một tham chiếu đối tượng.
BlankRows là một trong các biến được khai báo ở đầu Macro # 5.
Liên minh trả về công đoàn của một số phạm vi.
Như đã giải thích tại Stat Trek, sự kết hợp của 2 bộ là “tập các phần tử thuộc về một hoặc cả hai bộ”. Câu lệnh mà chúng ta đang xem xét tham chiếu đến liên kết giữa BlankRows và aRow, có nghĩa là union bao gồm tất cả các phần tử thuộc về (i) cả BlankRows và aRow, (ii) chỉ BlankRows và (iii) aRow. Đồ họa:
Do đó, câu lệnh này lấy biến BlankRows và gán cho nó sự kết hợp của các biến BlankRows và aRow.
Câu hỏi bạn có thể có là, điều này có ý nghĩa gì trong các thuật ngữ thực tế cho macro Delete_Empty_Rows_2?
Để trả lời câu hỏi này, bạn cần nhớ 2 điều:
Câu lệnh If… Then… Khác mà chúng ta đang xem xét nằm trong câu lệnh For Each… Next (Đối với mỗi aRow In Selection.Rows.EntireRow) đi qua từng hàng riêng biệt của bảng hoặc dải mà bạn đang làm việc. Đối với mỗi hàng trong số các hàng này, macro hỏi xem toàn bộ hàng có trống không (Nếu WorksheetFunction.CountA (aRow) = 0).
Lần đầu tiên vòng lặp chạy, BlankRows là không có gì và phép thử logic “If Not BlankRows Is Nothing” đánh giá sai. Điều này tiếp tục là trường hợp cho đến khi hàng trống đầu tiên được tìm thấy và được gán cho biến BlankRows. Sau đó, điều kiện đánh giá là đúng và phép gán được thực hiện bởi hàng này (Set BlankRows = Union (BlankRows, aRow)) chỉ xảy ra sau đó.
Ngoài các điều trên, khi điều kiện đánh giá sai (chỉ xảy ra cho đến khi vòng lặp tìm thấy hàng trống), câu lệnh được thực thi (Set BlankRows = aRow) đặt BlankRows thành aRow. Nói cách khác, ngay sau lần đầu tiên nhóm các lệnh trong vòng lặp tìm thấy một hàng trống, BlankRows bằng aRow. Điều này có vẻ như sau trong Excel:
Thời gian sau khi vòng lặp chạy, BlankRows bằng với liên kết của BlankRows và aRow (tại thời điểm đó) là 2 hàng trống. Thêm vào đó, macro chọn hàng trống thứ ba (aRow). BlankRows sau đó được thiết lập để liên minh mới này của BlankRows và aRow.
Bởi bây giờ bạn có thể có được ý tưởng. Bất kỳ hàng trống nào Excel tìm thấy đều được thêm vào biến BlankRows. Khi vòng lặp đã chạy nhiều lần theo yêu cầu, BlankRows đại diện cho tất cả các hàng trống trong bảng hoặc dải ô.
Dòng # 7: Khác
Hàng này là một phần của câu lệnh If… Then… Khác. Nó báo hiệu rằng các hàng theo sau nó (các câu lệnh khác) chỉ được thực thi nếu không có điều kiện nào trước đây được đánh giá là đúng.
Trong macro Delete_Blank_Rows_2, các câu lệnh khác được thực hiện nếu điều kiện "Nếu không có BlankRows là không có gì" được đánh giá là false. Điều này xảy ra cho đến khi các lệnh trong vòng lặp được báo hiệu bởi câu lệnh “For Each aRow In Selection.Rows.EntireRow” tìm hàng trống đầu tiên.
Nói cách khác, các tín hiệu “Khác” mà các câu lệnh theo sau là các lệnh được thực hiện trong lần đầu tiên vòng lặp trong Macro # 5 chạy. Chính xác hơn, những câu lệnh này được thực hiện cho đến khi macro tìm thấy hàng trống đầu tiên.
Dòng # 8: Đặt BlankRows = aRow
Bạn có thể đã biết những gì tuyên bố này không:
Nó đặt biến BlankRows thành aRow.
Vui lòng tham khảo phần giải thích của câu lệnh “Đặt BlankRows = Union (BlankRows, aRow)” ở trên để có giải thích kỹ lưỡng về cách Macro # 5 quản lý biến BlankRows.
Dòng # 9 và dòng # 10: Kết thúc nếu | Kết thúc nếu
Như bạn đã biết, các câu lệnh này chỉ đơn giản là chấm dứt các câu lệnh If… Then… Khác. Lý do có 2 câu lệnh If End, đơn giản là, 2 riêng biệt Nếu… Sau đó… Các câu lệnh khác đang bị chấm dứt.
Dòng số 11: Tiếp theo
Bạn đã biết rằng hàng này chấm dứt vòng lặp For Each… Next.
Dòng # 12: Nếu không BlankRows là gì thì
Chúng tôi đã thấy tuyên bố này chính xác hơn nữa ở trên. Bạn biết rằng nó báo hiệu sự khởi đầu của một câu lệnh If… Then… Khác.
Hơn nữa, bạn biết rằng câu hỏi được hỏi bởi bài kiểm tra logic là liệu BlankRows có ám chỉ đến điều gì đó không.
Dòng # 13: Application.ScreenUpdating = False
Chúng tôi đã nhìn thấy một tuyên bố tương tự về cơ bản cho điều này trong macro Delete_Empty_Rows.
Do đó, bạn biết rằng thuộc tính Application.ScreenUpdating chuyển lượt cập nhật màn hình cho các mục đích tăng tốc macro.
Dòng # 14: BlankRows.Delete
Các macro có trong hướng dẫn này đã áp dụng phương thức Delete trong một vài trường hợp. Ví dụ, macro Delete_Blank_Rows_3 kết hợp phương thức Delete với một thể hiện của thuộc tính Range.EntireRow để xóa toàn bộ các hàng dựa trên việc một ô trong hàng có trống hay không. Trong trường hợp đó, toàn bộ câu lệnh là “Selection.EntireRow.SpecialCells (xlBlanks) .EntireRow.Delete”.
Xem xét ở trên, bạn có thể đoán những gì tuyên bố này không?
Bạn có thể trả lời (chính xác) rằng câu lệnh này lấy tất cả các hàng trống được biểu diễn bằng biến BlankRows và xóa chúng.
Vì hàng cụ thể này nằm trong một câu lệnh If… Then… Khác, nó chỉ được áp dụng nếu biến BlankRows tham chiếu đến một thứ gì đó. Điều này luôn luôn xảy ra trừ khi bạn đang làm việc với một bảng hoặc dải ô không có hàng trống.
Dòng # 15: Application.ScreenUpdating = True
Bạn đã biết rằng điều quan trọng là biến thuộc tính ScreenUpdating trở lại khi macro kết thúc và đó chính là điều mà dòng này thực hiện.
Dòng # 16: Kết thúc nếu
Câu lệnh này kết thúc câu lệnh thứ ba và cuối cùng, nếu… Sau đó… Câu lệnh khác của macro Delete_Empty_Rows_2.
Tóm tắt quá trình theo sau bởi macro Delete_Empty_Rows_2
Như thường lệ, chúng ta hãy kết thúc bằng cách tóm tắt quá trình tiếp theo là macro Delete_Empty_Rows_2.
Bước # 1: Macro đi qua mỗi hàng trong phạm vi mà bạn đã chọn và hỏi xem nó có hoàn toàn trống không.
Bước # 2: Khi macro đã tìm thấy hàng trống đầu tiên trong phạm vi, nó gán nó cho biến BlankRows.
Bước # 3: Khi macro tìm thấy hàng trống thứ hai trong phạm vi được chọn, macro sẽ thêm nó vào biến BlankRows. Do đó, BlankRows giờ đây đại diện cho 2 hàng trống đầu tiên trong phạm vi ô tương ứng.
Bước 4: Macro lặp lại các bước # 1 và # 3 cho đến khi nó tìm thấy tất cả các hàng trống trong phạm vi bạn đang làm việc. Mỗi hàng được thêm vào biến BlankRows như vậy, cuối cùng, BlankRows đại diện cho tất cả các hàng trống.
Bước # 5: Tất cả các hàng trống, được biểu thị bằng biến BlankRows, sẽ bị xóa.
Ngoài các mục trên và cho mục đích tăng tốc macro, Delete_Empty_Rows_2 tắt cập nhật màn hình trước bước # 5. Cập nhật màn hình được bật lại sau khi các hàng trống đã bị xóa.
Đồ họa, toàn bộ quá trình này trông như sau:
Hình ảnh sau đây cho bạn thấy macro Delete_Empty_Rows_2 tại nơi làm việc:
Kết luận và các sửa đổi có thể có khác cho Macros trong hướng dẫn này
5 macro xuất hiện trong hướng dẫn này, không có nghĩa là, một danh sách đầy đủ tất cả các cách có thể mà bạn có thể mã macro để xóa các hàng trống hoặc các hàng có các ô trống trong Excel.
Khi bạn trở nên thành thạo hơn trong Visual Basic cho các ứng dụng và cải thiện kiến thức của bạn về macro, bạn sẽ tìm cách để tinh chỉnh và cải thiện các macro mà tôi đã giải thích ở trên. Có lẽ quan trọng hơn, bạn sẽ bắt đầu hiểu tại sao một cấu trúc cụ thể có thể phù hợp hơn một cấu trúc khác xem xét bối cảnh bạn đang làm việc và các mục tiêu bạn muốn đạt được.
Sau đây là danh sách rất ngắn gọn các cách có thể để sửa đổi các macro xuất hiện trong hướng dẫn này. Mục đích chỉ nhằm cung cấp cho bạn ý tưởng về các câu hỏi mà bạn có thể bắt đầu xem xét trong tương lai khi tạo macro để xóa các hàng hoặc hàng trống với các ô trống trong Excel.
Macro Delete_Blank_Rows_2 sử dụng câu lệnh With… End With.
Cấu trúc này có cần thiết không? Làm thế nào về việc sử dụng một dòng mã như trong macro Delete_Blank_Rows_3 hoặc macro được đề xuất bởi VoG tại diễn đàn của Mr.Excel?
Có bất kỳ macro nào xuất hiện trong lợi ích của hướng dẫn này không bao gồm các câu lệnh On Error cho các mục đích xử lý các lỗi thời gian chạy không?
Sẽ hữu ích khi tạo các hộp thoại bổ sung cho bất kỳ macro nào không?
Làm thế nào về một hộp thoại xác nhận rằng các hàng trống đã được gỡ bỏ, chẳng hạn như một xuất hiện trong vĩ mô ở cuối bài đăng này (đáng kể giống như macro Delete_Empty_Rows_2) được đề xuất bởi công nghệ tự xưng Geek Ankit Kaul? Làm thế nào về việc thêm hộp thoại nơi người dùng của sổ làm việc Excel của bạn có thể nhập, ví dụ, phạm vi ô mà họ muốn một macro cụ thể để tìm kiếm ô trống trong?
Tôi đã giải thích tại sao một số chuyên gia Excel khuyên bạn tránh sử dụng phương thức Select và thuộc tính Selection. Một số macro xuất hiện trong hướng dẫn này sử dụng các cấu trúc khác để đạt được mục tiêu xóa các hàng trống hoặc các hàng có các ô trống mà không sử dụng Select hoặc Selection.
Bạn có thể nghĩ ra các lựa chọn thay thế khác mà bạn có thể sử dụng để tránh sử dụng phương pháp Chọn hoặc thuộc tính Lựa chọn không?
Làm thế nào về việc sử dụng các biến và gán phạm vi trong mã VBA, như đề xuất tại TheSpreadsheetGuru hoặc bởi nhà phân tích kinh doanh PNRao ở đây?
Có điều gì bạn có thể làm để xử lý các ô có khoảng trống được tạo bởi dấu ngoặc kép (“”) không?
Tôi sẽ đề cập sâu hơn về chủ đề này trong các hướng dẫn trong tương lai nhưng, trong khi đó, cách sử dụng “.Value = .Value” theo gợi ý của erik.van.geit tại Mr.Excel.com và được triển khai trong macro Delete_Blank_Rows_2?
Cấu trúc macro Delete_Empty_Rows_2 một bài kiểm tra có điều kiện là "Nếu không BlankRows là không có gì".
Bạn có thể hỏi một câu hỏi tương tự đáng kể bằng cách sử dụng một cấu trúc khác không? Làm thế nào về việc sử dụng “Nếu BlankRows là không có gì” theo đề xuất của chuyên gia Excel John Walkenbach tại Office Archive?
0 comments so far,add yours