Lọc ẩn dòng trống tự động bằng Autofilter + VBA (1 người xem)

[Duong gia]

Lọc ẩn dòng trống tự động bằng Autofilter + VBA với code : Selection.AutoFilter Field:=1, Criteria1:="1" là OK, còn khi muốn bung hết ra thì làm sao mong các anh chị giúp đỡ. (Xin mở file đính kèm)

 

[salam]

Bạ xem file đính kèm nhé.

 

[hocthemexcel]

làm thế nào để tạo biểu tượng ô nổi trên nền file excel, ví dụ ô "lọc", "không lọc" trong fileAutofilter + VBA.xls

 

[cadafi]

làm thế nào để tạo biểu tượng ô nổi trên nền file excel, ví dụ ô "lọc", "không lọc" trong fileAutofilter + VBA.xls

Đây là hai nút commandbutton được tạo từ thanh Control Toolbox (click chuột phải lên thanh menu -->chọn Control Toolbox).

 

[boong]

Sau khi tạo xong biểu tượng. Bạn nhấp đúp chuột lên nó. Rồi chép đoạn code này vào.

Private Sub CommandButton1_Click() If CommandButton1.Caption = "Loc" Then     CommandButton1.Caption = "Khong Loc" Else CommandButton1.Caption = "Loc" End If End Sub

Sau khi chép xong, bạn nhấp vào cây thước êke để tắt nó đi. Và thử nhấp chuột lên nút lệnh xem! Thân.

 

[ndu96081631]

Với bài toán trên sao ta không tận dụng cột STT làm điều kiện lọc nhỉ? Cần gì AutoFilter? Cần gì cột phụ L?
Tôi sẽ làm như sau:
1> Đặt công thức tại A10:

A10 =IF(SUM(D10:I10)>0,MAX($A$9:A9)+1,"")

Kéo fill công thức đến A28
2> Tạo 1 code duy nhất để Hide và Unhide

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  Range("A10:A28").SpecialCells(3, 22).EntireRow.Hidden = DK
  Cmd.Caption = Choose(-1 * DK + 1, "Loc", "Khong Loc")
End Sub

Xem file

 

[phuongc5]

Với bài toán trên sao ta không tận dụng cột STT làm điều kiện lọc nhỉ? Cần gì AutoFilter? Cần gì cột phụ L?
Tôi sẽ làm như sau:
1> Đặt công thức tại A10:
Kéo fill công thức đến A28
2> Tạo 1 code duy nhất để Hide và Unhide

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  Range("A10:A28").SpecialCells(3, 22).EntireRow.Hidden = DK
  Cmd.Caption = Choose(-1 * DK + 1, "Loc", "Khong Loc")
End Sub

Xem file

Em chưa hiểu tham số của SpecialCells(3, 22). Anh có thể giải thích giúp em được không ạ? Em cảm ơn!

 

[hoangdanh282vn]

Em chưa hiểu tham số của SpecialCells(3, 22). Anh có thể giải thích giúp em được không ạ? Em cảm ơn!

expression.SpecialCells(Type, Value)

Type : XlCellType can be one of these XlCellType constants :

xlCellTypeAllFormatConditions. Cells of any format 
xlCellTypeAllValidation. Cells having validation criteria 
xlCellTypeBlanks. Empty cells 
xlCellTypeComments. Cells containing notes 
xlCellTypeConstants. Cells containing constants 
xlCellTypeFormulas. Cells containing formulas 
xlCellTypeLastCell. The last cell in the used range 
xlCellTypeSameFormatConditions. Cells having the same format 
xlCellTypeSameValidation. Cells having the same validation criteria 
xlCellTypeVisible. All visible cells

Value : XlSpecialCellsValue

xlErrors :16
xlLogical : 4
xlNumbers : 1
xlTextValues : 2

Các con số ứng với các giá trị nêu trên. Nếu số lớn hơn thì nó chính là tổng của các giá trị đó. Tức là vừa có giá trị này, vừa gồm cả giá trị khác nữa. Nếu mình làm thì mình sẽ ghi rõ các giá trị ra thay vì ghi số, để người khác dễ hiểu và ngay cả mình nhiều khi còn không biết nó là cái gì. Khi nó thì nhờ chức năng Help và Record lấy.

bạn xem thêm trong help nha.

 

[hoquanghoa12c3]

nhờ diễn đàn giúp đỡ

