Xin giúp đỡ về AutoFilter bằng VBA (1 người xem)

[MinhKhai]

Sử dụng AutoFilter thông qua menu của Excel, theo đó các dòng không được lọc sẽ bị ẩn dòng thì em đã biết.
Em cũng đã xem một vài file mà em tải trên diễn đàn trong đó có lọc bằng VBA nhưng em vẫn chưa hiểu và không áp dụng được.
Nay em gửi file, mong các ACE trong diễn đàn giúp tạo code lọc dữ liệu giúp em.
(Trong code có giải thích ý nghĩa, tác dụng của lệnh thì tuyệt vời)
Em chân thành cảm ơn.

 

[Hong.Van]

Sử dụng AutoFilter thông qua menu của Excel, theo đó các dòng không được lọc sẽ bị ẩn dòng thì em đã biết.
Em cũng đã xem một vài file mà em tải trên diễn đàn trong đó có lọc bằng VBA nhưng em vẫn chưa hiểu và không áp dụng được.
Nay em gửi file, mong các ACE trong diễn đàn giúp tạo code lọc dữ liệu giúp em.
(Trong code có giải thích ý nghĩa, tác dụng của lệnh thì tuyệt vời)
Em chân thành cảm ơn.

Bạn thử xem 02 đề tài này xem, lúc này tôi bận quá nên kg có thời gian giúp được!
http://www.giaiphapexcel.com/forum/...-mọi-người-làm-giúp-phần-tìm-kiếm-trong-excel
http://www.giaiphapexcel.com/forum/showthread.php?23166-Giải-thích-giúp-về-code-lọc-dữ-liệu/page3

 

[Quang_Hải]

Sử dụng AutoFilter thông qua menu của Excel, theo đó các dòng không được lọc sẽ bị ẩn dòng thì em đã biết.
Em cũng đã xem một vài file mà em tải trên diễn đàn trong đó có lọc bằng VBA nhưng em vẫn chưa hiểu và không áp dụng được.
Nay em gửi file, mong các ACE trong diễn đàn giúp tạo code lọc dữ liệu giúp em.
(Trong code có giải thích ý nghĩa, tác dụng của lệnh thì tuyệt vời)
Em chân thành cảm ơn.

Bố trí dữ liệu lại tí cho code đơn giản

 

[Hai Lúa Miền Tây]

Làm cho bạn = ado

Code trong sheet Filter1

[GPECODE=sql]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select f2,f3,f4,f5,f6,f7,f8,f9,f1 from [Data$A5:I1000] " _
& "where f3 is not null and f5 like '" & Sheet2.Range("D2").Value & "'"
End With
With Sheets("Filter1")
.[B6:J65000].ClearContents
.[B6].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End If

End Sub

[/GPECODE]

Code trong sheet Filter2

[GPECODE=sql]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, [D24]) Is Nothing Then
Set adoConn = CreateObject("ADODB.Connection")
Set adoRS = CreateObject("ADODB.Recordset")
With adoConn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
.Open
End With
With adoRS
.ActiveConnection = adoConn
.Open "select f2,f3,f4,f5,f6,f7,f8,f9 from [Data$A5:I1000] " _
& "where f3 is not null and f5 like '" & Sheet3.Range("D2").Value & "' and f7 like '" _
& Sheet3.Range("D3").Value & "' and f9 like '" & Sheet3.Range("D4").Value & "'"
End With
With Sheets("Filter2")
.[B7:J65000].ClearContents
.[B7].CopyFromRecordset adoRS
End With
adoRS.Close: Set adoRS = Nothing
adoConn.Close: Set adoConn = Nothing
End If
End Sub

[/GPECODE]

 

[ndu96081631]

Sử dụng AutoFilter thông qua menu của Excel, theo đó các dòng không được lọc sẽ bị ẩn dòng thì em đã biết.
Em cũng đã xem một vài file mà em tải trên diễn đàn trong đó có lọc bằng VBA nhưng em vẫn chưa hiểu và không áp dụng được.
Nay em gửi file, mong các ACE trong diễn đàn giúp tạo code lọc dữ liệu giúp em.
(Trong code có giải thích ý nghĩa, tác dụng của lệnh thì tuyệt vời)
Em chân thành cảm ơn.

