Chọn các hàng đã lọc trong Excel VBA
7. Giả sử bạn muốn lọc và dán dữ liệu vào một sổ làm việc mới thay vì thêm các trang tính trong cùng một sổ làm việc Show
Option Explicit Sub filter() Application.ScreenUpdating = False Dim x As Range Dim rng As Range Dim rng1 As Range Dim last As Long Dim sht As String Dim newBook As Excel.Workbook Dim Workbk As Excel.Workbook 'Specify sheet name in which the data is stored sht = "DATA Sheet" 'Workbook where VBA code resides Set Workbk = ThisWorkbook 'New Workbook Set newBook = Workbooks.Add(xlWBATWorksheet) Workbk.Activate 'change filter column in the following code last = Workbk.Sheets(sht).Cells(Rows.Count, "F").End(xlUp).Row With Workbk.Sheets(sht) Set rng = .Range("A1:F" & last) End With Workbk.Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True For Each x In Workbk.Sheets(sht).Range([AA2], Cells(Rows.Count, "AA").End(xlUp)) With rng .AutoFilter .AutoFilter Field:=6, Criteria1:=x.Value .SpecialCells(xlCellTypeVisible).Copy newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value newBook.Activate ActiveSheet.Paste End With Next x ' Turn off filter Workbk.Sheets(sht).AutoFilterMode = False With Application .CutCopyMode = False .ScreenUpdating = True End With End Sub8. Trường hợp muốn dán dữ liệu vào nhiều workbook sau khi lọc dữ liệu. Dữ liệu cho từng giá trị duy nhất sẽ được lưu trong một sổ làm việc khác có tên giống như giá trị duy nhất Option Explicit Sub filter() Application.ScreenUpdating = False Dim x As Range Dim rng As Range Dim rng1 As Range Dim last As Long Dim sht As String Dim newBook As Excel.Workbook Dim Workbk As Excel.Workbook 'Specify sheet name in which the data is stored sht = "DATA Sheet" 'Workbook where VBA code resides Set Workbk = ThisWorkbook 'change filter column in the following code last = Workbk.Sheets(sht).Cells(Rows.Count, "F").End(xlUp).Row With Workbk.Sheets(sht) Set rng = .Range("A1:F" & last) End With Workbk.Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True ' Loop through unique values in column For Each x In Workbk.Sheets(sht).Range([AA2], Cells(Rows.Count, "AA").End(xlUp)) With rng .AutoFilter .AutoFilter Field:=6, Criteria1:=x.Value .SpecialCells(xlCellTypeVisible).Copy 'Add New Workbook in loop Set newBook = Workbooks.Add(xlWBATWorksheet) newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value newBook.Activate ActiveSheet.Paste End With 'Save new workbook newBook.SaveAs x.Value & ".xlsx" 'Close workbook newBook.Close SaveChanges:=False Next x ' Turn off filter Workbk.Sheets(sht).AutoFilterMode = False With Application .CutCopyMode = False .ScreenUpdating = True End With End Sub Bạn có thể bỏ dòng mã này nếu không muốn đóng workbook mới tạo9. Giả sử bạn muốn tính tổng các giá trị trong mỗi trang tính. Chương trình sau tính tổng cột B của mỗi trang tính. Nó cũng viết 'Tổng số' ở hàng cuối cùng của cột A. Nó giả sử dữ liệu của bạn bắt đầu từ cột A Sub sumLoop() 10. Cách lọc chỉ một giá trị thay vì tất cả các giá trị duy nhất Xóa dòng mã sau -
Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True
Bước tiếp theo là nhập giá trị bạn muốn lọc vào ô AA2 và đảm bảo không có gì được điền vào toàn bộ cột AA ngoại trừ ô AA2. Ví dụ: tôi sẽ nhập giá trị 1 vào ô AA2 vì tôi muốn chọn 1 trong cột F (cột 'xếp hạng') Nhấp vào liên kết bên dưới để xem macro đang hoạt độngTải xuống sổ làm việc Liên kết có liên quan - Lọc dữ liệu dựa trên lựa chọn thả xuống Hướng dẫn Excel. 100 bài hướng dẫn Excel Thông báo bài viết liên quan về tác giả Deepanshu thành lập ListenData với một mục tiêu đơn giản - Làm cho các phân tích trở nên dễ hiểu và dễ theo dõi. Ông có hơn 10 năm kinh nghiệm trong lĩnh vực khoa học dữ liệu. Trong nhiệm kỳ của mình, ông đã làm việc với các khách hàng toàn cầu trong nhiều lĩnh vực khác nhau như Ngân hàng, Bảo hiểm, Cổ phần tư nhân, Viễn thông và Nhân sự Nhiều chức năng của Excel cũng có sẵn để sử dụng trong VBA – và phương thức Autofilter là một trong những chức năng như vậy Nếu bạn có một tập dữ liệu và bạn muốn lọc nó bằng một tiêu chí, bạn có thể dễ dàng thực hiện việc đó bằng cách sử dụng tùy chọn Bộ lọc trong dải băng Dữ liệu Và nếu bạn muốn một phiên bản nâng cao hơn của nó, thì cũng có một bộ lọc nâng cao trong Excel Vậy tại sao lại sử dụng AutoFilter trong VBA? Nếu bạn chỉ cần lọc dữ liệu và thực hiện một số thao tác cơ bản, tôi khuyên bạn nên sử dụng chức năng Bộ lọc sẵn có mà giao diện Excel cung cấp Bạn nên sử dụng Bộ lọc tự động VBA khi bạn muốn lọc dữ liệu như một phần của quá trình tự động hóa (hoặc nếu nó giúp bạn tiết kiệm thời gian bằng cách lọc dữ liệu nhanh hơn) Ví dụ: giả sử bạn muốn lọc nhanh dữ liệu dựa trên lựa chọn thả xuống, sau đó sao chép dữ liệu đã lọc này vào một trang tính mới Mặc dù điều này có thể được thực hiện bằng cách sử dụng chức năng bộ lọc sẵn có cùng với một số thao tác sao chép-dán, nhưng bạn có thể mất nhiều thời gian để thực hiện việc này theo cách thủ công Trong trường hợp như vậy, sử dụng Bộ lọc tự động VBA có thể tăng tốc mọi thứ và tiết kiệm thời gian Ghi chú. Tôi sẽ đề cập đến ví dụ này (về cách lọc dữ liệu dựa trên lựa chọn thả xuống và sao chép vào một trang tính mới) ở phần sau của hướng dẫn này Hướng dẫn này bao gồm Cú pháp bộ lọc tự động VBA của ExcelExpression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )
Ngoài Biểu thức, tất cả các đối số khác là tùy chọn Trong trường hợp bạn không sử dụng bất kỳ đối số nào, nó sẽ chỉ áp dụng hoặc xóa các biểu tượng bộ lọc cho các cột Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub Đoạn mã trên sẽ chỉ áp dụng phương thức Autofilter cho các cột (hoặc nếu nó đã được áp dụng, nó sẽ xóa nó) Điều này đơn giản có nghĩa là nếu bạn không thể nhìn thấy các biểu tượng bộ lọc trong tiêu đề cột, bạn sẽ bắt đầu nhìn thấy nó khi đoạn mã trên được thực thi và nếu bạn có thể nhìn thấy nó, thì nó sẽ bị xóa Trong trường hợp bạn có bất kỳ dữ liệu nào đã được lọc, nó sẽ xóa các bộ lọc và hiển thị cho bạn tập dữ liệu đầy đủ Bây giờ, hãy xem một số ví dụ về việc sử dụng Bộ lọc tự động VBA của Excel sẽ làm rõ cách sử dụng của nó Thí dụ. Lọc dữ liệu dựa trên điều kiện Văn bảnGiả sử bạn có tập dữ liệu như hình bên dưới và bạn muốn lọc tập dữ liệu đó dựa trên cột 'Mục' Đoạn mã dưới đây sẽ lọc tất cả các hàng có mục là 'Máy in' Sub FilterRows() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer" End Sub Đoạn mã trên đề cập đến Sheet1 và trong đó, nó đề cập đến A1 (là một ô trong tập dữ liệu) Lưu ý rằng ở đây chúng tôi đã sử dụng Trường. =2, vì cột mặt hàng là cột thứ hai trong tập dữ liệu của chúng tôi từ bên trái Bây giờ nếu bạn đang nghĩ - tại sao tôi cần làm điều này bằng mã VBA. Điều này có thể dễ dàng được thực hiện bằng chức năng bộ lọc sẵn có. Bạn đúng Nếu đây là tất cả những gì bạn muốn làm, tốt hơn nên sử dụng chức năng Bộ lọc sẵn có Nhưng khi bạn đọc phần hướng dẫn còn lại, bạn sẽ thấy rằng điều này có thể được kết hợp với một số mã bổ sung để tạo ra khả năng tự động hóa mạnh mẽ Nhưng trước khi tôi cho bạn xem những thứ đó, trước tiên hãy để tôi trình bày một vài ví dụ để cho bạn thấy tất cả những gì mà phương pháp AutoFilter có thể làm Bấm vào đây để tải file ví dụ về và làm theo Thí dụ. Nhiều Tiêu chí (VÀ/HOẶC) trong cùng một CộtGiả sử tôi có cùng tập dữ liệu và lần này tôi muốn lọc tất cả các bản ghi có mục là 'Máy in' hoặc 'Máy chiếu' Đoạn mã dưới đây sẽ làm điều này Sub FilterRowsOR() Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector" End Sub Lưu ý rằng ở đây tôi đã sử dụng toán tử xlOR Điều này yêu cầu VBA sử dụng cả hai tiêu chí và lọc dữ liệu nếu bất kỳ tiêu chí nào trong hai tiêu chí được đáp ứng Tương tự, bạn cũng có thể sử dụng tiêu chí AND Ví dụ: nếu bạn muốn lọc tất cả các bản ghi có số lượng lớn hơn 10 nhưng nhỏ hơn 20, bạn có thể sử dụng mã bên dưới Sub FilterRowsAND() Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20" End Sub Thí dụ. Nhiều tiêu chí với các cột khác nhauGiả sử bạn có tập dữ liệu sau Với Autofilter, bạn có thể lọc nhiều cột cùng lúc Ví dụ: nếu bạn muốn lọc tất cả các bản ghi có mục là 'Máy in' và Đại diện bán hàng là 'Đánh dấu', bạn có thể sử dụng mã bên dưới ________số 8_______Thí dụ. Lọc 10 bản ghi hàng đầu bằng phương pháp AutoFilterGiả sử bạn có tập dữ liệu dưới đây Dưới đây là mã sẽ cung cấp cho bạn 10 bản ghi hàng đầu (dựa trên cột số lượng) Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Items End Sub Trong đoạn mã trên, tôi đã sử dụng ActiveSheet. Bạn có thể sử dụng tên trang tính nếu bạn muốn Lưu ý trong ví dụ này muốn lấy top 5 chỉ cần thay số ở Criteria1. =”10″ từ 10 đến 5 Vì vậy, đối với 5 mặt hàng hàng đầu, mã sẽ là Sub FilterRowsTop5() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10Items End Sub Nó có thể trông kỳ lạ, nhưng cho dù bạn muốn có bao nhiêu mục hàng đầu, giá trị Toán tử luôn luôn là xlTop10Items Tương tự, đoạn mã dưới đây sẽ cung cấp cho bạn 10 mục dưới cùng Sub FilterRowsBottom10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10Items End Sub Và nếu bạn muốn 5 mục dưới cùng, hãy thay đổi số trong Tiêu chí 1. =”10″ từ 10 đến 5 Thí dụ. Lọc 10 phần trăm hàng đầu bằng phương pháp tự động lọcGiả sử bạn có cùng một tập dữ liệu (như được sử dụng trong các ví dụ trước) Dưới đây là mã sẽ cung cấp cho bạn 10 phần trăm bản ghi hàng đầu (dựa trên cột số lượng) Sub FilterRowsTop10() ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10Percent End Sub Trong tập dữ liệu của chúng tôi, vì chúng tôi có 20 bản ghi, nó sẽ trả về 2 bản ghi hàng đầu (chiếm 10% tổng số bản ghi) Thí dụ. Sử dụng ký tự đại diện trong bộ lọc tự độngGiả sử bạn có một tập dữ liệu như hình bên dưới Nếu bạn muốn lọc tất cả các hàng có tên mặt hàng chứa từ 'Board', bạn có thể sử dụng mã bên dưới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub0 Trong đoạn mã trên, tôi đã sử dụng ký tự đại diện * (dấu hoa thị) trước và sau từ ‘Board’ (là tiêu chí) Dấu hoa thị có thể đại diện cho bất kỳ số ký tự nào. Vì vậy, điều này sẽ lọc bất kỳ mục nào có từ 'bảng' trong đó Thí dụ. Sao chép các hàng đã lọc vào một trang tính mớiNếu bạn không chỉ muốn lọc các bản ghi dựa trên tiêu chí mà còn sao chép các hàng đã lọc, bạn có thể sử dụng macro bên dưới Nó sao chép các hàng đã lọc, thêm một trang tính mới, rồi dán các hàng đã sao chép này vào trang tính mới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub1 Đoạn mã trên sẽ kiểm tra xem có bất kỳ hàng nào được lọc trong Sheet1 hay không Nếu không có hàng nào được lọc, nó sẽ hiển thị hộp thông báo cho biết rằng Và nếu có các hàng được lọc, nó sẽ sao chép các hàng đó, chèn một trang tính mới và dán các hàng này vào trang tính mới được chèn đó Thí dụ. Lọc dữ liệu dựa trên giá trị ôSử dụng Bộ lọc tự động trong VBA cùng với danh sách thả xuống, bạn có thể tạo một chức năng mà ngay sau khi bạn chọn một mục từ danh sách thả xuống, tất cả các bản ghi cho mục đó sẽ được lọc Một cái gì đó như hình dưới đây Bấm vào đây để tải file ví dụ về và làm theo Loại cấu trúc này có thể hữu ích khi bạn muốn nhanh chóng lọc dữ liệu và sau đó sử dụng nó trong công việc của mình Dưới đây là mã mà sẽ làm điều này Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub2 Đây là mã sự kiện trang tính, mã này chỉ được thực thi khi có thay đổi trong trang tính và ô mục tiêu là B2 (nơi chúng tôi có trình đơn thả xuống) Ngoài ra, một điều kiện If Then Else được sử dụng để kiểm tra xem người dùng đã chọn 'Tất cả' từ danh sách thả xuống chưa. Nếu Tất cả được chọn, toàn bộ tập dữ liệu sẽ được hiển thị Mã này KHÔNG được đặt trong một mô-đun Thay vào đó, nó cần được đặt ở phần phụ trợ của trang tính chứa dữ liệu này Dưới đây là các bước để đặt mã này vào cửa sổ mã trang tính
Bây giờ khi bạn sử dụng danh sách thả xuống, nó sẽ tự động lọc dữ liệu Đây là mã sự kiện trang tính, mã này chỉ được thực thi khi có thay đổi trong trang tính và ô mục tiêu là B2 (nơi chúng tôi có trình đơn thả xuống) Ngoài ra, một điều kiện If Then Else được sử dụng để kiểm tra xem người dùng đã chọn 'Tất cả' từ danh sách thả xuống chưa. Nếu Tất cả được chọn, toàn bộ tập dữ liệu sẽ được hiển thị BẬT/TẮT Bộ lọc Tự động Excel bằng VBAKhi áp dụng Bộ lọc tự động cho một dải ô, có thể đã có sẵn một số bộ lọc Bạn có thể sử dụng mã bên dưới để tắt mọi bộ lọc tự động được áp dụng trước Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub3 Mã này kiểm tra toàn bộ trang tính và xóa mọi bộ lọc đã được áp dụng Nếu bạn không muốn tắt bộ lọc từ toàn bộ trang tính mà chỉ tắt từ một tập dữ liệu cụ thể, hãy sử dụng mã bên dưới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub4 Đoạn mã trên kiểm tra xem đã có sẵn bộ lọc hay chưa Nếu các bộ lọc đã được áp dụng, nó sẽ xóa nó, nếu không thì không có gì Tương tự, nếu bạn muốn bật AutoFilter, hãy sử dụng mã bên dưới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub5 Kiểm tra xem AutoFilter đã được áp dụng chưaNếu bạn có một trang tính có nhiều bộ dữ liệu và bạn muốn đảm bảo rằng bạn biết rằng không có bộ lọc nào sẵn có, bạn có thể sử dụng mã bên dưới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub6 Mã này sử dụng chức năng hộp thông báo hiển thị thông báo 'Đã có bộ lọc' khi tìm thấy bộ lọc trên trang tính, nếu không, nó hiển thị 'Không có bộ lọc' Hiển thị tất cả dữ liệuNếu bạn đã áp dụng các bộ lọc cho tập dữ liệu và bạn muốn hiển thị tất cả dữ liệu, hãy sử dụng mã bên dưới Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub7 Đoạn mã trên kiểm tra xem FilterMode là TRUE hay FALSE Nếu đó là sự thật, điều đó có nghĩa là một bộ lọc đã được áp dụng và nó sử dụng phương thức ShowAllData để hiển thị tất cả dữ liệu Lưu ý rằng điều này không loại bỏ các bộ lọc. Các biểu tượng bộ lọc vẫn có sẵn để sử dụng Sử dụng Bộ lọc Tự động trên Trang tính được Bảo vệTheo mặc định, khi bạn bảo vệ một trang tính, các bộ lọc sẽ không hoạt động Trong trường hợp bạn đã có sẵn các bộ lọc, bạn có thể bật AutoFilter để đảm bảo rằng nó hoạt động ngay cả trên các trang tính được bảo vệ Để thực hiện việc này, hãy chọn tùy chọn Sử dụng bộ lọc tự động trong khi bảo vệ trang tính Mặc dù điều này hoạt động khi bạn đã có sẵn bộ lọc, nhưng trong trường hợp bạn cố gắng thêm Bộ lọc tự động bằng mã VBA, nó sẽ không hoạt động Vì trang tính được bảo vệ nên nó sẽ không cho phép bất kỳ macro nào chạy và thực hiện các thay đổi đối với Bộ lọc tự động Vì vậy, bạn cần sử dụng mã để bảo vệ trang tính và đảm bảo bộ lọc tự động được bật trong đó Điều này có thể hữu ích khi bạn đã tạo một bộ lọc động (thứ mà tôi đã trình bày trong ví dụ – ‘Lọc dữ liệu dựa trên giá trị ô’) Dưới đây là mã sẽ bảo vệ trang tính nhưng đồng thời cho phép bạn sử dụng Bộ lọc cũng như macro VBA trong đó Sub FilterRows() Worksheets("Filter Data").Range("A1").AutoFilter End Sub8 Mã này cần được đặt trong cửa sổ mã ThisWorkbook Dưới đây là các bước để đặt mã trong cửa sổ mã ThisWorkbook
Ngay khi bạn mở sổ làm việc và bật macro, nó sẽ tự động chạy macro và bảo vệ Trang tính 1 Tuy nhiên, trước khi làm điều đó, nó sẽ chỉ định 'EnableAutoFilter = True', có nghĩa là các bộ lọc cũng sẽ hoạt động trong trang tính được bảo vệ Ngoài ra, nó đặt đối số 'UserInterfaceOnly' thành 'True'. Điều này có nghĩa là trong khi trang tính được bảo vệ, mã macro VBA sẽ tiếp tục hoạt động Bạn cũng có thể thích các Hướng dẫn VBA sau
SÁCH EXCEL MIỄN PHÍ Nhận Ebook 51 mẹo Excel để tăng năng suất và hoàn thành công việc nhanh hơn
Tên CÓ - GỬI SÁCH ĐIỆN TỬ CHO TÔI 20 suy nghĩ về “Bộ lọc tự động Excel VBA. Hướng dẫn đầy đủ với các ví dụ”
|