Code thay thế chức năng filter (1 người xem)

ruamap9 · 6/11/13

Chào các anh chị trên diễn đàn!
Em đáng có một vấn đề vướng mắc cần các anh chị trợ giúp. Chi tiết có trong file đính kèm
Cảm ơn các anh chị!

Hai Lúa Miền Tây · 6/11/13

ruamap9 đã viết:
Chào các anh chị trên diễn đàn!
Em đáng có một vấn đề vướng mắc cần các anh chị trợ giúp. Chi tiết có trong file đính kèm
Cảm ơn các anh chị!

Record macro lại động tác sau:
1./ Lọc duy nhất cột họ tên, đưa ra cột I, vậy là ta có kết quả duy nhất của tên.
2./ Dùng hàm Vlookup để điền Quê vào cột J, Copy và Paste Value cột J.

Let'GâuGâu · 6/11/13

ruamap9 đã viết:
Chào các anh chị trên diễn đàn!
Em đáng có một vấn đề vướng mắc cần các anh chị trợ giúp. Chi tiết có trong file đính kèm
Cảm ơn các anh chị!

xem thử

Sub Macro1()

Dim found As Range
[I10:J1000].ClearContents
[D9

26].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[I9], Unique:=True
For i = 10 To [I1000].End(3).Row
Set found = [D9

1000].Find(Range("I" & i), , , , , 1)
If Not found Is Nothing Then
Range("j" & i) = found.Offset(, 1)
End If
Next

End Sub

Quang_Hải · 6/11/13

ruamap9 đã viết:
Chào các anh chị trên diễn đàn!
Em đáng có một vấn đề vướng mắc cần các anh chị trợ giúp. Chi tiết có trong file đính kèm
Cảm ơn các anh chị!

Xem thêm

PHP:

Sub Loc()
Dim I As Long, J As Long, Cot As Long, X As Long
Dim Sarr(), Darr(), Dk As String
Cot = 2
Sarr = Range([D10], [D65536].End(3)).Resize(, Cot).Value
ReDim Darr(1 To UBound(Sarr), 1 To Cot)
With CreateObject("Scripting.Dictionary")
    For I = 1 To UBound(Sarr)
        Dk = Sarr(I, 1) & Sarr(I, Cot)
        If Not .exists(Dk) Then
        J = J + 1
        .Add Dk, J
        For X = 1 To Cot
            Darr(J, X) = Sarr(I, X)
        Next
      End If
    Next
End With
[I10].Resize(J, Cot) = Darr
End Sub

leonguyenz · 6/11/13

Rercod Marco chức năng Remove Duplicates là gọn nhẹ mà:
[gpecode=vb]
Sub RemoveDup()
Range("$D$9:$E$26").RemoveDuplicates 1, xlYes
End Sub
[/gpecode]

Hai Lúa Miền Tây · 6/11/13

leonguyenz đã viết:
Rercod Marco chức năng Remove Duplicates là gọn nhẹ mà:
[gpecode=vb]
Sub RemoveDup()
Range("$D$9:$E$26").RemoveDuplicates 1, xlYes
End Sub
[/gpecode]

Lưu ý chỉ có ở Excel 2007 trở lên thôi Thảo à.

ndu96081631 · 6/11/13

Let'GâuGâu đã viết:
xem thử

Sub Macro1()

Dim found As Range
[I10:J1000].ClearContents
[D926].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[I9], Unique:=True
For i = 10 To [I1000].End(3).Row
Set found = [D91000].Find(Range("I" & i), , , , , 1)
If Not found Is Nothing Then
Range("j" & i) = found.Offset(, 1)
End If
Next

End Sub

Không cần For... Next cũng được mà:

Mã:

Sub Macro2()
  Range("I9:J1000").ClearContents
  With Range("D9:D1000")
    .AdvancedFilter [COLOR=#ff0000]1[/COLOR], , , True
    [COLOR=#ff0000].Resize(, 2).SpecialCells(12)[/COLOR].Copy Range("I9")
    [COLOR=#ff0000].Parent.ShowAllData[/COLOR]
  End With
End Sub

(nghiên cứu những chổ màu đỏ)

leonguyenz · 6/11/13

Hai Lúa Miền Tây đã viết:
Lưu ý chỉ có ở Excel 2007 trở lên thôi Thảo à.

Vậy dùng code này thử xem, theo cách anh nói, thay Vlookup bằng Find:
[gpecode=vb]
Sub Loc()
Dim iR As Long, Rng As Range
Set Rng = Range("D9

26")
Range("I9:J" & Rng.Rows.Count).Clear
Rng.AdvancedFilter 2, , Range("I9"), True
Range("J9") = Range("E9").Value
For iR = 1 To Range("I10:I" & Range("I65535").End(xlUp).Row).Rows.Count
Cells(iR + 9, "J") = Rng.Find(Cells(iR + 9, "I"), , , 1).Offset(, 1)
Next iR
End Sub

[/gpecode]

leonguyenz · 6/11/13

ndu96081631 đã viết:
Không cần For... Next cũng được mà:

Mã:

Sub Macro2() Range("I9:J1000").ClearContents With Range("D9:D1000") .AdvancedFilter [COLOR=#ff0000]1[/COLOR], , , True [COLOR=#ff0000].Resize(, 2).SpecialCells(12)[/COLOR].Copy Range("I9") [COLOR=#ff0000].Parent.ShowAllData[/COLOR] End With End Sub

(nghiên cứu những chổ màu đỏ)

Nhờ Thầy diễn giải dùm:
- SpecialCells(12), ý nghĩa số 12 và Parent.ShowAllData (trong VBA xem được 10 đối số (hay tham số gì đó) của SpecialCells)
- Dùng code này để dò tìm giống như Vlookup hoặc Find được không?
Cảm ơn!

leonguyenz · 6/11/13

Em rõ cái SpecialCells rồi, 12 là giá trị của xlCellTypeVisible.
Còn ShowAllData tại sao trong Help chỉ nói đến Ver 2007? 2003 có dùng được không Thầy?
Nhờ Thầy diễn giải dùm .Parent.ShowAllData.

ndu96081631 · 6/11/13

leonguyenz đã viết:
Nhờ Thầy diễn giải dùm:
- SpecialCells(12), ý nghĩa số 12 và Parent.ShowAllData (trong VBA xem được 10 đối số (hay tham số gì đó) của SpecialCells)
- Dùng code này để dò tìm giống như Vlookup hoặc Find được không?
Cảm ơn!

Bài này nếu làm bằng tay thì ta sẽ làm như sau:
- Quét chọn cột D (1 cột duy nhất) và dùng Advanced Filter lọc duy nhất tại chổ (không copy sang nơi khác)
- Xong, bây giờ mới quét chọn 2 cột (D và E), Ctrl + G\Spcial\Visible Cells only
- Bấm Ctrl + C để copy rồi chọn I9 và Paste
- Cuối cùng, vào mục Sort & Filter, chọn Clear (tương đương với ShowAll Data bên Excel 2003)
===> 2 cái màu đỏ mà Thảo hỏi ở trên tương ứng là 2 cái màu đỏ trả lời bên dưới
Còn cái Parent thì dễ hiểu rồi: Nó là CHA MẸ ---> Range("gi gì đó").Parent chính là Cha Mẹ của Range("gi gì đó"), tức là đang nói đến Sheet chứa Range("gi gì đó") đấy mà
Cái mà các bạn cần đặt câu hỏi ở đây là: Tại sao tôi không viết: Sheet1.ShowAllData mà lại viết .Parent.ShowAllData

leonguyenz · 6/11/13

ndu96081631 đã viết:
Bài này nếu làm bằng tay thì ta sẽ làm như sau:
- Quét chọn cột D (1 cột duy nhất) và dùng Advanced Filter lọc duy nhất tại chổ (không copy sang nơi khác)
- Xong, bây giờ mới quét chọn 2 cột (D và E), Ctrl + G\Spcial\Visible Cells only
- Bấm Ctrl + C để copy rồi chọn I9 và Paste
- Cuối cùng, vào mục Sort & Filter, chọn Clear (tương đương với ShowAll Data bên Excel 2003)
===> 2 cái màu đỏ mà Thảo hỏi ở trên tương ứng là 2 cái màu đỏ trả lời bên dưới
Còn cái Parent thì dễ hiểu rồi: Nó là CHA MẸ ---> Range("gi gì đó").Parent chính là Cha Mẹ của Range("gi gì đó"), tức là đang nói đến Sheet chứa Range("gi gì đó") đấy mà
Cái mà các bạn cần đặt câu hỏi ở đây là: Tại sao tôi không viết: Sheet1.ShowAllData mà lại viết .Parent.ShowAllData

Thầy diễn giải vậy rất dễ hiểu.
Khi Record Marco (đã xén bớt):
[gpecode=vb]
Sub Macro4()
Range("D9

26").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Range("D9:E26").SpecialCells(xlCellTypeVisible).Copy Range("I9")
ActiveSheet.ShowAllData
End Sub
[/gpecode]
Đúng như Thầy nói định hỏi tại sao lại dùng .Parent, mà như Thầy giải thích thì em hiểu nó bao hàm đối tượng trên Sheet đã chọn.
Híc, đang muốn học hỏi thêm, nhưng giờ em bận họp mất rồi.
Cảm ơn Thầy!

Let'GâuGâu · 6/11/13

ndu96081631 đã viết:
Tại sao tôi không viết: Sheet1.ShowAllData mà lại viết .Parent.ShowAllData

theo tôi đoán là vậy: nếu sheet1.showalldata thì trên sheet đó có chổ nào có filter thì nó gởi bỏ hết
còn .Parent thì nó chỉ gởi bỏ filter ở vùng gốc thôi

để kiểm tra thử xem đúng không?hihhihihi

hỏng đúng, nó quốc tuốt luốt hết.ihichic.

mhung12005 · 6/11/13

Let'GâuGâu đã viết:
theo tôi đoán là vậy: nếu sheet1.showalldata thì trên sheet đó có chổ nào có filter thì nó gởi bỏ hết
còn .Parent thì nó chỉ gởi bỏ filter ở vùng gốc thôi

để kiểm tra thử xem đúng không?hihhihihi

hỏng đúng, nó quốc tuốt luốt hết.ihichic.

Theo mình thì không phải vậy đâu bạn ơi. Vì:

ShowAllData = Clear (nghĩa là bấm vào nút Clear trong khung Sỏrt & Filter í)

nên tất cả các vùng có dùng filter đều bỏ hết.

Quang_Hải · 6/11/13

A là cha của B
B là cha của C
C là cha của D

Nếu ta nói C.parent là ta muốn nói tới B. Tương tự cứ phá CD.. trong Dos để quay về thư mục mẹ

mhung12005 · 6/11/13

quanghai1969 đã viết:
A là cha của B
B là cha của C
C là cha của D

Nếu ta nói C.parent là ta muốn nói tới B. Tương tự cứ phá CD.. trong Dos để quay về thư mục mẹ

Em cũng nghĩ như vậy.

Như trong trường hợp của sư phụ NDU viết thì em hiểu là :

Cha hay mẹ gì đó của Range("D9

1000") - tức là Activessheet.Showalldata. Vậy đúng không anh ?

siwtom · 6/11/13

mhung12005 đã viết:
Em cũng nghĩ như vậy.

Như trong trường hợp của sư phụ NDU viết thì em hiểu là :

Cha hay mẹ gì đó của Range("D91000") - tức là Activessheet.Showalldata. Vậy đúng không anh ?

Thì đúng rồi. Nhìn code ta chỉ thấy Range("D9

1000"), tức đây là vùng trên ActiveSheet.
Vậy Range("D9

1000").Parent = ActiveSheet

Còn tại sao dùng Parent thay cho Sheet1 thì trong bài này tôi nghĩ thế này. Trong tất cả 5 sheet thì vùng dữ liệu nằm ở vị trí như nhau. Nếu ta chạy code khi sheet1, sheet2, ..., hoặc sheet5 đang activate thì code sẽ thao tác trên ActiveSheet, tức sheet1, ..., hoặc sheet5. Nếu ta thay Parent bằng Sheet1 mà ta chạy code khi vd. sheet3 đang activate thì các thao tác lọc và copy được làm trên sheet3 nhưng ShowAllData lại được gọi cho Sheet1, tức không đúng. Nói cách khác nếu dùng Parent thì ta có thể chạy code khi bất cứ sheet nào đang activate. Tức ShowAllData luôn được gọi cho sheet có chứa Range "kia". Nếu dùng sheetxyz thì khi ta chạy code cho sheet khác thì lại phải sửa thành: sheet2, ..., hoặc sheet5.
Thậm chí nếu vùng dữ liệu trên các sheet nằm ở vị trí khác nhau thì khi chạy code cho active sheet cụ thể thì chỉ phải sửa lại Range(...) mà thôi. Dùng Sheetxyz thay cho Parent thì phải sửa ở 2 chỗ.
Tất nhiên nếu code chỉ cần chạy trên sheet cụ thể thì cứ viết tường minh ra vd.
Sheet2.Range("D9

1000"), Sheet2.Range("I9"), Sheet2.ShowAllData

Lúc đó code luôn chạy cho sheet2 bất chấp active sheet là sheet nào.

Hay là còn bí mật gì mà tôi không nghĩ tới?

ndu96081631 · 6/11/13

mhung12005 đã viết:
Em cũng nghĩ như vậy.

Như trong trường hợp của sư phụ NDU viết thì em hiểu là :

Cha hay mẹ gì đó của Range("D91000") - tức là Activessheet.Showalldata. Vậy đúng không anh ?

Trường hợp này viết ActiveSheet là hoàn toàn chính xác
Nhưng ý tôi muốn các bạn để ý đến Parent.
So sánh 2 code

Mã:

With Range(....)
  ....
  ....
  [B][COLOR=#ff0000].Parent[/COLOR][/B].ShowAllData
End With

Và:

Mã:

With Range(....)
  ....
  ....
  [COLOR=#ff0000][B]ActiveSheet[/B][/COLOR].ShowAllData
End With

Gần như hoàn toàn giống nhau. Nhưng nếu người ta sửa 1 chút thế này:

Mã:

With [B][COLOR=#0000cd]Sheets("ABCXYZ gì gì đó")[/COLOR][/B].Range(....)
  ....
  ....
  [B][COLOR=#ff0000].Parent[/COLOR][/B].ShowAllData
End With

Thì cái Parent ở đây sẽ hoàn toàn khác với thằng ActiveSheet bên dưới

Mã:

With [B][COLOR=#0000cd]Sheets("ABCXYZ gì gì đó").[/COLOR][/B]Range(....)
  ....
  ....
  [COLOR=#ff0000][B]ActiveSheet[/B][/COLOR].ShowAllData
End With

Cái .Parent là đang nói đến Sheet "ABCXYZ gì gì đó" còn ActiveSheet là đang nói đến cái Sheet mà ta đang đứng khi thực thi code ---> Nó sẽ thật sự khác biệt khi ta đứng tại sheet này và xử lý dữ liệu tại 1 Sheet khác
Tóm lại:
- .Parent của vùng dữ liệu nào thì nghĩa là ta đang chỉ chính xác đến cái Sheet chứa dữ liệu đó
- ActiveSheet: Không liên quan gì đến dữ liệu nào cả, nó chỉ đơn giản là Sheet hiện hành
- Trong 1 số trường hợp cụ thế (khi chạy dữ liệu trên nhiều sheet) thì .Parent không bao giờ = ActiveSheet
- Theo thói quen: Hể ta With... cái gì ở phía trên thì phải "bám nó" đến cùng (nếu không thì With làm gì)

ruamap9 · 6/11/13

siwtom đã viết:
Thì đúng rồi. Nhìn code ta chỉ thấy Range("D91000"), tức đây là vùng trên ActiveSheet.
Vậy Range("D91000").Parent = ActiveSheet

Còn tại sao dùng Parent thay cho Sheet1 thì trong bài này tôi nghĩ thế này. Trong tất cả 5 sheet thì vùng dữ liệu nằm ở vị trí như nhau. Nếu ta chạy code khi sheet1, sheet2, ..., hoặc sheet5 đang activate thì code sẽ thao tác trên ActiveSheet, tức sheet1, ..., hoặc sheet5. Nếu ta thay Parent bằng Sheet1 mà ta chạy code khi vd. sheet3 đang activate thì các thao tác lọc và copy được làm trên sheet3 nhưng ShowAllData lại được gọi cho Sheet1, tức không đúng. Nói cách khác nếu dùng Parent thì ta có thể chạy code khi bất cứ sheet nào đang activate. Tức ShowAllData luôn được gọi cho sheet có chứa Range "kia". Nếu dùng sheetxyz thì khi ta chạy code cho sheet khác thì lại phải sửa thành: sheet2, ..., hoặc sheet5.
Thậm chí nếu vùng dữ liệu trên các sheet nằm ở vị trí khác nhau thì khi chạy code cho active sheet cụ thể thì chỉ phải sửa lại Range(...) mà thôi. Dùng Sheetxyz thay cho Parent thì phải sửa ở 2 chỗ.

Tất nhiên nếu code chỉ cần chạy trên sheet cụ thể thì cứ viết tường minh ra vd.
Sheet2.Range("D91000"), Sheet2.Range("I9"), Sheet2.ShowAllData

Lúc đó code luôn chạy cho sheet2 bất chấp active sheet là sheet nào.

Hay là còn bí mật gì mà tôi không nghĩ tới?

Cảm ơn các anh chị đã giải đáp. Cho em hỏi thêm với trường hợp em cần xóa hẳn các dòng chứa dự liệu trùng nhau như trên nhưng kết quả lại điền giữ nguyên tại vị trí ô D10 và lặp các kết quả cho các sheet còn lại thì làm bằng cách nào ạ? Em xin gửi kèm file chuc nang filter-2.xls với đề nghị cần giúp đỡ như trong file. Cảm ơn các anh chị!

ndu96081631 · 6/11/13

ruamap9 đã viết:
Cảm ơn các anh chị đã giải đáp. Cho em hỏi thêm với trường hợp em cần xóa hẳn các dòng chứa dự liệu trùng nhau như trên nhưng kết quả lại điền giữ nguyên tại vị trí ô D10 và lặp các kết quả cho các sheet còn lại thì làm bằng cách nào ạ? Em xin gửi kèm file chuc nang filter-2.xls với đề nghị cần giúp đỡ như trong file. Cảm ơn các anh chị!

Hơi khó 1 chút nhưng cũng làm được tuốt bằng Advanced Filter:

Mã:

Sub RemoveDuplicate()
  Dim rFilder As Range
  On Error Resume Next
  With Range("D9:E100")
    Application.ScreenUpdating = False
    .Resize(, 1).AdvancedFilter 1, , , True
    Set rFilder = .SpecialCells(12)
    .Parent.ShowAllData
    rFilder.EntireRow.Hidden = True
    .SpecialCells(12).Delete 2
    .EntireRow.Hidden = False
  End With
  Application.ScreenUpdating = True
End Sub

Code này thực thi trên 1 Sheet. Nếu muốn nhiều sheet thì For..Next
Mời các cao thủ thêm phần for.. next vào nhé. Bây giờ các bạn mới thấy tác dụng của Parent đây

ruamap9 · 6/11/13

ndu96081631 đã viết:
Hơi khó 1 chút nhưng cũng làm được tuốt bằng Advanced Filter:

Mã:

Sub RemoveDuplicate() Dim rFilder As Range On Error Resume Next With Range("D9:E100") Application.ScreenUpdating = False .Resize(, 1).AdvancedFilter 1, , , True Set rFilder = .SpecialCells(12) .Parent.ShowAllData rFilder.EntireRow.Hidden = True .SpecialCells(12).Delete 2 .EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub

Code này thực thi trên 1 Sheet. Nếu muốn nhiều sheet thì For..Next
Mời các cao thủ thêm phần for.. next vào nhé. Bây giờ các bạn mới thấy tác dụng của Parent đây

Em xin cảm ơn Thầy ndu96081631 và các anh chị. Chỉ còn vòng lặp For..Next để thực hiện cho nhiều sheet nữa mong các Pro chỉ giáo.

Thong Hoang Tien 250691 · 11/6/17

/note:
Set rng = Workbooks("Test1").Sheets(2).Range("E:F").Select
ActiveSheet.Range(rng).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

đoạn code này của mình chạy trong file tên Book2.xlsm, nhưng sao khi chạy nó báo type mismatch nhỉ? ai giải thích giúp mình với

phulien1902 · 11/6/17

Thong Hoang Tien 250691 đã viết:
/note:
Set rng = Workbooks("Test1").Sheets(2).Range("E:F").Select
ActiveSheet.Range(rng).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

đoạn code này của mình chạy trong file tên Book2.xlsm, nhưng sao khi chạy nó báo type mismatch nhỉ? ai giải thích giúp mình với

Bạn thử thay:

PHP:

Set rng = Workbooks("Test1").Sheets(2).Range("E:F").Select

bằng:

PHP:

Sheets(2).Range("E:F").Select

Thong Hoang Tien 250691 · 11/6/17

phulien1902 đã viết:
Bạn thử thay:

PHP:

Set rng = Workbooks("Test1").Sheets(2).Range("E:F").Select

bằng:

PHP:

Sheets(2).Range("E:F").Select

không được bạn, bạn thử xem file giúp mình có vấn đề gì với nó vậy .

Thong Hoang Tien 250691 · 11/6/17

Mã:

Public Sub ABC()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range

     ChDir "C:\Users\thoangti\Desktop\"
  Workbooks.OpenText Filename:= _
        "C:\Users\thoangti\Desktop\Test1.xlsx"
Workbooks("Test1.xlsx").Activate
Set wb1 = Workbooks("test1")
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
With wb1
With ws2
        .Range("D:H").ClearContents
End With
With ws1
        lR1 = .Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To lR1
    Set code = Sheets(1).Cells(i, 5)
    Set vTO = Sheets(1).Cells(i, 2)
If Len(code) = 7 Then
        vcode = code
    Else
        vcode = "0" & code
End If
 
        copydata = Workbooks("Test1").Sheets(2).Cells(Rows.Count, "E").End(xlUp).Row
        Workbooks("Test1").Sheets(2).Cells(copydata + 1, "E") = vTO
        Workbooks("Test1").Sheets(2).Cells(copydata + 1, "F") = vcode
    
    Next i
   End With
With ws2
    lr2 = .Range("E" & Rows.Count).End(xlUp).Row
    Set rng = Workbooks("Test1").Sheets(2).Range("E:F").Select
    ActiveSheet.Range(rng).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes 'bi loi o day
    
End With
 End With
End Sub

Code thay thế chức năng filter (1 người xem)

Người dùng đang xem chủ đề này

Thành viên mới

File đính kèm

❆❆❆❆❆❆❆❆

Thành viên mới

Thành viên gạo cội

Thành viên gạo cội

❆❆❆❆❆❆❆❆

Huyền thoại GPE

Thành viên gạo cội

File đính kèm

Thành viên gạo cội

Thành viên gạo cội

Huyền thoại GPE

Thành viên gạo cội

Thành viên mới

Thành viên chậm chạm

Thành viên gạo cội

Thành viên chậm chạm

Thành viên gắn bó

Huyền thoại GPE

Thành viên mới

File đính kèm

Huyền thoại GPE

Thành viên mới

Thành viên hoạt động

GPE - My love

Thành viên hoạt động

Thành viên hoạt động

Thời gian đếm ngược.