Cách xóa hàng đã lọc 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

Nội dung chính Show

Video hướng dẫn
Tải xuống tệp Excel
Sử dụng Macro để xóa hàng dựa trên giá trị ô
Quá trình giải thích
Mã vĩ mô VBA
Tránh cảnh báo bật lên
Áp dụng Macro cho các bảng
Macro bổ sung
Thông báo cảnh báo tùy chỉnh
Xóa hàng dựa trên nhiều tiêu chí
Xóa hàng dựa trên tiêu chí do người dùng chỉ định
Điền trước tên người dùng vào ô nhập liệu
chủ đề liên quan
Phần kết luận
Cách tìm kiếm danh sách thả xuống xác thực dữ liệu trong Excel
Chuyển đến ô nguồn của công thức XLOOKUP
3 cách để điền vào các ô trống trong Excel
Macro VBA để tạo kết nối truy vấn nguồn cho tất cả các bảng Excel
Bạn có thể xóa các hàng trong Excel khi được lọc không?
Làm cách nào để xóa dữ liệu bộ lọc trong VBA?

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

Video hướng dẫn

Xem trên YouTube và Đăng ký Kênh của chúng tôi

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

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
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: 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 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: 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 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: 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à 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: 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 logic AND giữa các cột và tất cả các điều kiện phải được đáp ứng

Nếu bạn muốn sử dụng logic OR khi đá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ễ dàng nhất để tạo hai macro riêng biệt. Bạn có thể đặt tất cả mã trong một macro hoặc tạo một macro khác để gọi từng 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 tiêu chí do người dùng chỉ định

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

Chúng ta có thể sử dụng Ứng dụng. Phương thức InputBox để yêu cầu người dùng nhập tiêu chí

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 vào 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

Điền trước tên người dùng vào ô nhập liệu

Daniel cũng có câu hỏi về việc đặt tiêu chí lọc cho tên người dùng hiện tại. Ứng dụng. Thuộc tính Tên người dùng thực sự trả về giá trị này miễn là người dùng đã đặt đúng giá trị đó trong Office/Excel

Chúng ta có thể sử dụng giá trị này làm giá trị tham số Mặc định trong hộp nhập liệu

  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 trang tính mà bạn đang lọc, nhưng bạn cũng có thể đồng bộ hóa tên đó với người dùng để tiết kiệm nhiều thời gian cho họ

chủ đề liên quan

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

Hướng dẫn cơ bản về bộ lọc với VBA Macros
Cách sao chép và dán chỉ các ô hiển thị
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

Phần 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 nhập mà bạn biết là mình không cần đến–có thể vì chúng quá cũ hoặc chúng chỉ mới 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 từng giá trị của chúng

Tuy nhiên, tôi thường sử dụng phương pháp này vì sử dụng các bộ lọc tích hợp sẵn trong Excel có thể nhanh hơn rất nhiều. 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ì? . Cảm ơn bạn. 🙂

Trước đó Cách tạo các nút macro trong trang tính Excel

Tiếp theo Cách sử dụng Macro VBA để sao chép dữ liệu sang sổ làm việc khác

Bạn cũng có thể thích

Cách tìm kiếm danh sách thả xuống xác thực dữ liệu trong Excel

Chuyển đến ô nguồn của công thức XLOOKUP

3 cách để điền vào các ô trống trong Excel

Macro VBA để tạo kết nối truy vấn nguồn cho tất cả các bảng Excel

Sam Sammito nói.

Thông tin tốt Jon. làm rất tốt.
Cảm ơn

Jon Acampora nói.
Cảm ơn bạn Sam. tôi đánh giá cao sự hỗ trợ của bạn. 🙂

Terence Hayes nói.

Rất thú vị, tôi không nghĩ sẽ làm theo cách đó.
Cách thông thường của tôi là sử dụng vòng lặp For Next để kiểm tra từng hàng riêng lẻ.
Tôi cho rằng cách của bạn thanh lịch hơn và nhanh hơn đáng kể

Jon Acampora nói.
Cảm ơn Terence

Daniel Rippe nói.

Này Jon,

Bạn sẽ sửa đổi các macro này như thế nào để chúng lấy đầu vào cho các tiêu chí?

Có cách nào để nói Trống làm đầu vào và cách nó sẽ được gửi đến phần Tiêu chí của Bộ lọc tự động không?

