programming excel

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

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 Sub

8. 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ạo
newBook.Close SaveChanges:=False

9. 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[]
Làm mờ WS dưới dạng trang tính
Cho Mỗi WS Trong ThisWorkbook. bảng tính
WS. Kích hoạt
Phạm vi ["A" & Hàng. Đếm]. Kết thúc[xlUp]. Lựa chọn
cuối cùng = Lựa chọn. Chèo thuyền
totRow = cuối cùng + 1
WS. Phạm vi ["A" & totRow] = "Tổng cộng"
WS. Phạm vi["B" & totRow] = Ứng dụng. Bảng TínhChức Năng. Tổng[Cột["B. B"]]
WS tiếp theo
kết thúc phụ

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 động
Tả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
Chia sẻ Chia sẻ Tweet Đăng ký

Cú pháp bộ lọc tự động VBA của Excel

Expression. AutoFilter[ _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ ]

Sự diễn đạt. Đây là phạm vi mà bạn muốn áp dụng bộ lọc tự động
Đồng ruộng. [Đối số tùy chọn] Đây là số cột mà bạn muốn lọc. Điều này được tính từ bên trái trong tập dữ liệu. Vì vậy, nếu bạn muốn lọc dữ liệu dựa trên cột thứ hai, giá trị này sẽ là 2
tiêu chí1. [Đối số tùy chọn] Đây là tiêu chí dựa vào đó bạn muốn lọc tập dữ liệu
Nhà điều hành. [Đối số tùy chọn] Trong trường hợp bạn cũng đang sử dụng tiêu chí 2, bạn có thể kết hợp hai tiêu chí này dựa trên Toán tử. Các toán tử sau đây có sẵn để sử dụng. xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
tiêu chí2. [Đối số tùy chọn] Đây là tiêu chí thứ hai mà bạn có thể lọc tập dữ liệu
Hiển ThịThả Xuống. [Đối số tùy chọn] Bạn có thể chỉ định xem bạn có muốn biểu tượng thả xuống bộ lọc xuất hiện trong các cột được lọc hay không. Đối số này có thể là TRUE hoặc FALSE

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ản

Giả 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ột

Giả 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:="

Cú pháp bộ lọc tự động VBA của Excel

Thí dụ. Lọc dữ liệu dựa trên điều kiện Văn bản

Thí dụ. Nhiều Tiêu chí [VÀ/HOẶC] trong cùng một Cột

Bài Viết Liên Quan

Toplist mới

Bài mới nhất

Chủ Đề