Hướng dẫn excel macro filter and delete visible rows - bộ lọc macro excel và xóa các hàng hiển thị

Điểm mấu chốt: Tìm hiểu cách sử dụng các macro VBA để xóa các hàng dựa trên các giá trị hoặc điều kiện ô. Bao gồm hướng dẫn video và mã mẫu. Learn how to use VBA macros to delete rows based on cell values or conditions. Includes video tutorial and sample code.

Cấp độ kỹ năng: Trung cấp Intermediate

Video hướng dẫn

Tải xuống tệp Excel

Dưới đây là tệp tôi sử dụng trong video trên có chứa các ví dụ mã macro VBA.

Quá trình chuẩn bị dữ liệu của bạn có bao gồm xóa cùng một hàng dựa trên một điều kiện không? Nếu vậy, bạn có thể sử dụng một macro để xóa ngay bất kỳ hàng nào có giá trị cụ thể, ngày hoặc thậm chí các ô trống.

Quá trình tổng thể là hai bước đơn giản:two simple steps:

1. Bước đầu tiên là lọc các hàng dựa trên tiêu chí lọc cho các giá trị bị xóa.filter the rows based on filter criteria for the values to be deleted.
2. Sau đó, macro xóa các ô có thể nhìn thấy trong phạm vi.deletes the visible cells in the range.

Quá trình giải thích

Dưới đây là hình ảnh của bộ dữ liệu có một số ô trống trong cột E (sản phẩm). Bạn có thể thấy một trong những ô trống đó là E6.

Để loại bỏ các hàng có các ô trống như thế này, Macro trước tiên áp dụng bộ lọc cho cột sản phẩm.

Tiếp theo, macro chỉ cần xóa tất cả các hàng có thể nhìn thấy được để lại bởi bộ lọc. Nó sử dụng phương pháp SpecialCells để tạo tham chiếu đến các ô có thể nhìn thấy.

Điều này giống như sử dụng menu chuyển đến đặc biệt (phím tắt ALT+;) để chọn chỗ trống. Kiểm tra bài viết và video của tôi về cách sao chép và dán các ô hiển thị để tìm hiểu thêm.

Cuối cùng, macro cũng có thể xóa các bộ lọc để bạn xem lại toàn bộ phạm vi dữ liệu, trừ đi các hàng bạn đã xóa.

Đó là một macro 2 hoặc 3 bước đơn giản sẽ tiết kiệm thời gian từ việc thực hiện quá trình này theo cách thủ công.

Lưu ý quan trọng: Tôi đã thêm một bước để xóa tất cả các bộ lọc trong phạm vi hoặc bảng ở đầu macro. Điều này đảm bảo rằng không có bộ lọc nào được áp dụng cho các cột khác có thể khiến các hàng bổ sung được lọc ra. I added a step to clear all filters in the range or Table at the beginning of the macro. This ensures that there are no filters applied to other columns that could cause additional rows to be filtered out.

Một lời cảm ơn lớn đến Hoang đã chỉ ra điều này trên video YouTube!

Mã macro VBA

Mã VBA dưới đây có thể được sao chép/dán vào Trình chỉnh sửa VB. Mã cũng được bao gồm trong tệp Excel trong phần Tải xuống ở trên.

Sub Delete_Rows_Based_On_Value()
'Apply a filter to a Range and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/
Dim ws As Worksheet
  'Set reference to the sheet in the workbook.
  Set ws = ThisWorkbook.Worksheets("Regular Range")
  ws.Activate 'not required but allows user to view sheet if warning message appears
    'Clear any existing filters
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0
  '1. Apply Filter
  ws.Range("B3:G1000").AutoFilter Field:=4, Criteria1:=""
    '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
    '3. Clear Filter
  On Error Resume Next
    ws.ShowAllData
  On Error GoTo 0
End Sub

Bạn sẽ chỉ cần cập nhật bảng tính và tham chiếu phạm vi cho tệp cụ thể của bạn.

Tránh cảnh báo bật lên

Khi bạn chạy macro, bạn sẽ nhận được một thông báo cảnh báo bật lên cho biết rằng xóa toàn bộ hàng tờ?

Nếu bạn muốn chạy macro mà không bị gián đoạn hộp pop-up đó, bạn chỉ cần xóa các dấu nháy đơn trước hai dòng mã bắt đầu bằng application.displayalerts.

Vì vậy, phần đó của macro bây giờ trông như thế này:

  '2. Delete Rows
  Application.DisplayAlerts = False
    ws.Range("B4:G1000").SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True

Thuộc tính application.displayalerts là một chuyển đổi để bật/tắt cảnh báo cảnh báo. Đặt nó thành sai sẽ tắt cảnh báo. Đây là những cảnh báo mà bạn có thể thấy từ Excel khi xóa phạm vi, xóa tờ, đóng mà không cần lưu, v.v.

Áp dụng macro cho các bảng