Điều này sẽ cho phép mọi người sử dụng nó cho bất kỳ dữ liệu nào họ có

Tôi đoán việc nhận nhiều đầu vào sẽ khó thực hiện hơn một chút nhưng chúng ta sẽ thực hiện nó như thế nào?

Jon Acampora nói.
Chào Daniel,
Câu hỏi tuyệt vời. Tôi đã thêm một phần vào bài viết trên để trả lời điều này. Tôi đã thêm một macro mới sử dụng phương thức InputBox để nhắc người dùng nhập tiêu chí lọc
Tôi cũng đã thêm một phần sẽ trả lại tên người dùng cho InputBox làm giá trị mặc định, sử dụng Ứng dụng. thuộc tính Tên người dùng. Điều này sẽ hoạt động tốt nếu người dùng đã đặt tên của họ trong Office/Excel và tên đó khớp với giá trị trong bảng tính của bạn. Nếu không khớp thì có thể gõ lại vào InputBox. Hoặc bạn có thể thay đổi Office hoặc giá trị trong tệp để chúng khớp với nhau và điều này sẽ giúp người dùng tiết kiệm thời gian nhập tên của họ
Tệp mẫu cũng chứa macro mới
Tôi hy vọng điều đó sẽ giúp. Cảm ơn một lần nữa

Sudsy nói.

Xin chào John, như mọi khi rất nhiều thông tin. Hoàn toàn mới đối với VBA nên đánh giá cao cách tiếp cận chi tiết và chi tiết của bạn. Có vẻ như phạm vi được mã hóa cứng cho bộ lọc (B3. G1000)

Có khó để xác định phạm vi này một cách nhanh chóng?. hầu hết các tình huống tôi gặp phải, phạm vi thay đổi từ ngày này sang ngày khác

Tôi muốn bắt đầu ghi lại các đoạn trích để sử dụng lại, đồng thời xác định và ghi lại vô số cách để xác định phạm vi thao tác nằm ở đầu danh sách của tôi. Cảm ơn cho bất kỳ hướng

edwin nói.

Tôi nghĩ bạn có thể sử dụng phạm vi mở rộng quá mức chẳng hạn như B3. D100000 mà không có vấn đề gì
Jon Acampora nói.
Xin chào Sudsy,
Câu hỏi rất hay. Vâng, có một số cách để thực hiện điều này.
Trước tiên, hãy xem bài viết và video của tôi về 3 cách để tìm ô được sử dụng cuối cùng trên trang tính bằng VBA
Một cách tiếp cận khác là sử dụng Bảng Excel (ListObject trong VBA). Bạn có thể tham chiếu toàn bộ Bảng trong VBA và nó sẽ luôn bao gồm các hàng mới. Có một phiên bản macro ở trên dành cho Bảng Excel
Tôi hy vọng điều đó sẽ giúp. Cảm ơn
- Mike nói.
  Rời khỏi các hàng. sử dụng $B. $ G

Bob Bacon nói.

Xin chào Jon – Cảm ơn về một chủ đề liên quan khác và lời giải thích được thực hiện hoàn hảo của bạn (và hình ảnh đi kèm. ) Tôi tự hỏi liệu bạn có cân nhắc việc thêm mã vba hỏi người dùng tiêu chí lọc/xóa của họ không, sau đó áp dụng phản hồi của họ cho dữ liệu. Phương pháp này loại bỏ nhu cầu viết nhiều hơn một bộ hướng dẫn macro.
Một lần nữa xin cảm ơn bạn đã chia sẻ kiến thức chuyên môn về Excel.

Bob

Jon Acampora nói.
Xin chào Bob,
Câu hỏi rất hay. Tôi vừa thêm một phần mới trong bài viết ở trên cho tính năng này. Nó sử dụng Ứng dụng. Phương thức InputBox để nhắc người dùng chỉ định tiêu chí lọc.
Tệp mẫu cũng đã được cập nhật với macro mới
Tôi hy vọng điều đó sẽ giúp. Cảm ơn. 🙂

GaryZ nói.

Jon, macro của bạn cực kỳ hữu ích

Khi tôi sử dụng macro thông báo cảnh báo tùy chỉnh, nó sẽ ngắt đếm các hàng với thông báo lỗi. “424. đối tượng được yêu cầu"

Tôi đang sử dụng mã chính xác của bạn

