Xóa dòng trong một sheet rất chậm

[vancong28x3]

Xin chào tất cả mọi người.
Mình có 1 file khi xóa dòng trong 1 sheet có chứa nhiều dòng dữ liệu phải đợi rất chậm. Còn sheet không có dữ liệu hoặc, ít dòng dữ liệu thì xóa rất nhanh. Không biết lỗi do đâu. Rất mong mọi người giúp cách để xóa các dòng trong các sheet này nhanh hơn.
Trân trọng cám ơn!

 

[Maika8008]

Trong khi chờ đợi tìm ra nguyên nhân thì bạn chép dữ liệu trong sheet bị chậm đó ra 1 file khác, xóa sheet đó đi, thêm sheet mới rồi chép dữ liệu trở lại.

 

[SA_DQ]

. . . . . . Rất mong mọi người giúp cách để xóa các dòng trong các sheet này nhanh hơn.
Trân trọng cám ơn!

Chắc bạn đang định xóa các dong ở trang 'DL'
Nếu đúng vậy thì bạn đang cần xóa theo tiêu chí nào & . . . .
(Sẵn hỏi luôn) Những dòng dữ liệu cần xóa có phải lưu lại vô đâu đó hay 1 đi không trở lại'?

 

[vancong28x3]

Chắc bạn đang định xóa các dong ở trang 'DL'
Nếu đúng vậy thì bạn đang cần xóa theo tiêu chí nào & . . . .
(Sẵn hỏi luôn) Những dòng dữ liệu cần xóa có phải lưu lại vô đâu đó hay 1 đi không trở lại'?

Cám ơn bạn. Dòng mình cần xóa ở sheet DL và CUVT. M xóa luôn.

Bài đã được tự động gộp: 24/2/23

Chắc bạn đang định xóa các dong ở trang 'DL'
Nếu đúng vậy thì bạn đang cần xóa theo tiêu chí nào & . . . .
(Sẵn hỏi luôn) Những dòng dữ liệu cần xóa có phải lưu lại vô đâu đó hay 1 đi không trở lại'?

Cám ơn bạn. Mình cũng đã copy thử sang sheet khác. Nhưng ở sheet mới vẫn xóa dòng rất chậm. Nếu sheet mình làm DT và CUVT mà có ít dòng. Thì vẫn xóa bình thường. Còn nếu nhiều dòng lại xóa rất chậm.

 

[SA_DQ]

@Chủ bài đăng:
Bạn thử chạy macro này:

Sub XoaDuLieu()
 Dim Rws As Long, Tmp As Long, Tmr As Double
 Dim Sh As Worksheet
 
 Set Sh = ThisWorkbook.Worksheets("CUVT")
 Sh.[b4].CurrentRegion.Offset(1).ClearContents
 Randomize
 With Sheets("DL")
    Rws = .[b2].CurrentRegion.Rows.Count
    Tmp = (10) ^ 4 + Rws * Rnd() \ 1
    .[b2].Resize(Tmp, 2).Copy Destination:=Sh.[A5]
    .[E2].Resize(Tmp, 4).Copy Destination:=Sh.[c5]
    .[l2].Resize(Tmp, 3).Copy Destination:=Sh.[G5]
 End With
 Tmr = Timer()
 Sh.[B5].CurrentRegion.Offset(1).ClearContents
 Sh.[K9999].End(xlUp).Offset(1).Value = Timer() - Tmr
 Sh.[m9999].End(xlUp).Offset(1).Value = Tmp
End Sub

Sau đó đối chiếu với bảng ni ra răng?

Thời gian

Số dòng

0.03125​

8820​

0.023438​

5983​

0.039063​

12155​

0.039063​

11619​

0.027344​

10431​

0.042969​

21293​

 

[vancong28x3]

@Chủ bài đăng:
Bạn thử chạy macro này:

