Code VBA Lọc theo Mảng bị lỗi Đầy Bộ Nhớ. Xin cách Sửa code

[Văn Toàn 1996]

Chào tất cả Mọi Người trên diễn Đàn. Em đang dùng code Bên dưới để lọc 1 vùng có khoảng 800.000 dòng nhưng code báo lỗi đầy bộ nhớ như hình, em có thử 500.000 thì không có bị. Nhờ mọi người tư vấn cách sửa lỗi. Xin chân thành cảm ơn !

Sub testloc()
Dim sArr(), dArr(), Dk1 As String, I As Long, K As Long, R As Long, Col As Long, a As Long
a = 700000
sArr = Range("A1:S" & a).Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 19)
For I = 1 To R
    If sArr(I, 6) = 3 Then
        K = K + 1
        For Col = 1 To 19
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("V1:AN1000000").ClearContents
Range("V1").Resize(K, 19) = dArr
End Sub

 

[VetMini]

Chạy nhiều lần. Một lần từ 1 đến 400,000. Lần thứ hai 400.000 đến 800.000, vân vân
Hoặc chỉnh code chạy từ... đến...
Hoặc chỉnh chạy code 2 lần.

Đó là nếu bị ngay ở sArr
Nếu bị ở dArr thì chỉnh code lại chỉ xài sArr thôi

 

[Văn Toàn 1996]

Chạy nhiều lần. Một lần từ 1 đến 400,000. Lần thứ hai 400.000 đến 800.000, vân vân
Hoặc chỉnh code chạy từ... đến...
Hoặc chỉnh chạy code 2 lần.

Đó là nếu bị ngay ở sArr
Nếu bị ở dArr thì chỉnh code lại chỉ xài sArr thôi

Code báo lổi ngay dòng Sarr . Nhờ bạn giúp sửa code lại. xin chân thành cảm ơn

 

[bebo021999]

Code báo lổi ngay dòng Sarr . Nhờ bạn giúp sửa code lại. xin chân thành cảm ơn
Vì bạn khai báo nó là mảng array sArr()
Chỉ cần

Dim sArr

là đủ
Góp ý thêm:
Loop qua 800K dòng thì chua lắm. Có nhiều cách không dùng For, đơn cử:
- Range.Autofilter cột 6, với điều kiện =3.
- Sau đó set range.visible, gán 1 lần vào dArr

 

[Văn Toàn 1996]

Code báo lổi ngay dòng Sarr . Nhờ bạn giúp sửa code lại. xin chân thành cảm ơn

Vì bạn khai báo nó là mảng array sArr()
Chỉ cần

Dim sArr

là đủ

Cảm ơn anh. Em đã sửa nhưng code Vẫn báo Out Of memory ngay tại dòng Sarr

 

[bebo021999]

Cảm ơn anh. Em đã sửa nhưng code Vẫn báo Out Of memory ngay tại dòng Sarr

Bạn dùng Autofilter như mình gợi ý tại #4 nhé

 

[Văn Toàn 1996]

Bạn dùng Autofilter như mình gợi ý tại #4 nhé

Range.Autofilter mình chưa biết qua thuật toán này. Nhờ bạn chỉ giáo

 

[BuiQuangThuan]

Range.Autofilter mình chưa biết qua thuật toán này. Nhờ bạn chỉ giáo

Set arr thành range. Rồi dùng autofiter như bài 4

 

[hoanganhvo2612]

Chào tất cả Mọi Người trên diễn Đàn. Em đang dùng code Bên dưới để lọc 1 vùng có khoảng 800.000 dòng nhưng code báo lỗi đầy bộ nhớ như hình, em có thử 500.000 thì không có bị. Nhờ mọi người tư vấn cách sửa lỗi. Xin chân thành cảm ơn !

Sub testloc()
Dim sArr(), dArr(), Dk1 As String, I As Long, K As Long, R As Long, Col As Long, a As Long
a = 700000
sArr = Range("A1:S" & a).Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 19)
For I = 1 To R
    If sArr(I, 6) = 3 Then
        K = K + 1
        For Col = 1 To 19
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("V1:AN1000000").ClearContents
Range("V1").Resize(K, 19) = dArr
End Sub

View attachment 294729

... Mình nhầm

 

[Văn Toàn 1996]

Set arr thành range. Rồi dùng autofiter như bài 4

CÁI NÀY không thể dùng autofiter vì mình muốn code lọc xong xuất ra 1 Vùng khác

 

[VetMini]

CÁI NÀY không thể dùng autofiter vì mình muốn code lọc xong xuất ra 1 Vùng khác