Nếu dữ liệu của bạn nằm trong bảng Excel thay vì chỉ một loạt các ô, bạn vẫn có thể xóa các hàng dựa trên nội dung ô bằng macro. Mã gần như giống nhau, nhưng được điều chỉnh một chút để nó áp dụng cho các bảng. Đây là mã bạn sẽ sử dụng cho một bảng.Excel Table instead of just a range of cells, you can still delete rows based on cell contents using a macro. The code is almost the same, but tweaked slightly so that it applies to Tables. Here is the code you would use for a Table.

Sub Delete_Rows_Based_On_Value_Table()
'Apply a filter to a Table and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/
Dim lo As ListObject
  'Set reference to the sheet and Table.
  Set lo = Sheet3.ListObjects(1)
  ws.Activate
    'Clear any existing filters
  lo.AutoFilter.ShowAllData
    '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"
    '2. Delete Rows
  Application.DisplayAlerts = False
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  '3. Clear Filter
  lo.AutoFilter.ShowAllData
End Sub

Macro bổ sung

Tôi cũng đã bao gồm một vài macro bổ sung để giúp tùy chỉnh quy trình hơn nữa.

Thông báo cảnh báo tùy chỉnh

Bạn có thể tạo một hộp thông báo tùy chỉnh trước khi xóa hàng, thay vì cảnh báo Excel mặc định để xóa hàng. Macro bên dưới cũng cho bạn biết số lượng hàng sẽ xóa và hỏi bạn có muốn tiến hành không.custom message box before deleting rows, instead of the default Excel warning to delete rows. The macro below also tells you the number of rows it is going to delete, and asks if you want to proceed.

