Tăng tốc độ xóa dòng?

tigertiger · 9/6/09

Một anh bạn nhờ, có sheet lớn số dòng khoảng 10000 dòng, muốn xóa tất cả ác dòng mà có giá trị cột B >=1000, có đoạn code sau nhưng rất chậm

PHP:

Dim myRow As Long
    Dim myCol As String
 
    Application.ScreenUpdating = False
    myCol = "B"
    For myRow = 10000 To 2 Step -1
        If ActiveSheet.Cells(myRow, myCol).Value >= 1000 Then
            Rows(myRow).delete
        End If
    Next myRow
    Application.ScreenUpdating = True

vậy làm sao cải thiện tốc độ, các bạn góp ý cho

xin cảm ơn.

ThuNghi · 9/6/09

Minh Nguyen đã viết:
Mình bổ sung thêm một chút:
Với file dữ liệu trên, mình filter theo "Revision:" ở cột A và tiến hành xóa -> báo lỗi
Cám ơn

Sao mình xóa theo 0* hay 1* thì vô tư khi dùng AutoFilter

ndu96081631 · 9/6/09

Minh Nguyen đã viết:
Vâng, về cơ bản theo yêu cầu của bài này, filter sẽ khả thi.
Xin mở rộng một chút, bác đã bao giờ gặp phải lỗi không xóa được khi dùng filter chưa ạ ?
Em có file dữ liệu gần 40.000 dòng (dữ liệu thô only), filter xong thì nó xóa hết luôn chứ không phải chỉ những dòng đã lọc ra. Nhân đây em nhờ bác xem giúp em lỗi này.
.

Nhớ không lầm bài này đã từng đề cập trên diển đàn rồi mà...
Nói chung lổi này không phải do AutoFilter mà do SpecialCells... nó không tài nào xác lập được vùng dử liệu đặc biệt...
Với trường hợp này, sau khi AutoFilter xong, muốn làm gì bạn hãy chia dử liệu ra làm nhiều phần (chẳng hạn chia dử liệu ra làm 2 hoặc 3 phần) rồi tiếp tục thao tác
Ví dụ:
- Thay vì dùng:

PHP:

Range("A2:A60000").SpecialCells(4).EntireRow.Delete

ta chia thành 6 phần

PHP:

Range("A2:A10000").SpecialCells(4).EntireRow.Delete
Range("A10001:A20000").SpecialCells(4).EntireRow.Delete
Range("A20001:A30000").SpecialCells(4).EntireRow.Delete
Range("A30001:A40000").SpecialCells(4).EntireRow.Delete
Range("A40001:A50000").SpecialCells(4).EntireRow.Delete
Range("A50001:A60000").SpecialCells(4).EntireRow.Delete

Thử xem!
----------------

ThuNghi đã viết:
Sao mình xóa theo 0* hay 1* thì vô tư khi dùng AutoFilter

Thử Filter cột B là Blanks rồi Ctrl + G\Special\Visible cells only xem ---> Sẽ gặp ngay lổi này:

rollover79 · 9/6/09

ThuNghi đã viết:
Sao mình xóa theo 0* hay 1* thì vô tư khi dùng AutoFilter

Nó phụ thuộc vào số lượng dòng dữ liệu lọc ra cần xóa. Nếu quá lớn thì excel không phân tích được vùng đó nên bật lên 1 cảnh báo vậy thôi.

tedaynui · 9/6/09

Minh Nguyen đã viết:
Mình bổ sung thêm một chút:
Với file dữ liệu trên, mình filter theo "Revision:" ở cột A và tiến hành xóa -> báo lỗi
Cám ơn

Với File của bạn khá lớn (gần 40.000 dòng) nên khi Filter xong và Del hay Clear đều báo lỗi như bạn mô tả. Nhưng nếu ta giảm bớt số dòng chọn và xoá làm nhiều lần thì vẫn được. Mình nghĩ do số dòng quá lớn và các vùng chọn lại quá rời rạc nhau nên Excel "chịu hỏng nổi" (sau khi Filter), hi hi !$@!!

Trong trường hợp làm tay, mình nghĩ :
Cách 1:
- Filter rồi xoá nhiều lần... Pó tay +-+-+-+

Cách 2: (khả thi hơn)
- Tạo 1 cột phụ để đánh số thứ tự
- Sau đó Sort cột Level ; xoá dòng
- Cuối cùng là Sort lại cột đánh số thứ tự để giữ lại trật tự của dữ liệu