lRows = WorksheetFunction. Tổng phụ(103, lo. Danh sáchCột(1). Dữ LiệuCơ ThểPhạm Vi. SpecialCells(xlCellTypeVisible))

Việc tôi sắp xếp tệp csv thay vì tệp excel có tạo ra sự khác biệt không?

Cảm ơn

Jon Acampora nói.
Xin chào Gary,
Tệp phải được lưu dưới dạng tệp xlsm. Mã này cũng dành cho Bảng Excel, vì vậy dữ liệu sẽ cần phải có trong Bảng Excel. Hoặc bạn có thể sửa đổi mã cho phạm vi thông thường, nằm trong macro đầu tiên.
Tôi hy vọng điều đó sẽ giúp

Edil nói.

Cảm ơn một lần nữa Jon,

Một bộ macro khác rất hữu ích đối với tôi

Cùng với macro “xóa hàng trống”, điều này rất tiện dụng

Daryl Duren nói.

Jon, tôi thích video của bạn và hướng dẫn của bạn. Bạn có kế hoạch tác giả bất kỳ cuốn sách?

Để tránh xảy ra lỗi nếu phạm vi hoặc bảng đã được lưu với các bộ lọc được áp dụng,
Tôi thêm dòng mã này trước khi lọc theo tiêu chí của mình.

Nếu bảng tính ("tên"). filtermode = true then worksheets(“name”). showalldata

Bec nói.

Hi Jon,

Cám ơn vì cái này. Có cách nào để làm cho bộ lọc của bạn tra cứu các giá trị sẽ sử dụng để lọc không?

Cảm ơn
Cảm ơn

Bittu nói.

Tôi rất hâm mộ các đoạn mã dễ hiểu của bạn với các hình minh họa rất đơn giản để chứng minh mã này hoạt động như thế nào và như thế nào. Tôi thực sự đánh giá cao cách bạn giải thích và viết mã để những người không chuyên hay đúng hơn là người mới bắt đầu có thể dễ dàng hiểu và sửa đổi mã theo yêu cầu của mình. Cảm ơn vì những hướng dẫn tuyệt vời của bạn, hãy tiếp tục phát huy

Christina nói.

Hi Jon,

điều này cực kỳ hữu ích.
Khi tôi sửa đổi các tiêu chí, tất cả đều hoạt động, nhưng khi không có hàng nào khớp với tiêu chí (tôi đã sử dụng mã cho nhiều tiêu chí), bằng cách nào đó, nó bị kẹt, không hiển thị hàng và không hiển thị .
Có điều gì tôi đang làm sai không (tôi đã sử dụng chính xác mã của bạn và mã vẫn hoạt động tốt nếu có các hàng khớp với tiêu chí) hay nên áp dụng điều gì khác cho mã?

Cảm ơn rất nhiều
Christina

Josh nói.
Tôi đã gặp một thử thách tương tự và cuối cùng nhận ra rằng đó là do định dạng số. Tôi muốn xóa các hàng không có số dư nhưng chúng ở định dạng đô la. Nó không hiệu quả với tôi cho đến khi tôi thay đổi tiêu chí lọc thành “$0. 00”. Hi vọng điêu nay co ich

Chris nói.

Hi Jon

Tôi đang cố xóa một số hàng khỏi trang tính theo ví dụ đầu tiên của bạn. Tôi muốn chọn “Tiêu chí” từ một ô trên một trang tính khác trong cùng một sổ làm việc

Tôi đã thử như sau