Sub Delete_Rows_Based_On_Value_Table_Message()
'Display Yes/No message prompt before deleting rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/
Dim lo As ListObject
Dim lRows As Long
Dim vbAnswer As VbMsgBoxResult
  'Set reference to the sheet and Table.
  Set lo = Sheet6.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
    'Clear any existing filters
  lo.AutoFilter.ShowAllData
    '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:="Product 2"
    'Count Rows & display message
  On Error Resume Next
    lRows = WorksheetFunction.Subtotal(103, lo.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
    vbAnswer = MsgBox(lRows & " Rows will be deleted.  Do you want to continue?", vbYesNo, "Delete Rows Macro")
    If vbAnswer = vbYes Then
        'Delete Rows
    Application.DisplayAlerts = False
      lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
      'Clear Filter
    lo.AutoFilter.ShowAllData



      End If
End Sub

Đây là những gì hộp pop-up tùy chỉnh trông như thế nào:

Pop-up này chỉ phục vụ như một séc trước khi xóa các hàng. Đó là một cách tuyệt vời để kiểm tra kỹ mọi thứ có vẻ tốt trước khi xóa. Nó cũng ngăn bạn hoặc người dùng của bạn vô tình chạy macro.

Kiểm tra video của tôi về cách thêm hộp thông báo có/không trước khi macro chạy để biết chi tiết về mã này. Đó là một phần của loạt video 4 phần của tôi trên sổ làm việc macro cá nhân.

Xóa các hàng dựa trên nhiều tiêu chí

Bạn cũng có thể xóa các hàng bằng một macro sử dụng nhiều tiêu chí. Trong video trên, tôi lọc cho các hàng có cả trường sản phẩm trống và ngày trước 1/1/2015.more than one criteria. In the video above, I filter for rows that have both a blank Product field and a date before 1/1/2015.

Sub Delete_Rows_Based_On_Multiple_Values()
'Apply a filter to a Table and delete visible rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/
Dim lo As ListObject
  'Set reference to the sheet and Table.
  Set lo = Sheet5.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
    'Clear any existing filters
  lo.AutoFilter.ShowAllData
    '1. Apply Filter - Blanks in Product for before 2015 only
  lo.Range.AutoFilter Field:=4, Criteria1:=""
  lo.Range.AutoFilter Field:=1, Criteria1:="<1/1/2015"
    '2. Delete Rows
  Application.DisplayAlerts = False
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
  Application.DisplayAlerts = True
  '3. Clear Filter
  lo.AutoFilter.ShowAllData
End Sub

Điều này cho phép bạn xóa các hàng dựa trên các giá trị trong nhiều cột. Việc lọc về cơ bản sử dụng và logic giữa các cột và tất cả các điều kiện phải được đáp ứng.based on values in multiple columns. The filtering essentially uses AND logic between the columns and all conditions must be met.

Nếu bạn muốn sử dụng hoặc logic nơi đáp ứng bất kỳ điều kiện nào, bạn có thể chạy nhiều macro hoặc tạo công thức trong cột trợ giúp với logic.

Tùy thuộc vào logic, có thể dễ nhất chỉ tạo hai macro riêng biệt. Bạn có thể đặt tất cả mã vào một macro hoặc tạo một macro khác để gọi cho mỗi macro. Đây là một ví dụ về macro đó có thể trông như thế nào.

Sub Delete_Rows()
  Call Delete_Blank_Rows
  Call Delete_Before_2015
End Sub

Phương pháp này cũng cho phép bạn chạy từng macro riêng lẻ.

Xóa hàng dựa trên các tiêu chí được chỉ định bởi người dùng

Daniel và Bob đã hỏi một câu hỏi tuyệt vời trong các ý kiến ​​dưới đây. Họ muốn người dùng của tệp có thể chỉ định các tiêu chí bộ lọc, thay vì có mã hóa cứng trong macro.

Chúng ta có thể sử dụng phương thức application.InputBox để yêu cầu người dùng nhập các tiêu chí.Application.InputBox method to ask the user to input the criteria.

Phương thức InputBox tạo hộp thông báo bật lên cho phép người dùng nhập hộp văn bản. Giá trị trong hộp văn bản được chuyển trở lại macro và được lưu trữ trong một biến.

Đây là mã VBA. Tôi cũng đã thêm nó vào tệp mẫu trong phần Tải xuống ở trên.

Sub Delete_Rows_User_Input()
'Display Yes/No message prompt before deleting rows
'Source: https://www.excelcampus.com/vba/delete-rows-cell-values/
Dim lo As ListObject
Dim lRows As Long
Dim vbAnswer As VbMsgBoxResult
Dim sCriteria As Variant
  'Set reference to the sheet and Table.
  Set lo = Sheet9.ListObjects(1)
  lo.Parent.Activate 'Activate sheet that Table is on.
    'Clear any existing filters
  lo.AutoFilter.ShowAllData
    'Ask user for input
  sCriteria = Application.InputBox(Prompt:="Please enter the filter criteria for the Product column." _
                                    & vbNewLine & "Leave the box empty to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Type:=2)
    'Exit if user presses Cancel button
  If sCriteria = False Then Exit Sub
    '1. Apply Filter
  lo.Range.AutoFilter Field:=4, Criteria1:=sCriteria
    'Count Rows & display message
  On Error Resume Next
    lRows = WorksheetFunction.Subtotal(103, lo.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible))
  On Error GoTo 0
    vbAnswer = MsgBox(lRows & " Rows will be deleted.  Do you want to continue?", vbYesNo, "Delete Rows Macro")
    If vbAnswer = vbYes Then
        'Delete Rows
    Application.DisplayAlerts = False
      lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
      'Clear Filter
    lo.AutoFilter.ShowAllData
      End If
End Sub

Đổ đầy trước tên người dùng trong hộp đầu vào

Daniel cũng có một câu hỏi về việc đặt tiêu chí lọc theo tên của người dùng hiện tại. Thuộc tính application.username thực sự trả về giá trị này miễn là người dùng đã đặt nó đúng trong văn phòng/excel.

Chúng ta có thể sử dụng điều này làm giá trị tham số mặc định trong hộp đầu vào.

  sCriteria = Application.InputBox(Prompt:="Please enter the filter criteria for the Product column." _
                                    & vbNewLine & "Leave the box empty to filter for blanks.", _
                                    Title:="Filter Criteria", _
                                    Default:=Application.UserName, _
                                    Type:=2)

Người dùng vẫn có thể cần thay đổi tên nếu nó không khớp với giá trị trong cột bảng tính bạn đang lọc, nhưng bạn cũng có thể nhận được điều đó với người dùng để lưu chúng rất nhiều thời gian.

Chủ đề liên quan

Kiểm tra các bài viết của tôi về các chủ đề tương tự là tốt.

• Hướng dẫn cuối cùng về các bộ lọc với macro VBA
• Cách sao chép và dán các ô có thể nhìn thấy
• 3 cách để xóa toàn bộ hàng trống
• Cách tạo sổ làm việc macro cá nhân của bạn

Sự kết luận

Các macro như thế này có thể đặc biệt hữu ích nếu bạn đang tìm cách dọn dẹp dữ liệu và loại bỏ các mục mà bạn biết rằng bạn không cần có lẽ vì chúng quá già hoặc chúng chỉ hoàn thành một phần.

Có rất nhiều cách để hoàn thành nhiệm vụ này. Một cách tiếp cận phổ biến khác là lặp qua các ô và kiểm tra các giá trị của chúng riêng lẻ.

Tuy nhiên, tôi thường sử dụng phương pháp này vì nó có thể nhanh hơn rất nhiều để sử dụng các bộ lọc tích hợp trong Excel. Nó cũng cho phép chúng tôi xem trước phạm vi trước khi chúng tôi xóa nó.

Bạn sẽ sử dụng những macro này để làm gì? Vui lòng để lại một bình luận dưới đây với bất kỳ đề xuất và câu hỏi. Cảm ơn bạn! 🙂

Làm thế nào để bạn lọc và xóa các hàng trong macro excel?

Làm cách nào để xóa các hàng được lọc có thể nhìn thấy trong Excel?

Làm cách nào để xóa tất cả các hàng có thể nhìn thấy trong Excel VBA?

Làm cách nào để loại bỏ các hàng không mong muốn trong excel sau bộ lọc?