TDN

tigertiger · 9/6/09

ndu96081631 đã viết:
Nhớ không lầm bài này đã từng đề cập trên diển đàn rồi mà...
Nói chung lổi này không phải do AutoFilter mà do SpecialCells... nó không tài nào xác lập được vùng dử liệu đặc biệt...
Với trường hợp này, sau khi AutoFilter xong, muốn làm gì bạn hãy chia dử liệu ra làm nhiều phần (chẳng hạn chia dử liệu ra làm 2 hoặc 3 phần) rồi tiếp tục thao tác
Ví dụ:
- Thay vì dùng:

PHP:

Range("A2:A60000").SpecialCells(4).EntireRow.Delete

ta chia thành 6 phần

PHP:

Range("A2:A10000").SpecialCells(4).EntireRow.Delete Range("A10001:A20000").SpecialCells(4).EntireRow.Delete Range("A20001:A30000").SpecialCells(4).EntireRow.Delete Range("A30001:A40000").SpecialCells(4).EntireRow.Delete Range("A40001:A50000").SpecialCells(4).EntireRow.Delete Range("A50001:A60000").SpecialCells(4).EntireRow.Delete

Thử xem!
----------------

Thử Filter cột B là Blanks rồi Ctrl + G\Special\Visible cells only xem ---> Sẽ gặp ngay lổi này:

Đúng thế anh NDU ah,

phải thêm GO Special cells thì tốc độ mới cải thiện trong trường hợp tổng quát

Cám ơn tất cả mọi người
Đã đưa ra các ý kiến

anhchangnhaque1552000 · 12/6/09

Nhưng nếu chúng ta dùng filter sau đó mới xóa thì chúng ta chỉ xóa được 1 lần thôi. Theo mình thì dùng cách duyệt từng dòng thì hay hơn. Các bạn có đồng ý như thế không..hihi

ndu96081631 · 12/6/09

anhchangnhaque1552000 đã viết:
Nhưng nếu chúng ta dùng filter sau đó mới xóa thì chúng ta chỉ xóa được 1 lần thôi. Theo mình thì dùng cách duyệt từng dòng thì hay hơn. Các bạn có đồng ý như thế không..hihi

Hay hơn ở chổ nào vậy bạn?
Bạn cũng biết nói rằng: chúng ta dùng filter sau đó mới xóa thì chúng ta chỉ xóa được 1 lần thôi ---> Vậy tôi hỏi bạn giữa việc xóa 1 lần với xóa nhiều lần, cái nào nhanh hơn và hay hơn?

anhchangnhaque1552000 · 22/6/09

Mình không phủ định Autofilter. Nhưng tôi nghĩ như thế này không biết đúng hay không?
Giả sử cơ sở dữ liệu của chúng ta có n dòng trong đó có n/2 dòng thỏa điều kiện. Tạm gọi mỗi lần thực hiện 1 thao tác (phép so sánh, phép lọc, xóa..) là 1s. Khi ta dung vòng lập For. Vòng for sẽ duyệt n lần và trong n lần đó tìm được n/2 dòng thỏa điều kiện và thực hiện xóa duyệt đến đâu thì xóa đến đó nếu thỏa ĐK). Vậy tạm gọi tổng thời gian thực hiện là: t = n + n/2 = 3n/2.
Khi dùng Filter. Có phải là khi chúng ta lọc xong rồi mới xóa đúng không?
Vậy phải duyệt n lần để tìm n/2 lần thỏa ĐK để lọc ra và sau đó mất n/2 lần xóa (Không thể xóa cái vèo n/2 dòng). Như vậy tổng thơi gian là: t = n + n/2 + n/2 = 2n
(Hơn nữa khi dùng filter nếu code chưa thật hoàn thiện thì chúng ta thực hiện xóa lần 1 sau đó nếu quên mà nhấn nút thêm 1 lần nữa thì code có thể báo lỗi--> Do khi tôi tải các bài trên GPEC về thì chạy thử thấy code bị lỗi)
Vì thế tôi nghĩ For hay hơn. Nếu thấy chỗ nào không đúng bạn giải thích giúp tôi nha.
Thanks

ndu96081631 · 22/6/09

anhchangnhaque1552000 đã viết:
Mình không phủ định Autofilter. Nhưng tôi nghĩ như thế này không biết đúng hay không?
Giả sử cơ sở dữ liệu của chúng ta có n dòng trong đó có n/2 dòng thỏa điều kiện. Tạm gọi mỗi lần thực hiện 1 thao tác (phép so sánh, phép lọc, xóa..) là 1s. Khi ta dung vòng lập For. Vòng for sẽ duyệt n lần và trong n lần đó tìm được n/2 dòng thỏa điều kiện và thực hiện xóa duyệt đến đâu thì xóa đến đó nếu thỏa ĐK). Vậy tạm gọi tổng thời gian thực hiện là: t = n + n/2 = 3n/2.
Khi dùng Filter. Có phải là khi chúng ta lọc xong rồi mới xóa đúng không?
Vậy phải duyệt n lần để tìm n/2 lần thỏa ĐK để lọc ra và sau đó mất n/2 lần xóa (Không thể xóa cái vèo n/2 dòng). Như vậy tổng thơi gian là: t = n + n/2 + n/2 = 2n
(Hơn nữa khi dùng filter nếu code chưa thật hoàn thiện thì chúng ta thực hiện xóa lần 1 sau đó nếu quên mà nhấn nút thêm 1 lần nữa thì code có thể báo lỗi--> Do khi tôi tải các bài trên GPEC về thì chạy thử thấy code bị lỗi)
Vì thế tôi nghĩ For hay hơn. Nếu thấy chỗ nào không đúng bạn giải thích giúp tôi nha.
Thanks

Làm gì có vụ đó chứ:
AutoFilter lọc 1 lần, rồi xóa 1 lần chắc chắn phải nhanh hơn so với For duyệt toàn bộ các dòng chứ
Vụ AutoFilter này tôi làm hoài nên tin chắc rằng For không bao giờ có thể qua mặt được... Nếu bạn không tin, cứ giả lập dử liệu rồi đưa lên đây, chúng ta cùng kiểm chứng tốc độ giữa 2 phương pháp (chứ chỉ nói thì không có bằng chứng)

cadafi · 22/6/09

anhchangnhaque1552000 đã viết:
....Giả sử cơ sở dữ liệu của chúng ta có n dòng trong đó có n/2 dòng thỏa điều kiện. Tạm gọi mỗi lần thực hiện 1 thao tác (phép so sánh, phép lọc, xóa..) là 1s. Khi ta dung vòng lập For. Vòng for sẽ duyệt n lần và trong n lần đó tìm được n/2 dòng thỏa điều kiện và thực hiện xóa duyệt đến đâu thì xóa đến đó nếu thỏa ĐK). Vậy tạm gọi tổng thời gian thực hiện là: t = n + n/2 = 3n/2.
Khi dùng Filter. Có phải là khi chúng ta lọc xong rồi mới xóa đúng không?
Vậy phải duyệt n lần để tìm n/2 lần thỏa ĐK để lọc ra và sau đó mất n/2 lần xóa (Không thể xóa cái vèo n/2 dòng). Như vậy tổng thơi gian là: t = n + n/2 + n/2 = 2n

Không biết bạn dựa vào cơ sở nào để giả định thực hiện 1 thao tác là 1s cho cả For..Next và Autofilter vậy? Cách so sánh này không có cơ sở!

Như anh NDU nói, bạn thử giả lập dữ liệu (khoản 50.000 dòng chẳng hạn) rồi so sánh thời gian thực hiện thử xem! Lúc đó mới thấy cái lợi hại của AutoFilter!

anhchangnhaque1552000 · 24/6/09

For - Auto

ca_dafi đã viết:
Không biết bạn dựa vào cơ sở nào để giả định thực hiện 1 thao tác là 1s cho cả For..Next và Autofilter vậy? Cách so sánh này không có cơ sở!

Như anh NDU nói, bạn thử giả lập dữ liệu (khoản 50.000 dòng chẳng hạn) rồi so sánh thời gian thực hiện thử xem! Lúc đó mới thấy cái lợi hại của AutoFilter!

Nếu vậy anh chị có thể giúp mình đánh giá thời gian tối ưu của 2 thuật toán (For next và Autofilter) được không.
Cảm ơn.

ndu96081631 · 24/6/09

anhchangnhaque1552000 đã viết:
Nếu vậy anh chị có thể giúp mình đánh giá thời gian tối ưu của 2 thuật toán đó không.(For next và Autofilter)

Dể mà... bạn giả lập dử liệu cở 20.000 dòng, sau đó Test bằng 2 cách trên xem cái này nhanh hơn!
Nếu không tự test được cứ gữi file lên đây, tôi test cho!
Dám khẳng định 100% rồi bạn rằng AutoFilter nhanh hơn... nếu không nói là nhanh hơn rất.. rất.. nhiều