Sub XoaDuLieu()
 Dim Rws As Long, Tmp As Long, Tmr As Double
 Dim Sh As Worksheet
 
 Set Sh = ThisWorkbook.Worksheets("CUVT")
 Sh.[b4].CurrentRegion.Offset(1).ClearContents
 Randomize
 With Sheets("DL")
    Rws = .[b2].CurrentRegion.Rows.Count
    Tmp = (10) ^ 4 + Rws * Rnd() \ 1
    .[b2].Resize(Tmp, 2).Copy Destination:=Sh.[A5]
    .[E2].Resize(Tmp, 4).Copy Destination:=Sh.[c5]
    .[l2].Resize(Tmp, 3).Copy Destination:=Sh.[G5]
 End With
 Tmr = Timer()
 Sh.[B5].CurrentRegion.Offset(1).ClearContents
 Sh.[K9999].End(xlUp).Offset(1).Value = Timer() - Tmr
 Sh.[m9999].End(xlUp).Offset(1).Value = Tmp
End Sub

Sau đó đối chiếu với bảng ni ra răng?

Thời gian

Số dòng

0.03125​

8820​

0.023438​

5983​

0.039063​

12155​

0.039063​

11619​

0.027344​

10431​

0.042969​

21293​

Cám ơn bạn. Nhưng ý mình muốn là xóa 1 dòng. Như code bạn viết thì xóa nhanh nhưng chỉ xóa được số liệu trong ô.
Mình có gửi lại file theo như ý m nói trong file kèm theo.
Trong file mình có code như sau để tìm số dương ở cột E sau đó xóa ô từ A đến N tương ứng với dòng chứa số dương vừa tìm được nhưng khi xóa nó chạy rất chậm. Mà xóa bằng tay 1 dòng cũng rất chậm. Không hiểu vì sao.
Mong bạn giúp.

For b = Range("E500000").End(xlUp).Row To 5 Step -1
If Range("E" & b).Value > 0 Then
Range(Range("A" & b), Range("I" & b)).Select
Selection.Delete Shift:=xlUp
End If
Next b

 

[Maika8008]

Cám ơn bạn. Nhưng ý mình muốn là xóa 1 dòng. Như code bạn viết thì xóa nhanh nhưng chỉ xóa được số liệu trong ô.
Mình có gửi lại file theo như ý m nói trong file kèm theo.
Trong file mình có code như sau để tìm số dương ở cột E sau đó xóa ô từ A đến N tương ứng với dòng chứa số dương vừa tìm được nhưng khi xóa nó chạy rất chậm. Mà xóa bằng tay 1 dòng cũng rất chậm. Không hiểu vì sao.
Mong bạn giúp.

For b = Range("E500000").End(xlUp).Row To 5 Step -1
If Range("E" & b).Value > 0 Then
Range(Range("A" & b), Range("I" & b)).Select
Selection.Delete Shift:=xlUp
End If
Next b

Bạn thử làm theo tôi nhé:
- Chép dữ liệu ở sheet bị chậm đó sang FILE mới
- Xóa sheet bị chậm đó đi.
- Chèn thêm 1 sheet mới và lấy lại với tên trước đây.
- Chép dữ liệu từ file mới kia sang sheet mới tạo này.

Xem nó có bị chậm khi xóa dòng không!

 

[SA_DQ]

Lý ra bạn phải viết những ý này ngay từ bài 1:

tìm trong cột E các số dương và xóa ô từ A đến E tương ứng với dòng đã được tìm thấy

Giờ thì trễ rồi; Tạm biệt bạn!

Sub XoaDuLieu()
 Dim Rws As Long, Col As Integer, W As Long, J As Long, Tmr As Double, Cot As Integer
 Dim Arr()

 Rws = [E4].CurrentRegion.Rows.Count
 Col = [E4].CurrentRegion.Columns.Count
 Arr() = [A5].Resize(Rws, Col).Value
 ReDim aKQ(1 To Rws, 1 To Col)
 Tmr = Timer()
 For J = 1 To UBound(Arr())
    If Arr(J, 5) > 0 Then
    Else
        W = W + 1
        For Cot = 1 To Col
            aKQ(W, Cot) = Arr(J, Cot)
        Next Cot
    End If
 Next J
 MsgBox W, , Timer() - Tmr
End Sub

 

[vancong28x3]

Bạn thử làm theo tôi nhé:
- Chép dữ liệu ở sheet bị chậm đó sang FILE mới
- Xóa sheet bị chậm đó đi.
- Chèn thêm 1 sheet mới và lấy lại với tên trước đây.
- Chép dữ liệu từ file mới kia sang sheet mới tạo này.

Xem nó có bị chậm khi xóa dòng không!

Cám ơn bạn. Đúng là do file mình. Chỉ cần copy, paste sang file mới là không bị nữa.
do code mình dùng nó làm thành hiện trạng này.

Bài đã được tự động gộp: 24/2/23

Lý ra bạn phải viết những ý này ngay từ bài 1:

Giờ thì trễ rồi; Tạm biệt bạn!

Sub XoaDuLieu()
 Dim Rws As Long, Col As Integer, W As Long, J As Long, Tmr As Double, Cot As Integer
 Dim Arr()

 Rws = [E4].CurrentRegion.Rows.Count
 Col = [E4].CurrentRegion.Columns.Count
 Arr() = [A5].Resize(Rws, Col).Value
 ReDim aKQ(1 To Rws, 1 To Col)
 Tmr = Timer()
 For J = 1 To UBound(Arr())
    If Arr(J, 5) > 0 Then
    Else
        W = W + 1
        For Cot = 1 To Col
            aKQ(W, Cot) = Arr(J, Cot)
        Next Cot
    End If
 Next J
 MsgBox W, , Timer() - Tmr
End Sub

Sorry bạn. ban đầu mình chỉ muốn tìm hiểu nguyên nhân tại sao khi xóa, chèn dòng thì file nó lại chậm như vậy. Để tìm cách sửa đoạn code của mình viết.
Nhưng giờ thì biết do code mình viết nó làm ra hiện trạng này.
Nếu được bạn có thể cho mình thông tin liên lạc để trao đổi, học hỏi thêm code từ bạn được không?
Trân trọng cám ơn!

 

[Hoàng Tuấn 868]

do code mình dùng nó làm thành hiện trạng này.

Nhiều khả năng là không phải do code.

 

[vancong28x3]

Nhiều khả năng là không phải do code.

Sau khi mình copy, paste số chết. Chỉ copy vùng số liệu, không copy cả sheet sang file mới. Thì xóa bình thường.
Sau đó copy code vào chạy. thì lại bị lỗi xóa dòng, chèn dòng bị chậm.

 

[SA_DQ]

Bạn chủ bài đăng có thể cho mọi người chiêm nghiệm đứa con tinh thần của bạn không?
Bài 1 của bạn chưa hề nói xóa dòng ở trang nào?
Chỉ bài sau đó của bạn mới biết tên trang tính cần xóa dòng dữ liệu; Nhưng trong file thì trang đó hầu như trắng trơn!
. . . . . .

 

[vancong28x3]

Bạn chủ bài đăng có thể cho mọi người chiêm nghiệm đứa con tinh thần của bạn không?
Bài 1 của bạn chưa hề nói xóa dòng ở trang nào?
Chỉ bài sau đó của bạn mới biết tên trang tính cần xóa dòng dữ liệu; Nhưng trong file thì trang đó hầu như trắng trơn!
. . . . . .

Mình cần xử lý số liệu như sau:
Mình có rất nhiều mã sản xuất. Trong 1 mã sản xuất này có rất nhiều vật tư. như trong sheet DL và một số hàng vật tư trong kho sheet TK 1.1
Hiện tại mình cần phải lập thứ tự ưu tiên cho các mã sản xuất này. Vì các mã sản xuất có thể dùng vật tư trùng nhau nên mình cứ khi cần làm mà mình phải lấy hàng tồn kho chia cho các vật tư của mã sản xuất xem cái nào thiếu, cái nào đủ.
Để làm bảng theo dõi, mua vật tư về.
Sau đó lập bảng cho mã sản xuất theo dõi xem vật tư nào thiếu.
Mình lập file khi mới làm cho 10 mã sản xuất thì chạy không vấn đề. Nhưng khi dùng cho nhiều hơn thì bắt đầu file chạy chậm và bị đơ.
Khi thử code thì mình thấy tình trạng xóa dòng nó bị đơ. nên vướng.
Do cũng không phải người rành làm về vba nên viết được như file này. Nếu bạn xem có gì góp ý, cứ nói mình để sửa nhé.
Khi vào sheet CUVT dùng nhấn số 1. sau nhấn số 2 để chạy code.

Thanks bạn

 

[HUONGHCKT]

Mình cần xử lý số liệu như sau:
Mình có rất nhiều mã sản xuất. Trong 1 mã sản xuất này có rất nhiều vật tư. như trong sheet DL và một số hàng vật tư trong kho sheet TK 1.1
Hiện tại mình cần phải lập thứ tự ưu tiên cho các mã sản xuất này. Vì các mã sản xuất có thể dùng vật tư trùng nhau nên mình cứ khi cần làm mà mình phải lấy hàng tồn kho chia cho các vật tư của mã sản xuất xem cái nào thiếu, cái nào đủ.
Để làm bảng theo dõi, mua vật tư về.
Sau đó lập bảng cho mã sản xuất theo dõi xem vật tư nào thiếu.
Mình lập file khi mới làm cho 10 mã sản xuất thì chạy không vấn đề. Nhưng khi dùng cho nhiều hơn thì bắt đầu file chạy chậm và bị đơ.
Khi thử code thì mình thấy tình trạng xóa dòng nó bị đơ. nên vướng.
Do cũng không phải người rành làm về vba nên viết được như file này. Nếu bạn xem có gì góp ý, cứ nói mình để sửa nhé.
Khi vào sheet CUVT dùng nhấn số 1. sau nhấn số 2 để chạy code.

Thanks bạn

Tôi thấy code trong file này chạy mà chờ mỏi ra và mà vẫn chưa ra.
Tôi đã code lại thành công lấy ra cái bảng ở sheet CUVT có cột "thieu", "đu" gì đó (từ T5-W...). Còn phần bảng từ cột A-I đã code được 7-80%, thì cần phải hỏi thêm bạn mới hoàn chỉnh được. Nếu bạn vẫn còn quan tâm và muốn có kết quả, hãy liên hệ tôi qua số zalo 0986997214 để tôi tiện hỏi lại 1 số vấn đề phục vụ cho hoàn thiện code.

 

[vancong28x3]

Tôi thấy code trong file này chạy mà chờ mỏi ra và mà vẫn chưa ra.
Tôi đã code lại thành công lấy ra cái bảng ở sheet CUVT có cột "thieu", "đu" gì đó (từ T5-W...). Còn phần bảng từ cột A-I đã code được 7-80%, thì cần phải hỏi thêm bạn mới hoàn chỉnh được. Nếu bạn vẫn còn quan tâm và muốn có kết quả, hãy liên hệ tôi qua số zalo 0986997214 để tôi tiện hỏi lại 1 số vấn đề phục vụ cho hoàn thiện code.

thanks bạn đã quan tâm. Do mình không rành về vba nên viết đa số là dùng vòng lặp. mà với dữ liệu lên tới hơn chục nghìn dòng nó sẽ gần như bị đơ.
Mình cũng đang sửa lại code để xử lý nhanh hơn.
Mình sẽ liên hệ bạn để trao đổi thêm và mong học hỏi từ bạn nhiều hơn.

 

[HUONGHCKT]

thanks bạn đã quan tâm. Do mình không rành về vba nên viết đa số là dùng vòng lặp. mà với dữ liệu lên tới hơn chục nghìn dòng nó sẽ gần như bị đơ.
Mình cũng đang sửa lại code để xử lý nhanh hơn.
Mình sẽ liên hệ bạn để trao đổi thêm và mong học hỏi từ bạn nhiều hơn.

Kiến thức về VBA của tôi nông cạn và chắp vá còn non và xanh lắm, nhưng thấy bạn cũng có thái độ học hỏi nghiêm túc và qua đọc code của bạn, Tôi có 1 số lời khuyên bạn:
1/ Đã dùng VBA thì hạn chế dùng hàm của MS Excel, do mỗi lần bảng tính có sự thay đổi gì đó thì lại load lại tất cả các ô, nếu ô đó có công thức thì Excel lại phải tính toán lại từ đầu, do vậy file sẽ chậm, và dung lượng cũng tăng lên đáng kể.
2/ Dùng công thức của Excel thì không nên tham chiếu quá nhiều hàng, chỉ nên là dữ liệu đến đâu thì tham chiếu đến đó chứ không nên tham chiếu cả cột, như kiểu (M1:M1000) thay cho (M:M)
3/ Thay vì copy dữ liệu của Sheet(DL) sang sheet(KHSX) rồi sau đó mới dùng Filter để lọc các giá trị =0 và giá trị ="-", và chọn toàn bộ kết quả lọc đó để xóa và một loại các định dạng khác, THÌ bạn lọc luôn trên ShDL với điều kiện là
Criteria1:="<>-", Operator:=xlAnd, Criteria2:="<>0". Sau đó copy và PasteSpecial Paste:=xlPasteValues. như vậy sẽ được hết công thức trong kết quả paste
Phần định dạng thì chỉ cần thế này là đủ:
Selection.Borders.LineStyle = 1 'xlNone ( 1=kẻ khung, xlNone= không kẻ)
Selection.EntireColumn.AutoFit.
Bạn làm theo tôi chỉ dẫn bảo đảm là code vừa ngắn vừa dễ đọc và tốc độ cũng tăng đáng kể
4/ Tại sao khi có được kết quả sau khi lọc bạn lại phải xóa đi 1 số cột. Để in bảng này ra chăng?
(việc xóa các cột này cũng như ở trên là xóa đi những dòng có giá trị ở cột M =0 hoặc ="-" -khoảng 12.000 dòng, thì code không chạy chậm mới là chuyện lạ, mà kể cả xóa bằng thủ công (bằng tay) cũng chậm, quay quay và đơ luôn).

Chúc bạn thành công.

 

[Hoàng Tuấn 868]

Kiến thức về VBA của tôi nông cạn và chắp vá còn non và xanh lắm

Bạn làm theo tôi chỉ dẫn bảo đảm là code vừa ngắn vừa dễ đọc và tốc độ cũng tăng đáng kể

Chả non, chả xanh tẹo nào.

 

[vancong28x3]

Kiến thức về VBA của tôi nông cạn và chắp vá còn non và xanh lắm, nhưng thấy bạn cũng có thái độ học hỏi nghiêm túc và qua đọc code của bạn, Tôi có 1 số lời khuyên bạn:
1/ Đã dùng VBA thì hạn chế dùng hàm của MS Excel, do mỗi lần bảng tính có sự thay đổi gì đó thì lại load lại tất cả các ô, nếu ô đó có công thức thì Excel lại phải tính toán lại từ đầu, do vậy file sẽ chậm, và dung lượng cũng tăng lên đáng kể.
2/ Dùng công thức của Excel thì không nên tham chiếu quá nhiều hàng, chỉ nên là dữ liệu đến đâu thì tham chiếu đến đó chứ không nên tham chiếu cả cột, như kiểu (M1:M1000) thay cho (M:M)
3/ Thay vì copy dữ liệu của Sheet(DL) sang sheet(KHSX) rồi sau đó mới dùng Filter để lọc các giá trị =0 và giá trị ="-", và chọn toàn bộ kết quả lọc đó để xóa và một loại các định dạng khác, THÌ bạn lọc luôn trên ShDL với điều kiện là
Criteria1:="<>-", Operator:=xlAnd, Criteria2:="<>0". Sau đó copy và PasteSpecial Paste:=xlPasteValues. như vậy sẽ được hết công thức trong kết quả paste
Phần định dạng thì chỉ cần thế này là đủ:
Selection.Borders.LineStyle = 1 'xlNone ( 1=kẻ khung, xlNone= không kẻ)
Selection.EntireColumn.AutoFit.
Bạn làm theo tôi chỉ dẫn bảo đảm là code vừa ngắn vừa dễ đọc và tốc độ cũng tăng đáng kể
4/ Tại sao khi có được kết quả sau khi lọc bạn lại phải xóa đi 1 số cột. Để in bảng này ra chăng?
(việc xóa các cột này cũng như ở trên là xóa đi những dòng có giá trị ở cột M =0 hoặc ="-" -khoảng 12.000 dòng, thì code không chạy chậm mới là chuyện lạ, mà kể cả xóa bằng thủ công (bằng tay) cũng chậm, quay quay và đơ luôn).

Chúc bạn thành công.

Cám ơn với những góp ý của bạn. Mình cũng đã tìm ra được cách để xử lý nhanh hơn 1 chút, nhưng cũng mới đi được 30% chặng đường.
Các vấn đề bạn nêu ở trên:
1 và 2 mình sẽ chú ý.
3. Mới đầu mình định giữ công thức để sau này mình đã ra bảng cuối cùng rồi thì chỉ cần nhập thay đổi số lượng, file sẽ tự tính, k phải chạy lại từ đầu. Nhưng hiện mình đã bỏ công thức và dùng mảng, lọc loại bỏ dòng có kết quả bằng 0 đã nhanh hơn rất nhiều (nay m mới học được cách sử dụng mảng , hihi)
vấn đề kẻ đúng là mình không biết xlNone nên mới để nh dòng - mình đã tìm để dùng 1 lệnh nhưng chưa tìm ra, nay bạn nói m mới biết
4. sau khi lọc xong mình xóa cột để định dạng bảng. Copy các mã vật tư giống nhau để tính ở sheet("xoa")
Mục đích dùng sheet("xoa") vì như sau
VD: hàng tồn kho còn 100 cái.
Nhưng tổng vật tư A cần dùng cho 5 mã hàng (MH) là 150 cái.
vật tư A của MH1 cần là 20
A của MH2 cần là 30
A của MH3 cần là 50
A của MH4 cần là 10
A của MH5 cần là 40
MH là thứ tự ưu tiên từ 1 đến 5. Nên hàng tồn kho sẽ trừ dần cho các MH. để xem MH ưu tiên nào cần bổ sung bao nhiêu
Như ví dụ này. MH4 bắt đầu thiếu. Cần cung cấp thêm.
Sau khi tính xong cho từng MH ở sheet("xoa") sẽ làm bảng tổng hợp ở sheet("CUVT") xem các mã nào thiếu để theo dõi mua hàng. đủ thì k cần quan tâm.
Đây là mục đích mình cần làm file này.
Rất vui khi được bạn góp ý.
Mình có gửi kết bạn qua zalo rồi. Tên Văn Công. bạn chú ý giúp nhé.
Cám ơn bạn nhiều.

 

[HUONGHCKT]

Chả non, chả xanh tẹo nào.

Cảm ơn anh đã đọc bài và tương tác.
Kiến thức về VBA nói riêng và Excel nói chung thì đúng là còn non và xanh lắm, thật mà.
Nhưng tuổi đời thì chín và gần rụng rồi.

 

[Hoàng Tuấn 868]

Kiến thức về VBA nói riêng và Excel nói chung thì đúng là còn non và xanh lắm, thật mà.

Có thể non và xanh với người này, nhưng với người khác thì lại không nhỉ.