Chân thành cảm ơn bác ndu96081631 em đã download file của bàc về để học hỏi. nhưng sao cái Excel trên máy của em báo lổi (em gửi kèm thông báo lỗi), mong bác và diễn đàn Excel chỉ giúp.

 

[Hoàng Trọng Nghĩa]

Chân thành cảm ơn bác ndu96081631 em đã download file của bàc về để học hỏi. nhưng sao cái Excel trên máy của em báo lổi (em gửi kèm thông báo lỗi), mong bác và diễn đàn Excel chỉ giúp.

Khi tải file về, bạn phải chọn là Save (nếu chọn Open đôi khi Macro không chạy). File có chứa Macro, bạn muốn chạy File thì phải chọn Macro Securities ở chế độ Low (Tool > Macro > Macro Securities > Low).

Thân ái.

 

[Hoàng Trọng Nghĩa]

Với bài toán trên sao ta không tận dụng cột STT làm điều kiện lọc nhỉ? Cần gì AutoFilter? Cần gì cột phụ L?
Tôi sẽ làm như sau:
1> Đặt công thức tại A10:
Kéo fill công thức đến A28
2> Tạo 1 code duy nhất để Hide và Unhide

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  Range("A10:A28").SpecialCells(3, 22).EntireRow.Hidden = DK
  Cmd.Caption = Choose(-1 * DK + 1, "Loc", "Khong Loc")
End Sub

Xem file

Đúng là mỗi Thầy có mỗi kiểu hay riêng biệt. Nếu như cách của Thầy NDU thay vì EntireRow.Hidden thì chọn AutoFilter thì muốn hide hay unhide cũng không được, vã lại, muốn định dạng hoặc kéo cho row rộng một tí thì những row bị ẩn cũng không vì thế mà thay đổi.
(Em mượn File của Thầy chút nhé! Cám ơn Thầy).

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$8:$A$28")
    If DK Then .AutoFilter Field:=1, Criteria1:="<>" Else .AutoFilter
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

(đã bỏ thuộc tính Print Object cho nút lệnh "Lọc")

 

[spring2402]

Chao cac anh chi va cac ban,

Hien tai, minh fai lam file time sheet cho 100 nhan vien va co the nhieu hon nua. Moi nhan vien se fai co 10 dong de tong hop thong tin cham cong cac the loai. Neu lam thu cong chen dong thi se rat lau. Cac anh chi va cac ban co cach nao giup tu dong chen 10 dong them vao moi hang trong danh sach 100 nhan vien do khong a?

Cam on cac anh chi.

 

[ndu96081631]

Đúng là mỗi Thầy có mỗi kiểu hay riêng biệt. Nếu như cách của Thầy NDU thay vì EntireRow.Hidden thì chọn AutoFilter thì muốn hide hay unhide cũng không được, vã lại, muốn định dạng hoặc kéo cho row rộng một tí thì những row bị ẩn cũng không vì thế mà thay đổi.
(Em mượn File của Thầy chút nhé! Cám ơn Thầy).

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$7:$A$28")
    If DK Then .AutoFilter Field:=1, Criteria1:="<>" Else .AutoFilter
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

(đã bỏ thuộc tính Print Object cho nút lệnh "Lọc")

Viết thế này sẽ "đẹp" hơn:

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$8:$A$28")
    If DK Then .AutoFilter 1, "<>", , , False Else .AutoFilter
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

Ẩn luôn mũi tên AutoFilter

 

[Hoàng Trọng Nghĩa]

Chẳng hiểu tại sao, khi dùng AutoFilter, mình đã đã chọn địa chỉ quá rõ ràng rồi (như $A$7:$A$28) mà cứ thấy hàng tiếp theo liền nó là nó cứ Filter! Các Thầy có cách làm cho nó đi đúng quỹ đạo không?

 

[DNam]

Chẳng hiểu tại sao, khi dùng AutoFilter, mình đã đã chọn địa chỉ quá rõ ràng rồi (như $A$7:$A$28) mà cứ thấy hàng tiếp theo liền nó là nó cứ Filter!..

Mình nghĩ khi dùng AutoFilter thì Excel sẽ áp dụng cho cả cột.

Với số lượng dòng ít, có địa chỉ rõ ràng và bên dưới còn dữ liệu không cần lọc thì theo mình nên dùng vòng lặp

