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: //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: //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: //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: //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:="