1. Điểm này bạn quá chủ quan. Đâu có ai cấm fikter, copy visible qua vùng khác, rồi bỏ filter.
2. Không filter tại chỗ thì thì dùng Advanced Filter là đúng nhất.

 

[SA_DQ]

Viết theo chỉ đạo từ #2:

Sub LocVoiTri3()
 Dim Arr()
 Dim Rws As Long, DgC As Long, J As Long, W As Long, Col As Integer, Tmr As Double
 
 Tmr = Timer()
 Rws = Sheet4.[A2].CurrentRegion.Rows.Count
 DgC = Rws \ 2 + 1
 Arr() = [A1].Resize(DgC, 19).Value
 ReDim aKQ(1 To (DgC + DgC \ 2), 1 To 19)
 For J = 1 To UBound(Arr())
    If Arr(J, 6) = 3 Then
        W = W + 1
        For Col = 1 To 19
            aKQ(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
 Arr() = Range(Cells(DgC + 1, "A"), Cells(Rws, 19)).Value
 For J = 1 To UBound(Arr())
    If Arr(J, 6) = 3 Then
        W = W + 1
        For Col = 1 To 19
            aKQ(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
 MsgBox Timer() - Tmr, , W
End Sub

Kết quả từ file hơn 80 vạn dòng là 2.65gy & kết quả lọc ra được khoảng 26 700 dòng

 

[VetMini]

Bài này:
- Nếu muốn code kiểu Advanced Filter thì cứ record macro.
- Nếu nhất định dùng mảng thì phải sửa code nhiều. Mà tôi thì không thích theo cách viết trường phái GPE cho nên nếu phải sửa nhiều thì thà viết lại.

Viết theo chỉ đạo từ #2:
...

Code của bạn là điển hình có thể thu gọn bằng lệnh GoSub

GoGub LocArr
 Arr() = Range(Cells(DgC + 1, "A"), Cells(Rws, 19)).Value
GoGub LocArr
Range("V1:VN1000000").Clearcontent
Range("V1").Ressize(W, 19) = aKQ
 MsgBox Timer() - Tmr, , W
Exit Sub ' dòng này rất quan trọng, khong thể thiếu

LocArr:
 For J = 1 To UBound(Arr())
    If Arr(J, 6) = 3 Then
        W = W + 1
        For Col = 1 To 19
            aKQ(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
Return
End Sub

 

[SA_DQ]

Bài này: . . . .
Code của bạn là điển hình có thể thu gọn bằng lệnh GoSub
. . .

Xin bạn cho nhận xét hay khác biệt, nếu không xài như bạn nêu mà vầy:
Option Explicit
Dim Arr(), aKQ()
Dim W As Long, Col As Integer

Sub LocVoiTri3()
 Dim Rws As Long, DgC As Long, Tmr As Double
 
 Tmr = Timer()
 Rws = Sheet4.[A2].CurrentRegion.Rows.Count
 DgC = Rws \ 2 + 1
 Arr() = [A1].Resize(DgC, 19).Value
 ReDim aKQ(1 To (DgC + DgC \ 2), 1 To 19)
 MacroCon
 Arr() = Range(Cells(DgC + 1, "A"), Cells(Rws, 19)).Value
 MacroCon
 MsgBox Timer() - Tmr, , W
End Sub

Sub MacroCon()
 Dim J As Long

 For J = 1 To UBound(Arr())
    If Arr(J, 6) = 3 Then
        W = W + 1
        For Col = 1 To 19
            aKQ(W, Col) = Arr(J, Col)
        Next Col
    End If
 Next J
 Erase Arr()
End Sub

 

[VetMini]

Xin bạn cho nhận xét hay khác biệt, nếu không xài như bạn nêu mà vầy:
...

Đầu tiên, hai hàm ấy hoàn toàn độc lập nhau. A gọi B không có nghĩa là B phải biết A. Những gì A muốn B biết thì phải truyền qua tham số. Không có tham số thì bắt buộc các liên lạc phải qua biến toàn cục. Tôi nghĩ là bạn có khai biến toàn cục, nhưng cái bạn đưa lên không thấy phần ấy. Biến toàn cục có cái bất lợi của chúng. Điển hình là chúng làm giảm tính chất độc lập của hàm. Hai hàm A và B phải biết chính xác những gì tồn tại trong biến toàn cục. Điển hình là trường hợp code của bạn, A khi gọi B lần thứ nhất đã dựa vào thực tế rằng các biến toàn cục mang trị khởi đầu mặc định từ VBA; lần thứ hai thì A đã dựa vào thực tế rằng B đã được gọi 1 lần trước đó, các biến toàn cục tiếp tục mang trị mà chúng đã mang khi B kết thúc lần thứ nhất.
Bỏ qua phần ấy, hãy để ý rằng: A thay vì gọi B có thể gọi hàm khác thay thế, B chả sao cả nếu không được A gọi.
Mặt khác, A có thể bảo vệ biến của mình bằng cách chỉ truyền qua value.

Chính bản thân tôi cũng không thích lênh SoSub. Nhưng có một số người, nhất là dân viết Basic từ thế kỷ trước, vì các lý do sau đây:
- Khỏi phải khai báo lại thành biến toàn cục
- Sub dạng GoSub không phải là độc lập. Đấy là hàm nội của hàm chính. Tất cả tài nguyên (biến/hằng các hàm nội khác) đều được hàm nội này biết và toàn quyền sử dụng. Với hàm độc lập, gặp parameter list dài một chút thì truyền hơi mệt. Mà module chứa một đống biến toàn cục cũng hơi gay. Bởi hàm có thể khai báo biến nội che qua biến toàn cục cho nên hơi khó đọc code (*1). Trong trường hợp này, hàm nội giải quyết vấn đề code được lặp đi lặp lại nhiều lần một cách hiệu quả mà không cần tới vòng lặp.
- Code của hàm chính tự do chạy qua hàm nội nếu không có gì cản trở. Vì vậy, câu lệnh Exit Sub khi hết code hàm chính là tối quan trọng. Không có câu lệnh này, code sẽ chạy vào hàm nội và đưa đến run time error khi thấy lệnh Return.
- Vì Sub dạng GoSub không hoàn toàn độc lập cho nên lúc gọi VBA chỉ phải save địa chỉ lệnh gọi vào ngăn xếp và 'pop' ra lúc gặp lệnh Return. Khác với hàm đọc lập, VBA phải save một đống khác vào ngăn xếp. Vì vậy SoSub có khả năng nhanh hơn hàm độc lập.
- Ngày xưa, bộ nhớ không nhiều cho nên hầu hết các chương trình được viết dùng hàm nội. Đó là lý do tại sao các tay viết Basic thế kỷ trước thich viết kiểu này.

(*1) Có biến trùng tên với biến toàn cục được khai báo lại là biến nội thì VBA sẽ mặc định là hàm sử dụng biến nội.
Muốn VBA biết rằng hàm muốn dùng biến toàn cục thì thêm tiền tố là tên module nó được khai báo. Theo VBA thì biến/hằng toàn cục là properties của Module; biến/hằng khai báo trong sub/function là properties của sub/function.
Chạy thử code sau đây sẽ rõ:
Dim a
Sub tt()
Dim a
a = 10
Module1.a = 1
Debug.Print a, Module1.a
End Sub


Xin lỗi các bạn khác:
Cách hoạt động của compiler và tầm vực của biến là nơi mà tôi tự hào rằng kiến thức của mình không thua bất cứ người nào ở diễn đàn này.
Vì vậy gặp dịp thì hơi ngứa mồm một chút. Thôi thì cứ coi như ở điểm này tôi quá tự phụ đi.

 

[SA_DQ]

Mình cảm ơn bạn rất nhiều qua những gì bạn viết & những điều đó đã khai sáng thêm cho mình rất nhiều!
Mình thấy cách viết GoSub phức tạp hơn; không biết cách suy diễn như mình vậy có đúng hay không nữa?
Xin rất trân trọng & cảm ơn bạn!

 

[HeSanbi]

Bạn dùng hàm FilterV bên bài viết này thử xem sao

UDF FilterA và FilterV - Tận dụng bộ lọc AutoFilter và AdvancedFilter

HÀM UDF FilterA và FilterV bổ trợ tận dụng hai bộ lọc AutoFilter và AdvancedFilter Hôm nay tôi chia sẻ với các bạn hàm có thể gọi hai bộ lọc mà Application Excel hỗ trợ trong VBA hoặc phải cài đặt bằng tay trong Ribbon khá rối, thì nay đã có hàm bổ trợ này giúp các bạn thao tác gọn gàng và...

www.giaiphapexcel.com

Hoặc bạn tìm hiểu ADODB, cho dữ liệu lớn

 

[vmhieu.cdcs]

Lỗi này do khai báo biến, mình đã gặp trường hợp này 01 lần,
Đối với các coder chuyên nghiệp thì họ sẽ khắc phục lỗi này bằng cách sử dụng tiếp vĩ ngữ trong khai báo biến để thay thế
Code của bạn Dim a As Long thay thế bằng Dim a& thì sẽ hết lỗi