Sub Cmd_Click()
   DK = Cmd.Caption
   For i = 1 To 22
      If [A6].Offset(i, ).Value = 0 And DK = "Loc" Then [A6].Offset(i, ).EntireRow.Hidden = True
      If DK = "Khong Loc" Then [A6].Offset(i, ).EntireRow.Hidden = False
   Next i
   Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

 

[Hoàng Trọng Nghĩa]

Mình nghĩ khi dùng AutoFilter thì Excel sẽ áp dụng cho cả cột.

Với số lượng dòng ít, có địa chỉ rõ ràng và bên dưới còn dữ liệu không cần lọc thì theo mình nên dùng vòng lặp

Sub Cmd_Click()
   DK = Cmd.Caption
   For i = 1 To 22
      If [A6].Offset(i, ).Value = 0 And DK = "Loc" Then [A6].Offset(i, ).EntireRow.Hidden = True
      If DK = "Khong Loc" Then [A6].Offset(i, ).EntireRow.Hidden = False
   Next i
   Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

Cám ơn bạn, cái mình cần là AutoFilter, chứ không phải EntireRow.Hidden.
Bởi vì nếu dùng EntireRow.Hidden thì Thầy NDU đã làm rất sáng tỏ rồi không cần vòng lặp:

Sub Cmd2_Click()
  DK = Cmd.Caption = "Loc"
  Range("A10:A28").SpecialCells(3, 22).EntireRow.Hidden = DK
  Cmd.Caption = Choose(-1 * DK + 1, "Loc", "Khong Loc")
End Sub

À. Mình cũng thử copy công thức của bạn máy lại báo lỗi nhỉ?
AutoFilter không dùng cho cả cột đâu bạn! Hình như nếu có địa chỉ rõ ràng, nó sẽ xác định dòng liền kề có giá trị hay không, nếu có, nó thêm vào, nếu không nó sẽ bỏ qua tất cả những cái bên dưới dòng trống liền kề bên dưới nó.

 

[1ndt.com]

Thầy NDU cho em hỏi, trước khi nhúng VBA code như thầy đã viết thì tạo nút "Lọc" để code đó thực thị được với nút đó, e đã thử insert button nhưng không được, điều kiện của em là ẩn những dòng mà cột đó = 0 hoặc bằng ký tự trắng, cảm ơn thầy !!!

 

[dongducnhiem]

Vừa ẩn dòng vừa ẩn cột

Viết thế này sẽ "đẹp" hơn:

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$8:$A$28")
    If DK Then .AutoFilter 1, "<>", , , False Else .AutoFilter
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

Ẩn luôn mũi tên AutoFilter

Chào anh & các bạn thành viên!
Trong bài này, nếu ta cùng lúc ẩn thêm cột ( mặc dù cột có số liệu, ví dụ cột B) thì sửa code trên như thế nào?
Xin cảm ơn!

 

[Hoàng Trọng Nghĩa]

Chào anh & các bạn thành viên!
Trong bài này, nếu ta cùng lúc ẩn thêm cột ( mặc dù cột có số liệu, ví dụ cột B) thì sửa code trên như thế nào?
Xin cảm ơn!

Thì bạn làm như vầy đi:

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$8:$A$28")
    If DK Then
        .AutoFilter 1, "<>", , , False
        Range("B:B").EntireColumn.Hidden = True
    Else
        .AutoFilter
        Range("B:B").EntireColumn.Hidden = False
    End If
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

 

[dongducnhiem]

Code sử dụng cho ở nhiều Sheet?

Thì bạn làm như vầy đi:

Sub Cmd_Click()
  DK = Cmd.Caption = "Loc"
  With Range("$A$8:$A$28")
    If DK Then
        .AutoFilter 1, "<>", , , False
        Range("B:B").EntireColumn.Hidden = True
    Else
        .AutoFilter
        Range("B:B").EntireColumn.Hidden = False
    End If
  End With
  Cmd.Caption = IIf(DK, "Khong Loc", "Loc")
End Sub

Cảm ơn bạn minhthien321 đã giúp đỡ
Tôi có thêm 01 vấn đề như thế này, trong 01 File có nhiều Sheet trong đó có 12 sheet là có cấu trúc tương đối đồng dạng, nếu áp dụng bài trên thì ta phải Copy code trên vào 12 Sheet và đồng thời tạo ra 12 nút "Cmd".
Bây giờ tôi muốn gom thành một đọan code để ở đâu đó và ta chỉ tạo 12 nút lệnh ở 12 sheet có được không bạn?
Xin cảm ơn