Dim Wk dưới dạng Chuỗi
Đặt Wk = ThisWorkbook. Bảng tính (“Thiết kế”). Phạm vi(“K2”)
.
.
.
.
ws. Phạm vi ("A2. D1000”). Trường bộ lọc tự động. =3, Tiêu chí1. =Wk

nhưng tôi gặp lỗi biên dịch ở dòng mã Set WK

Tôi đang sai ở đâu?
Tôi rất cảm kích sự trợ giúp của bạn.

sandeep kothari nói.

Jon thân mến, Tuyệt vời, như thường lệ.
Vui lòng sửa mã trong ví dụ đầu tiên về Bảng Excel ở trên.
ws. Kích hoạt

biến ws chưa được khai báo & do đó mã không hoạt động

Tất cả những gì tôi đã học được trong VBA phần lớn là do bạn

Độ tin cậy R. nói.

xin vui lòng là người mới trong VBA, tôi muốn sửa đổi mã VBA của bạn cho nhiều tiêu chí để lọc dữ liệu của tôi nhưng cột A đầu tiên của tôi là ngày và giờ ở định dạng này 1/1/2015 0. 00 vì vậy tôi muốn xóa từng hàng trừ 9. 00 và 18. 00 hàng ngày cho mỗi ngày trong một tháng vì mỗi trang tính có dữ liệu cho cả tháng. xin vui lòng tôi cần sự giúp đỡ của bạn. cảm ơn

Vincent nói.

Cảm ơn vì tất cả các hướng dẫn.
Tôi đang làm việc với mã của bạn để xóa các hàng cho nhiều tiêu chí trong một bảng.
Làm cách nào để áp dụng nó với các tiêu chí trên cùng một cột?

Ví dụ: tôi muốn lọc các giá trị không phải là “A, B, C, D” và xóa các hàng đó

Alex nói.

Tôi làm cách nào để "Xóa hàng dựa trên tiêu chí do người dùng chỉ định" hoạt động trên nhiều trang tính nhưng chỉ yêu cầu nhập tên một lần?

Alex nói.

Bắt đầu thử và tự làm và bị mắc kẹt
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim lo As ListObject
Dim lRows As LongDim lRows As LongDim vbAnswer As VbMsgBoxResult
Dim sCriteria As Variant
‘ Đặt WS_Count bằng số lượng trang tính trong
‘ sổ làm việc đang hoạt động.
WS_Count = ActiveWorkbook. bảng tính. Đếm
‘ Bắt đầu vòng lặp.
For I = 1 To WS_Count
‘Hiển thị lời nhắc thông báo Có/Không trước khi xóa các hàng
‘Đặt tham chiếu cho trang tính và Bảng.
Đặt lo = ActiveSheet. ListObjects(1)
lo. Cha mẹ. Kích hoạt 'Kích hoạt trang tính mà Bảng đang bật.
‘Xóa mọi bộ lọc hiện có
lo. Bộ lọc tự động. ShowAllData
‘Yêu cầu người dùng nhập dữ liệu
sCriteria = ActiveSheet. Phạm vi(“e1”). Giá trị
‘Thoát nếu người dùng nhấn nút Hủy
Nếu sCriteria = False Then Exit Sub
‘1. Áp dụng bộ lọc
lo. Bộ lọc tự động. ShowAllData
‘cố gắng thiết lập nó để tham chiếu một ô, các trang tính khác đã thiết lập nó để lấy giá trị từ cùng một ô trong trang tính 1
lo. Phạm vi. Trường bộ lọc tự động. =1, Tiêu chí1. =Trang tính đang hoạt động. Phạm vi(“e1″). Giá trị
'Đếm số hàng & thông báo hiển thị
Lỗi khi tiếp tục tiếp theo
'thông báo lỗi ở đây với mã này
lHàng . Tổng phụ(103, lo. Danh sáchCột(1). Dữ LiệuCơ ThểPhạm Vi. SpecialCells(xlCellTypeVisible))
Khi Lỗi GoTo 0
vbAnswer = MsgBox(lRows & ” Hàng sẽ bị xóa. Bạn có muốn tiếp tục không?”, vbYesNo, “Delete Rows Macro”)
Nếu vbAnswer = vbYes Thì
‘Ứng dụng. DisplayAlerts = Sai
lo. Dữ LiệuCơ ThểPhạm Vi. Ô đặc biệt(xlCellTypeVisible). Toàn bộ hàng. Xóa
Ứng dụng. Cảnh báo hiển thị = Đúng
lo. Bộ lọc tự động. Hiển thịTất cảDữ liệu
kết thúc nếu
MsgBox ActiveWorkbook. Bảng tính(I). Tên
tiếp theo tôi
kết thúc phụ
Keith nói.
Alex, bạn có thể làm cho mã này hoạt động không?

Dave nói.

Có vẻ tốt nhưng tôi nhận được thông báo lỗi sau khi cố chạy cái này

o. Dữ LiệuCơ ThểPhạm Vi. Ô đặc biệt(xlCellTypeVisible). Xóa - điều này dẫn đến lỗi cũng như ws. Hoạt động

——————-

Sub Delete_Rows_Based_On_Value_Table()
‘Áp dụng bộ lọc cho Bảng và xóa các hàng hiển thị
‘Nguồn. https. //www. khuôn viên excel. com/vba/delete-rows-cell-values/

Dim lo As ListObject

‘Đặt tham chiếu cho trang tính và Bảng.
Đặt lo = Sheet3. ListObjects(1)
ws. Kích hoạt

‘Xóa mọi bộ lọc hiện có
lo. Bộ lọc tự động. ShowAllData

‘1. Áp dụng bộ lọc
lo. Phạm vi. Trường bộ lọc tự động. =4, Tiêu chí1. =”Sản phẩm 2″

‘2. Xóa hàng
Ứng dụng. DisplayAlerts = Sai
lo. Dữ LiệuCơ ThểPhạm Vi. Ô đặc biệt(xlCellTypeVisible). Xóa
Ứng dụng. Cảnh báo hiển thị = Đúng

‘3. Xóa bộ lọc
lo. Bộ lọc tự động. ShowAllData

kết thúc phụ

Rajkumar nói.

Xin chào

hướng dẫn này là tốt

Cách xóa ô từ cột A sang cột M phụ thuộc vào giá trị ở cột M (trống). Tôi không muốn xóa toàn bộ hàng

Heidi nói.

Tôi đang cố gắng thiết lập một macro để nhận khi một hàng/ô nói có, sau đó sao chép và dán dữ liệu đó vào một trang mới và làm mới trang gốc…. Tôi dường như chỉ có thể ra ngoài để thực hiện hàng 1, thay vì khi một hàng nói “có”…. Có ai giúp được không?

Sadeesh nói.

Xin chào Jon, tôi đang cố gắng tạo một tệp macro để giữ lại một số cột nhất định dựa trên các tiêu đề và xóa tất cả các phần còn lại, Các tiêu đề cột hợp lệ phải được giới thiệu bằng một trang tính excel khác (Điều này có thể là động), tôi đang cố gắng xây dựng nó

Mike Kamish nói.

Xin chào Jon,
Tôi chưa quen với macro Excel. Trong ví dụ của bạn, tôi không chắc trường “Trường. =4 đến từ. Nếu tôi muốn xóa tất cả các hàng có cột ngày trước một giá trị nào đó, tôi có thể sử dụng giá trị này không?

s. Phạm vi ("AV2. G1000”). Trường bộ lọc tự động. =4, Tiêu chí1. =”<= 1/1/2000"

Cám ơn rất nhiều

Jon Acampora nói.
Xin chào Mike,
Trường. =4 là số cột trong phạm vi đã lọc. Nó đề cập đến cột thứ 4. Đây là một bài viết về cách sử dụng phương pháp AutoFilter trong VBA. Có một liên kết đến một trang trên các bộ lọc cho các ngày.
https. //www. khuôn viên excel. com/vba/macro-filters-autofilter-method/
Tôi hy vọng điều đó sẽ giúp. Cảm ơn một lần nữa và chúc một ngày tốt lành

Naxal nói.

Cảm ơn bạn đã cung cấp thông tin. Làm cách nào để xóa nhiều hàng có các ô khác nhau
Ví dụ

Tôi muốn xóa các hàng có giá trị ô là 156,158, 189, 200

Điều đó sẽ thay đổi phần tiêu chí1 trong mã macro của bạn như thế nào.
lo. Phạm vi. Trường bộ lọc tự động. =4, Tiêu chí1. =”Sản phẩm 2″

Cedric nói.
Bạn đã bao giờ tìm thấy một câu trả lời cho câu hỏi này?
- Jon Acampora nói.
  Xin chào Cedric,
  Tôi vừa đăng một câu trả lời và muốn đảm bảo rằng bạn đã xem nó. Cảm ơn.
Jon Acampora nói.
Xin chào Naxal,
Câu hỏi rất hay. Và tôi xin lỗi vì đã không trả lời sớm hơn.
Bạn có thể sử dụng Mảng trong tham số Tiêu chí để chỉ định nhiều tiêu chí, như sau
tiêu chí1. =Array(“156”, “158”, “189”, “200”), Toán tử. =xlFilterValues
Đây là một bài viết về cách sử dụng phương pháp AutoFilter giải thích thêm
https. //www. khuôn viên excel. com/vba/macro-filters-autofilter-method/
Tôi hy vọng điều đó sẽ giúp. Cảm ơn một lần nữa và chúc một ngày tốt lành

jess nói.

Cảm ơn

Sarfaraz nói.

Điều gì sẽ xảy ra nếu Không có “Sản phẩm 2” trong phạm vi/cột đã chọn để xóa. vĩ mô sẽ dừng lại? .
Tôi có hướng dẫn tạo báo cáo hàng ngày tôi phải chọn dữ liệu cụ thể và muốn xóa toàn bộ hàng đó. Nếu tôi sẽ sử dụng macro ở trên để xóa dữ liệu như bạn đã làm cho Sản phẩm 2. Nó sẽ thực hiện công việc nhưng nếu toàn bộ trang tính của tôi không có sản phẩm dữ liệu 2 thì macro có dừng không?

Zach nói.

Jon,
Tôi đang gặp sự cố với macro này khi để hàng đầu tiên chứa tiêu chí, nhưng xóa tất cả các tiêu chí khác khi tôi áp dụng nó vào dữ liệu của mình.
Có đề xuất nào không?
TIA

Dale Anderson nói.

hướng dẫn tuyệt vời. Cảm ơn. tôi có một câu hỏi. Có thể làm cho phạm vi năng động? . Cảm ơn

VJ nói.

Cảm ơn Jon

Tôi đã sử dụng mã của bạn làm cơ sở để tạo macro để đáp ứng yêu cầu của tôi. Điều tôi đang cố gắng đạt được là có thể xóa các hàng khỏi bảng tính gốc và lưu nó dưới dạng tệp MỚI trong cùng đường dẫn nơi đặt bảng tính gốc. Tuy nhiên, hãy đóng bảng tính ban đầu mà không thực hiện bất kỳ thay đổi nào (tôi. e. không xóa hàng)

Tôi có 2 vấn đề.
1. Tôi không thể tắt Msgbox, theo gợi ý của bạn.
2. Mặc dù chọn “Hủy”, mã vẫn tiếp tục và lưu một tệp mới.

‘1. Áp dụng bộ lọc
ws. Phạm vi ("A2. W1474″). Trường bộ lọc tự động. =21, Tiêu chí1. =”Vị trí”

‘2. Xóa hàng
Ứng dụng. DisplayAlerts = Sai
ws. Phạm vi ("A2. W1474”). Ô đặc biệt(xlCellTypeVisible). Xóa
Ứng dụng. Cảnh báo hiển thị = Đúng

'Khi Lỗi GoTo 0

‘3. Xóa bộ lọc
Khi có lỗi Tiếp tục tiếp tục
ws. ShowAllData
Khi Lỗi GoTo 0

Khi Lỗi Tiếp tục Tiếp theo
wb. Lưu dưới dạng tên tệp. =(strFolder & “NEW_” & strFile)
Khi Lỗi GoTo 0

xlApp. Hiển thị = Sai
wb. Đóng

kết thúc phụ

Rajiv Nair nói.

Cảm ơn bạn. Giải thích rõ ràng, tuyệt vời

Margery Kuhn nói.

Xóa hàng dựa trên tiêu chí do người dùng chỉ định

Điều này rất gần với những gì tôi cần……
Tôi cần nó để xóa mọi thứ NHƯNG những gì người dùng đã nhập.

Xóa không bằng đầu vào Người dùng.
A 1
A 2
A 3
B 4
B 5
B 6
C 7
C 8
C 9

Nếu người dùng nhập B - Tôi muốn nó xóa tất cả các hàng A & C, giữ nguyên B

Bất kỳ trợ giúp sẽ được đánh giá cao

YG nói.

Xin chào,

Có cách nào để chạy các macro ở trên nhưng trên một nhóm trang tính cụ thể trong sổ làm việc không?

Macro siêu tiện dụng. Cám ơn rất nhiều

Lars nói.
Vâng, những gì bạn có thể làm là lặp qua tất cả các trang tính trong sổ làm việc (https. //hỗ trợ. Microsoft. com/en-us/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook) rồi kiểm tra xem trang tính có đúng màu tab hay không (bằng cách sử dụng tab. color) và sau đó chạy mã ở trên nếu màu chính xác

Hossein Nouri nói.

Hi Jon,

Điều gì sẽ là macro để xóa các hàng cho doanh số bán hàng bị vô hiệu hóa trong bảng Excel ba cột sau đây (i. e. , tất cả các hàng cho Tran# 1053899)?

Tran# Code
1053898 1 2402C MODELO ĐẶC BIỆT 2/12 CN
1053898 900 1 TỔNG CỘNG MẶT HÀNG
1053898 . 875% NJ Thuế trên 15. 99
1053898 950 ID # 101 TỔNG CỘNG
1053898 980 TIỀN MẶT
1053898 999 ĐỔI TIỀN MẶT
. 875% NJ Thuế trên 16. 99
1053899 2 750ML TRAVELERS CLUB VODKA
1053899 3 375ML SEAGRAMS EXTRA GIN
1053899 1001 ******* VOID SALE *******
1053900 1 2402C GROLSCH 2/12 NR
1053900 900 1 ITEMS SUBTOTAL
1053900 941 6.875% NJ Tax on 16.99
1053900 950 ID # 101 TỔNG CỘNG
1053900 980 TIỀN MẶT
1053900 999 ĐỔI TIỀN MẶT

Lars nói.

Điều này đã giúp rất nhiều nhưng tôi có một vấn đề. Bảng mà tôi muốn sử dụng Macro này nằm trong một trang tính cũng có dữ liệu khác. Vấn đề của tôi là khi xóa dữ liệu trong vùng dữ liệu đó, nó cũng xóa dữ liệu nằm ngoài bảng trên cùng một hàng. Có cách nào để sửa lỗi này?

JT nói.

Xin chào Jon,
Hướng dẫn của bạn rất hữu ích và dễ hiểu. Điều thú vị là tôi đã thử một số tùy chọn mã khác nhau và “. Xóa” một phần của

Phạm vi ("A3. Y1000”). Ô đặc biệt(xlCellTypeVisible). Xóa bỏ

đơn giản là không xóa các ô. Rất bực bội. Bạn có thể đưa ra bất kỳ lời giải thích là tại sao?

Để tham khảo, tôi đã ghi lại macro của mình trong Excel 365, sau đó gỡ lỗi/chèn mã của bạn vào đúng vị trí của nó… Tôi đang cố dọn sạch một bảng sau khi sao chép hàng loạt và dán, xóa các hàng không cần thiết dựa trên Cột A để trống. Như đã đề cập, mọi thứ hoạt động hoàn hảo ngoại trừ không có gì xóa

Cảm ơn bạn rất nhiều. Tôi chắc chắn sẽ có cà phê nếu bạn có thể giúp 🙂

Dewey nói.

Tôi thích thông tin bạn đã cung cấp CẢM ƠN BẠN. Điều này rất hữu ích và tôi chắc chắn rằng nó sẽ mở rộng khả năng của tôi. Tôi đang cố bẻ một đai ốc mà tôi cảm thấy khó và tôi có thể tưởng tượng rằng nhiều người dùng khác cũng gặp phải vấn đề tương tự. Tôi có thể thực hiện tác vụ theo cách thủ công nhưng mất nhiều thời gian. Đó là lý tưởng cho một kịch bản. Ý tưởng là loại bỏ các bản ghi trùng lặp đáp ứng các tiêu chí nhất định, tôi. e. 2 bản ghi giống hệt nhau, trong đó một bản ghi có câu lệnh “Đạt” trong một ô và bản ghi còn lại thì không. Tôi cần xóa dòng/bản ghi không đạt (trống) nhưng khi xảy ra các bản ghi trùng lặp, hãy xóa một trong 2 bản trùng nếu không có thành phần “Đạt”. Tôi nghĩ điều này sẽ biến thành 2 Tập lệnh VB riêng biệt. Tôi sẽ tiếp tục tìm kiếm trên web và đặc biệt là trang web của bạn vì tôi nghĩ bạn có thể đã trả lời đủ về điều này trong một bài viết khác, nơi thông tin ở đó chỉ cần sửa đổi một chút hoặc kết hợp cả 2 và làm việc với nó. Một lần nữa, cảm ơn vì một trang web tuyệt vời

MAKA nói.

sử dụng ví dụ đầu tiên, nó sẽ xóa dòng đầu tiên nếu không có dòng nào có tiêu chí xóa

Jundi nói.

Xin chào,
Cảm ơn bạn vì mã này hoạt động rất tốt nhưng làm cách nào để mã này hoạt động trên nhiều trang tính và xóa các tiêu chí giống nhau.

Cảm ơn

Ahab Abdel-Aziz nói.

Điều này thật tuyệt vời theo mọi cách. Các giải pháp rất tinh tế, tài liệu và ví dụ kỹ lưỡng, và phần giải thích bằng video hoàn toàn có thể truy cập được, ngay cả đối với một OG như tôi, người biết rất ít. Cảm ơn bạn

Jamie nói.

Cảm ơn vì hướng dẫn tuyệt vời này. Tôi rất mới sử dụng macro excel và tôi đã có thể áp dụng điều này vào trang tính của mình

Tôi muốn có thể xóa mọi thứ trừ giá trị do người dùng nhập

Tôi có cần thêm nội dung nào đó vào phần Áp dụng bộ lọc để lọc mọi thứ trừ sCriteria không?

Hay tôi cần thay đổi điều gì đó trong phần Xóa hàng để xóa tất cả dữ liệu ẩn?

Cảm ơn bạn đã giúp đỡ

Bill Palmer nói.

Cảm ơn bạn rất nhiều, Jon, vì những video tuyệt vời. Tìm thấy những gì tôi đang tìm kiếm trên web không phải lúc nào cũng dễ dàng, nhưng khi tôi truy cập vào đó và thấy tên của bạn, lời giải thích của bạn sẽ thành công

Kyle nói.

Có cách nào để ngăn macro chuyển sang chế độ gỡ lỗi khi không có giá trị mục tiêu nào không? . Nếu nút được nhấn, nó sẽ chuyển sang chế độ gỡ lỗi

Tim nói.

Xin chào Jon Acampora,

Tôi đánh giá cao đoạn mã này và nó hoạt động tốt trừ khi không có dữ liệu phù hợp với tiêu chí. Làm cách nào để xóa bộ lọc nếu không tìm thấy dữ liệu để mã không bị lỗi?

David nói.

Cảm ơn vì những lời khuyên. Tuy nhiên, vấn đề với một tuyên bố như

ws. Phạm vi ("B4. G1000”). Ô đặc biệt(xlCellTypeVisible). Xóa bỏ

Bạn không biết có bao nhiêu hàng trong phạm vi, vì vậy bạn chỉ đặt nó thành một số lớn (không phải 1000 là lớn như vậy). Tuy nhiên, bạn có thể làm cho nó giống như

Bạn có thể xóa các hàng trong Excel khi được lọc không?

Sau khi dữ liệu được lọc, bạn chỉ có thể xóa các hàng đã lọc bằng cách chọn dữ liệu đã lọc và nhấn đồng thời các phím "Ctrl" và "-" trên bàn phím của mình . Thao tác này sẽ mở hộp thoại "Xóa". Đảm bảo rằng tùy chọn "Shift cells up" được chọn và sau đó nhấp vào nút "OK". . This will open the "Delete" dialog box. Make sure that the "Shift cells up" option is selected and then click the "OK" button.

Làm cách nào để xóa dữ liệu bộ lọc trong VBA?

Xóa bộ lọc khỏi một cột . Ví dụ: hình bên dưới mô tả một ví dụ về xóa bộ lọc khỏi cột Quốc gia. Ghi chú. Bạn không thể xóa bộ lọc khỏi các cột riêng lẻ. Bộ lọc được bật cho toàn bộ phạm vi hoặc tắt. button next to the column heading, and then click Clear Filter from <"Column Name">. For example, the figure below depicts an example of clearing the filter from the Country column. Note: You can't remove filters from individual columns. Filters are either on for an entire range, or off.

programming excel

Cách xóa hàng đã lọc trong excel vba

Video hướng dẫn

Tải xuống tệp Excel

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

Quá trình giải thích

Mã vĩ mô VBA

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

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

Macro bổ sung

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

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

Xóa hàng dựa trên tiêu chí do người dùng chỉ định

Điền trước tên người dùng vào ô nhập liệu

chủ đề liên quan

Phần kết luận

Bạn cũng có thể thích

Cách tìm kiếm danh sách thả xuống xác thực dữ liệu trong Excel

Chuyển đến ô nguồn của công thức XLOOKUP

3 cách để điền vào các ô trống trong Excel

Macro VBA để tạo kết nối truy vấn nguồn cho tất cả các bảng Excel

Bạn có thể xóa các hàng trong Excel khi được lọc không?

Làm cách nào để xóa dữ liệu bộ lọc trong VBA?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội