Cách xóa hàng hiển thị trong excel vba

dòng dưới cùng. Tìm hiểu cách sử dụng macro VBA để xóa các hàng dựa trên các giá trị hoặc điều kiện của ô. Bao gồm video hướng dẫn và mã mẫu

Cấp độ kỹ năng. Trung gian

Video hướng dẫn

Tải xuống tệp Excel

Đây là tệp tôi sử dụng trong video ở trên có chứa các ví dụ về mã macro VBA

VBA-Macro-to-Delete-Rows-Based-on-Cell-Value. zip Tải xuống

Sử dụng Macro để xóa hàng dựa trên giá trị ô

Quy trình chuẩn bị dữ liệu của bạn có bao gồm việc xóa các hàng giống nhau dựa trên một điều kiện không?

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

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ị sẽ bị xóa
2. Sau đó, macro xóa các ô hiển thị trong phạm vi

Quá trình giải thích

Dưới đây là hình ảnh của tập 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

Để xóa các hàng có các ô trống như thế này, trước tiên, macro sẽ á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 hiển thị do bộ lọc để lại. Nó sử dụng phương thức SpecialCells để tạo tham chiếu đến các ô hiển thị

Điều này giống như sử dụng menu Go To Special [phím tắt Alt+;] để chọn khoảng 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 có thể xem toàn bộ phạm vi dữ liệu, trừ đi các hàng bạn đã xóa

Đó là macro 2 hoặc 3 bước đơn giản sẽ tiết kiệm thời gian thực hiện quy 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 bị lọc ra

Chân thành cảm ơn Hoàng đã chỉ ra điều này trên video YouTube

Mã vĩ mô VBA

Mã VBA dưới đây có thể được sao chép/dán vào Trình soạn thảo VB. Code cũng có trong file Excel ở phần Download bên 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 các tham chiếu Bảng tính và Phạm vi cho tệp cụ thể của mình

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

Khi bạn chạy macro, bạn sẽ nhận được thông báo cảnh báo bật lên có nội dung "Xóa toàn bộ hàng trang tính?"

Nếu bạn muốn chạy macro mà không làm gián đoạn hộp bật lên đó, bạn chỉ cần xóa dấu nháy đơn trước hai dòng mã bắt đầu bằng Ứng dụng. Cảnh báo hiển thị

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

Ứng dụng. Thuộc tính 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 bạn có thể thấy từ Excel khi xóa phạm vi, xóa trang tính, đóng mà không 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 dải ô, bạn vẫn có thể xóa các hàng dựa trên nội dung ô bằng cách sử dụng macro. Mã gần như giống nhau, nhưng được điều chỉnh một chút để áp dụng cho Bảng. Đây là mã bạn sẽ sử dụng cho Bảng

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 số macro bổ sung để giúp tùy chỉnh thêm quy trình

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

Bạn có thể tạo hộp thông báo tùy chỉnh trước khi xóa hàng, thay vì cảnh báo xóa hàng mặc định của Excel. Macro bên dưới cũng cho bạn biết số hàng nó sẽ xóa và hỏi bạn có muốn tiếp tục không

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à giao diện của hộp bật lên tùy chỉnh

Cửa sổ bật lên này chỉ dùng để kiểm tra trước khi xóa các hàng. Đó là một cách tuyệt vời để kiểm tra lại mọi thứ xem có ổn không 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

Hãy xem 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. Đây là một phần trong loạt video gồm 4 phần của tôi về Sổ làm việc Macro cá nhân

Xóa 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 macro bằng nhiều tiêu chí. Trong video ở trên, tôi lọc các hàng có cả trường Sản phẩm trống và ngày trước 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:="