SA_DQ · 24/6/09

Xin ủng hộ các bạn 2 macro sau

Macro tao dữ liệu

PHP:

Option Explicit
Dim Timer_ As Double
 Dim jJ As Long, eRw As Long

Sub AddValue()
 Application.ScreenUpdating = False
 Timer_ = Timer
 For jJ = 1 To 50000
   Cells(jJ, "A") = jJ
 Next jJ
 [C1].Value = Timer - Timer_
End Sub

Macro xóa 1/10 số records vừa tạo ra (Kết quả thời gian thực hiện đã được lưu lại tại [C3]):

PHP:

Sub Delete1_10()
 Dim Rng As Range, Clls As Range
 Application.ScreenUpdating = False
 Timer_ = Timer
 
 eRw = [a65500].End(xlUp).Row
 For jJ = eRw To 1 Step -1
   If Cells(jJ, "A").Value Mod 9 = 0 Then _
      Cells(jJ, "A").EntireRow.Delete
 Next jJ
 [c3].Value = Timer - Timer_
End Sub

ndu96081631 · 24/6/09

SA_DQ đã viết:
Macro tao dữ liệu

PHP:

Option Explicit Dim Timer_ As Double Dim jJ As Long, eRw As Long Sub AddValue() Application.ScreenUpdating = False Timer_ = Timer For jJ = 1 To 50000 Cells(jJ, "A") = jJ Next jJ [C1].Value = Timer - Timer_ End Sub

Macro xóa 1/10 số records vừa tạo ra (Kết quả thời gian thực hiện đã được lưu lại tại [C3]):

PHP:

Sub Delete1_10() Dim Rng As Range, Clls As Range Application.ScreenUpdating = False Timer_ = Timer eRw = [a65500].End(xlUp).Row For jJ = eRw To 1 Step -1 If Cells(jJ, "A").Value Mod 9 = 0 Then _ Cells(jJ, "A").EntireRow.Delete Next jJ [c3].Value = Timer - Timer_ End Sub

Sư phụ ơi... Sư phụ phải dùng kiểu lọc nào có thể làm được bằng AutoFilter ấy chứ
Cái Mod 9 = 0 của sư phụ thì AutoFilter xử lý bằng cách nào đây?

SA_DQ · 25/6/09

Vậy thì thêm vô macro đầu 1 cấu lệnh nữa:

ndu96081631 đã viết:
Sư phụ ơi... Sư phụ phải dùng kiểu lọc nào có thể làm được bằng AutoFilter ấy chứ
Cái Mod 9 = 0 của sư phụ thì AutoFilter xử lý bằng cách nào đây?

PHP:

Option Explicit
Dim Timer_ As Double
Dim jJ As Long, eRw As Long
 
Sub AddValue()
Application.ScreenUpdating = False
Timer_ = Timer
For jJ = 1 To 50000
Cells(jJ, "A") = jJ: Cells(JJ, 2)= jJ Mod 9 '<'
Next jJ
[C1].Value = Timer - Timer_
End Sub

Tăng tốc độ xóa dòng?

tigertiger

Coming back ...

ThuNghi

Hãy cho rồi sẽ nhận!

ndu96081631

Huyền thoại GPE

File đính kèm

rollover79

Thành viên tiêu biểu

tedaynui

(_)

tigertiger

Coming back ...

anhchangnhaque1552000

Thành viên mới

ndu96081631

Huyền thoại GPE

anhchangnhaque1552000

Thành viên mới

ndu96081631

Huyền thoại GPE

cadafi

Hành động từ trái tim

anhchangnhaque1552000

Thành viên mới

ndu96081631

Huyền thoại GPE

SA_DQ

/(hông là gì!

ndu96081631

Huyền thoại GPE

SA_DQ

/(hông là gì!

Bài viết mới nhất

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Đếm ngược thời gian

Tăng tốc độ xóa dòng?

Coming back ...

Hãy cho rồi sẽ nhận!

Huyền thoại GPE

File đính kèm

Thành viên tiêu biểu

(*_*)

Coming back ...

Thành viên mới

Huyền thoại GPE

Thành viên mới

Huyền thoại GPE

Hành động từ trái tim

Thành viên mới

Huyền thoại GPE

/(hông là gì!

Huyền thoại GPE

/(hông là gì!

Đếm ngược thời gian

(_)