Background: I have four lists of product combinations for the project. Each of each combination of products / settings is available on our website. These four lists are for the four languages of our site.
Each textual description of the product / settings combination is separate in the database, and over the years certain languages with certain product / settings combinations have been found in the database. (i.e. there is no row for it in the SQL database, so an error appears on the site.)
Problem: I have four lists of more than 110,000 items with missing data, to simplify, say, I have only ten products.
list 1 (L1): 1, 2, 3, 5, 6, 7, 8, 10
L2: 1, 2, 3, 4, 5, 6, 8, 9
L3: 1, 3, 4, 5, 6, 8, 9, 10
L4: 1, 2, 3, 4, 5, 6, 8, 9, 10
Now I have these four lists in four columns in an Excel file. However, when I now try to execute the "For" loop through the first line all the way to the end (xlUp) .row ..., it freezes after about 6,000 entries. My processor is 99% Excel and amazingly memory is still about 1 GB free (out of 4 GB).
I tried to find other solutions here in Stack Overflow, and that led me to a function that compared the two options that had all the columns inside them. It was a type methodology For each x in arr. It also turned out to be impracticable, as my computer froze about 10,000 records.
Objective: . My goal in the example I gave is to have four smaller lists of missing entries for each language. In the example:
L1: 4, 9
L2: 7
L3: 2, 7
L4: 7
Two main issues that I disagree about:
( , , L1 , 7, , .)
: .
440 000 , , , DoEvents , Excel " ". DoEvents, , Excel .
, , , , , . .
Dim MyAr As Variant
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, n As Long, r As Long, j As Long
Dim Col As New Collection
Dim itm
Dim aCell As Range
Dim FinalList() As String
'~~> Let say this sheet has the 4 lists in Col A to D
Set ws = ThisWorkbook.Sheets("Sheet2")
With ws
'~~> Find the last Row in Col A to D which has data
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow = .Range("A:D").Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lRow = 1
End If
'~~> Create a unique list
Dim z As Variant
z = 0
For Each aCell In .Range("A1:D" & lRow)
If Len(Trim(aCell.Value)) <> 0 Then
On Error Resume Next
Col.Add aCell.Text, CStr(aCell.Text)
On Error GoTo 0
End If
z = z + 1
Debug.Print z
DoEvents
Next
'~~> Output Column Say in Col J
r = 10
'~~> Loop through the list to match
For j = 1 To 4
Set aCell = .Range(.Cells(1, j), .Cells(lRow, j))
MyAr = aCell.Value
z = 0
For Each itm In Col
If ItemExist(itm) = False Then
ReDim Preserve FinalList(n)
FinalList(n) = itm
n = n + 1
End If
z = z + 1
Debug.Print z
DoEvents
Next
'~~> Output The results
.Cells(1, r).Value = "Missing List in List" & j
On Error Resume Next
.Cells(2, r).Resize(UBound(FinalList) + 1, 1).Value = _
Application.WorksheetFunction.Transpose(FinalList)
On Error GoTo 0
r = r + 1
Erase FinalList
n = 0
Next
End With
End Sub
Function ItemExist(sVal As Variant) As Boolean
Dim i As Long
For i = 0 To UBound(MyAr) - 1
If sVal = MyAr(i + 1, 1) Then
ItemExist = True
Exit For
End If
Next
End Function