Nếu lọc tại chổ, ta dùng AutoFilter là hợp lý. Ngược lại, nếu lọc từ nơi này rồi mang kết quả sang nơi khác, ta nên dùng Advanced Filter sẽ hay hơn vì không phải mất công đoạn copy sau khi lọc. Mặc khác, Advanced Filter cho phép bạn đảo thứ tự của dòng tiêu đề thoài mái
Vậy xem ra code mà quanghai1969 đưa ở trên là nhanh gọn nhất
Bạn có thể nghiên cứu nó bằng cách record macro quá trình lọc bằng tay, xem code rồi tự chỉnh lại cho phù hợp

 

[MinhKhai]

Bố trí dữ liệu lại tí cho code đơn giản

Thật tuyệt! Sao giải pháp đơn giản vả ngắn gọn vậy. Mấy phương pháp em tham khảo nó dài dòng quá nên em chả hiểu.
Xin giúp thêm: Giả sử kết quả lọc ở sheet Filter1 hoặc Filter2 mà có cấu trúc cột (thứ tự cột) không giống sheet nguồn thì điều chỉnh code thế nào ạ?

 

[ndu96081631]

Thật tuyệt! Sao giải pháp đơn giản vả ngắn gọn vậy. Mấy phương pháp em tham khảo nó dài dòng quá nên em chả hiểu.
Xin giúp thêm: Giả sử kết quả lọc ở sheet Filter1 hoặc Filter2 mà có cấu trúc cột (thứ tự cột) không giống sheet nguồn thì điều chỉnh code thế nào ạ?

Tôi đã nói ở trên rồi mà

Mặc khác, Advanced Filter cho phép bạn đảo thứ tự của dòng tiêu đề thoải mái

Vùng đặt kết quả, dù bạn có đảo vị trí cột thì kết quả vẫn chính xác ---> Đây là cái hay của Advanced Filter
Không tin bạn có thể thí nghiệm sẽ biết liền

 

[Quang_Hải]

Thật tuyệt! Sao giải pháp đơn giản vả ngắn gọn vậy. Mấy phương pháp em tham khảo nó dài dòng quá nên em chả hiểu.
Xin giúp thêm: Giả sử kết quả lọc ở sheet Filter1 hoặc Filter2 mà có cấu trúc cột (thứ tự cột) không giống sheet nguồn thì điều chỉnh code thế nào ạ?

Bạn lưu ý ô đầu tiên và ô cuối của dòng tiêu đề là được rồi. Cứ edit thoải mái cho đến khi la lên " Ah hiểu rồi". Mình cũng học kiểu đó thôi. Sửa chỗ [B5:J5] đó xem sao.

Sub loc1()
With Sheets("Data")
   .Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:D2], [B5:j5]
End With
End Sub

Sub loc2()
With Sheets("Data")
   .Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:F2], [B5:i5]
End With
End Sub

 

[MinhKhai]

Bạn lưu ý ô đầu tiên và ô cuối của dòng tiêu đề là được rồi. Cứ edit thoải mái cho đến khi la lên " Ah hiểu rồi". Mình cũng học kiểu đó thôi. Sửa chỗ [B5:J5] đó xem sao.

Ah, em hiểu rồi. Thằng Advanded Filter thật thông minh.
Cảm ơn các bác đã giúp đỡ.

 

[MinhKhai]

Bố trí dữ liệu lại tí cho code đơn giản

Cho em hỏi, ở 2 ô tiêu chí lọc, được chuyển từ ngang sang dọc, vậy nếu cứ sắp xếp theo chiều ngang thì Code có chạy được không ?
Cụ thể: code đang sử dụng là

.Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:D2], [B5:J5]

chuyển thành

.Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:E1], [B5:J5]

Em thử sửa nhưng không thấy hoạt động

 

[ndu96081631]

Cho em hỏi, ở 2 ô tiêu chí lọc, được chuyển từ ngang sang dọc, vậy nếu cứ sắp xếp theo chiều ngang thì Code có chạy được không ?
Cụ thể: code đang sử dụng là

.Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:D2], [B5:J5]

chuyển thành

.Range(.[A4], .[I65536]).AdvancedFilter 2, [D1:E1], [B5:J5]

Em thử sửa nhưng không thấy hoạt động

Câu trả lời: NẾU BẠN LÀM BẰNG TAY ĐƯỢC THÌ SẼ VIẾT CODE ĐƯỢC
Vậy bạn có thể thí nghiệm bằng cách bố trí lại vùng điều kiện rồi lọc bằng tay xem nó có ra kết quả không là biết liền

 

[MinhKhai]

Câu trả lời: NẾU BẠN LÀM BẰNG TAY ĐƯỢC THÌ SẼ VIẾT CODE ĐƯỢC
Vậy bạn có thể thí nghiệm bằng cách bố trí lại vùng điều kiện rồi lọc bằng tay xem nó có ra kết quả không là biết liền

Em chưa hiểu ý bác.
Trước khi hỏi trên diễn đàn em đã tí toáy nhưng không thành công.
Cho em hỏi tiếp câu hỏi khác: Khi sử dụng AutoFilter, ở dòng điều kiện lọc xuất hiện Dropdown list. DropdownList là list không trùng của các ô trong cột đó.
Quay lại bài toán ban đầu của em: Hiện tại các điều kiện lọc đang dùng Validation thủ công, vậy có thể cài đặt thế nào để khi lọc điều kiện 1, mà điều kiện 2 nếu lọc cũng không có thì sẽ không xuất hiện trong list điều kiện 2 (Giống dòng điều kiện lọc trong AutoFilter. ?
Xin được giải đáp..

 

[Quang_Hải]

Em chưa hiểu ý bác.
Trước khi hỏi trên diễn đàn em đã tí toáy nhưng không thành công.

Tức là không được đó mà. Vùng điều kiện lọc không thể nằm chung 1 dòng như bạn muốn được. Tính chất của AdvancedFilter là thế mà.
Lọc đơn giản thì dùng advancedfilter, nếu lọc phức tạp thì phải dùng mảng rồi sử dụng vòng lặp để duyệt qua từng dòng.

 

[MinhKhai]

Cho em xin tiếp tục hỏi thêm:
Em tải trên diễn đàn này 1 file mà em thấy mình hay cần dùng, đó là khi sử dụng chức năng AutoFilter, điều kiện nào được lọc sẽ được tô màu nền.
https://dl.dropbox.com/s/cdduirr0tr...2fq4wR5FFL0a2BqDHdmknZJgbjmC4t-HsNhQIHBA&dl=1
Tuy nhiên khi xem cách xử lý của file, em thấy hướng giải quyết có vẻ rối rắm, vòng vèo, vừa dùng code, vừa dùng hàm, vừa dùng ô trung gian...
Vậy có ai có cách xử lý khác không ạ ?

 

[ndu96081631]

Cho em xin tiếp tục hỏi thêm:
Em tải trên diễn đàn này 1 file mà em thấy mình hay cần dùng, đó là khi sử dụng chức năng AutoFilter, điều kiện nào được lọc sẽ được tô màu nền.
https://dl.dropbox.com/s/cdduirr0tr...2fq4wR5FFL0a2BqDHdmknZJgbjmC4t-HsNhQIHBA&dl=1
Tuy nhiên khi xem cách xử lý của file, em thấy hướng giải quyết có vẻ rối rắm, vòng vèo, vừa dùng code, vừa dùng hàm, vừa dùng ô trung gian...
Vậy có ai có cách xử lý khác không ạ ?

Xóa hết mấy module ấy đi rồi copy code dưới đây vào sheet có AutoFilter

Private Sub Worksheet_Calculate()
  Dim i As Long
  If ActiveSheet.AutoFilterMode Then
    With ActiveSheet.AutoFilter
      For i = 1 To .Filters.Count
        .Range(1, i).Interior.ColorIndex = -(.Filters(i).On) * 41
      Next
    End With
  End If
End Sub

----------
Trước khi thí nghiệm, nhớ xóa luôn Conditional Formating nhé

 

[MinhKhai]

Xóa hết mấy module ấy đi rồi copy code dưới đây vào sheet có AutoFilter

Private Sub Worksheet_Calculate()
  Dim i As Long
  If ActiveSheet.AutoFilterMode Then
    With ActiveSheet.AutoFilter
      For i = 1 To .Filters.Count
        .Range(1, i).Interior.ColorIndex = -(.Filters(i).On) * 41
      Next
    End With
  End If
End Sub

----------
Trước khi thí nghiệm, nhớ xóa luôn Conditional Formating nhé

Rất hay và đơn giản. Thật là dễ học và làm theo.
(Thế mà cái file gửi nó dùng phương pháp rối rắm thế nhỉ? )
Cảm ơn bác

 

[ndu96081631]

(Thế mà cái file gửi nó dùng phương pháp rối rắm thế nhỉ? )
Cảm ơn bác

File bạn đưa lên tôi cũng từng tải về đã lâu. Tôi cũng thấy nó rối rắm nên sửa lại (sửa cũng lâu rồi, giờ chỉ post lên thôi)
Tất cả các kiến thức đó toàn học tại GPE này. Bởi vậy mới nói: GPE mình chưa chắc thua các chuyên trang Excel của nước ngoài đâu...
------------------------
Tôi đố bạn cái này:
- Giờ hãy xóa hết các công thức trong bảng tính (có thể copy/paste value)
- Xong, Filter xem code chạy không?
- Nếu code không chạy, đố bạn biết tại sao?
Ẹc.... Ẹc...

 

[hungpecc1]

File bạn đưa lên tôi cũng từng tải về đã lâu. Tôi cũng thấy nó rối rắm nên sửa lại (sửa cũng lâu rồi, giờ chỉ post lên thôi)
Tất cả các kiến thức đó toàn học tại GPE này. Bởi vậy mới nói: GPE mình chưa chắc thua các chuyên trang Excel của nước ngoài đâu...
------------------------
Tôi đố bạn cái này:
- Giờ hãy xóa hết các công thức trong bảng tính (có thể copy/paste value)
- Xong, Filter xem code chạy không?
- Nếu code không chạy, đố bạn biết tại sao?
Ẹc.... Ẹc...

Em thử " Loi choi "trả lời xem đúng không anh nhé !
Sự kiện _CaCulate chỉ xảy ra khi có 1 phép tính nào được thực hiện trên sheet ,do đó :

-------> xóa hết công thức ---> không có caculate nào trên sheet --> code không chạy

vì filter không được coi là 1 caculate

->> để khắc phục điều trên em nghĩ gõ lệnh tại ô [IV65536] = today() ( nhưng mà trường hợp người dùng để formulas là manul thì --> pó tay chăng ?)

 

[MinhKhai]

Em thử " Loi choi "trả lời xem đúng không anh nhé !
Sự kiện _CaCulate chỉ xảy ra khi có 1 phép tính nào được thực hiện trên sheet ,do đó :

-------> xóa hết công thức ---> không có caculate nào trên sheet --> code không chạy

vì filter không được coi là 1 caculate

->> để khắc phục điều trên em nghĩ gõ lệnh tại ô [IV65536] = today() ( nhưng mà trường hợp người dùng để formulas là manul thì --> pó tay chăng ?)

Rất thuyết phục. Giả thuyết tương đương đáp án.

 

[MinhKhai]

File bạn đưa lên tôi cũng từng tải về đã lâu. Tôi cũng thấy nó rối rắm nên sửa lại (sửa cũng lâu rồi, giờ chỉ post lên thôi)
Tất cả các kiến thức đó toàn học tại GPE này. Bởi vậy mới nói: GPE mình chưa chắc thua các chuyên trang Excel của nước ngoài đâu...

Bác cho em hỏi tiếp với: Em có file hàng ngày cần hay phải lọc dạng AutoFilter. Tuy nhiên việc xác định được ô nào được lọc, và giá trị nào được lọc em phải làm vòng vèo (nghĩ mãi mới ra). Vì cần lấy giá trị được lọc đó đưa lên trên để sử dụng.
Trong file gửi kèm đó là 2 ô tô nền đỏ. Bác xem có thể gợi ý cho em cách nào đó ít vòng vèm không ?

https://dl.dropbox.com/s/ptbfpgda7l...a4Q_ZN1IwVsJo7IFzlsUiUhOWxDQ_zS8M2U4deXA&dl=1