Clear cells based on values of adjacent cells (multiple columns)

[Che_Broludo]

Hello all,

I have searched the archives and have found people asking (and answering) similar questions—but none of them quite fit my situation. I will attempt to describe it as clearly as I can.

I have huge terminology bases containing many translations of many terms, in many languages. No single language has every term, some languages have more than one translation for the same term—and every term has been assigned a Reliability Code from 1-4, which exists in the cell immediately to its right. I need to eliminate all terms with a Reliability Code of 1 or 2.

It is one thing to use VBA to clear every adjacent cell, based on the value of the adjacent cell, when you have just two columns. I have dozens of columns—or rather, three columns repeated many dozens of times. The first column contains the unique identifier code for the term, the second column contains the term itself, and the third column contains the Reliability Code. Then the next column is the unique identifier for the next term, followed by the next term, followed by the next term's Reliability Code. Ad infinitum.

How can I clear the contents of every cell to the left of every cell containing a Reliability Code of 1 or 2?

Thank you for taking the time to read!

 

[Che_Broludo]

How big is a "larger one", i.e. what is the size of the current region range? Is the out of memory causing a run time error? If yes, what line is the debugger highlighting?

Sorry, didn't answer the whole question. The debugger is highlighting this line: If V(i, j) Like "reliabilityCode: 1*" Or V(i, j) Like "reliabilityCode: 2*" Then

 

[JoeMo]

I ran this on one of the "larger" ones (but by no means the largest): Range("A1").CurrentRegion.Select and the cell count came to 9,510,079.

Can you answer my other questions?

 

[JoeMo]

Sorry, didn't answer the whole question. The debugger is highlighting this line: If V(i, j) Like "reliabilityCode: 1*" Or V(i, j) Like "reliabilityCode: 2*" Then

Don't see why that line would be causing a problem, but no matter, it's clear that your larger files are eating up too much memory.

To deal with that I propose the data range (variable R in the code) be partitioned into "sub-ranges" that get processed one at a time. This will extend the run time, but hopefully not to an intolerable level. The code for this (below) is untested so may need some tweaking. If it works, I would very much appreciate you replying with at least a qualitative measure of the execution time for at least one of the data sets that fall into the "larger ones". You can experiment with the value of the constant N in the code to see if it impacts execution time significantly.

Sub Che_BroludoPartitionRange()
Const N As Long = 10   'Number of sub-ranges to partition R into. Adjust to suit
Dim R As Range, Rpart() As Variant, NumRws As Long, x As Long, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
R.Select
Application.ScreenUpdating = False
NumRws = Application.RoundUp(R.Rows.Count / N, 0)
ReDim Rpart(1 To N)
For x = 1 To N
    If x = 1 Then
        Set Rpart(x) = Range(R.Cells(x + (x - 1) * NumRws, 1), R.Cells(x * NumRws, R.Columns.Count))
    Else
        Set Rpart(x) = Range(R.Cells((x - 1) * NumRws + 1, 1), R.Cells(x * NumRws, R.Columns.Count))
    End If
    V = Rpart(x).Value
    For i = LBound(V, 1) To UBound(V, 1)
        For j = 4 To UBound(V, 2) Step 3
            If V(i, j) Like "reliabilityCode: 1*" Or V(i, j) Like "reliabilityCode: 2*" Then
                V(i, j - 1) = ""
                V(i, j - 2) = ""
            End If
        Next j
    Next i
    Rpart(x).Value = V
    Erase V
Next x
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Sub Che_BroludoPartitionRange()
Const N As Long = 10   'Number of sub-ranges to partition R into. Adjust to suit
Dim R As Range, Rpart() As Variant, NumRws As Long, x As Long, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
R.Select
Application.ScreenUpdating = False
NumRws = Application.RoundUp(R.Rows.Count / N, 0)
ReDim Rpart(1 To N)
For x = 1 To N
    If x = 1 Then
        Set Rpart(x) = Range(R.Cells(x + (x - 1) * NumRws, 1), R.Cells(x * NumRws, R.Columns.Count))
    Else
        Set Rpart(x) = Range(R.Cells((x - 1) * NumRws + 1, 1), R.Cells(x * NumRws, R.Columns.Count))
    End If
    V = Rpart(x).Value
    For i = LBound(V, 1) To UBound(V, 1)
        For j = 4 To UBound(V, 2) Step 3
            If V(i, j) Like "reliabilityCode: 1*" Or V(i, j) Like "reliabilityCode: 2*" Then
                V(i, j - 1) = ""
                V(i, j - 2) = ""
            End If
        Next j
    Next i
    Rpart(x).Value = V
    Erase V
Next x
Application.ScreenUpdating = True
End Sub

This will not solve the OP's memory issues, but I wanted to point out that the red highlighted line of code above can be written in a more compacted form...

If V(i, j) Like "reliabilityCode: [12]*" Then

The square brackets in a Like comparison stands in for a single character which can be any one of the characters listed within it.

 

[Che_Broludo]

Don't see why that line would be causing a problem, but no matter, it's clear that your larger files are eating up too much memory.

To deal with that I propose the data range (variable R in the code) be partitioned into "sub-ranges" that get processed one at a time. This will extend the run time, but hopefully not to an intolerable level. The code for this (below) is untested so may need some tweaking. If it works, I would very much appreciate you replying with at least a qualitative measure of the execution time for at least one of the data sets that fall into the "larger ones". You can experiment with the value of the constant N in the code to see if it impacts execution time significantly.

Sub Che_BroludoPartitionRange()
Const N As Long = 10   'Number of sub-ranges to partition R into. Adjust to suit
Dim R As Range, Rpart() As Variant, NumRws As Long, x As Long, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
R.Select
Application.ScreenUpdating = False
NumRws = Application.RoundUp(R.Rows.Count / N, 0)
ReDim Rpart(1 To N)
For x = 1 To N
    If x = 1 Then
        Set Rpart(x) = Range(R.Cells(x + (x - 1) * NumRws, 1), R.Cells(x * NumRws, R.Columns.Count))
    Else
        Set Rpart(x) = Range(R.Cells((x - 1) * NumRws + 1, 1), R.Cells(x * NumRws, R.Columns.Count))
    End If
    V = Rpart(x).Value
    For i = LBound(V, 1) To UBound(V, 1)
        For j = 4 To UBound(V, 2) Step 3
            If V(i, j) Like "reliabilityCode: 1*" Or V(i, j) Like "reliabilityCode: 2*" Then
                V(i, j - 1) = ""
                V(i, j - 2) = ""
            End If
        Next j
    Next i
    Rpart(x).Value = V
    Erase V
Next x
Application.ScreenUpdating = True
End Sub

I have successfully run that code on three "larger" spreadsheets:

The first had 384,497 terms in 26 languages, and a range of 9,510,00; for N = 10 it completed in about three minutes.

The second had 476,420 terms in 26 languages, and a range of 44,149,000; for N = 10 it immediately gave the memory error. I doubled N to 20, and it completed after about an hour. For me, that is still within the tolerable range, as I could continue working on other, non-Excel tasks in the meantime.

The third had 653,111 terms in 26 languages, and a range of 19,581,565; for N = 10 it completed in about ten minutes.

The rest of the files fall somewhere between the second and the third in terms of the number of terms (which apparently has only a tenuous relationship to the cell range). I believe we can consider the matter resolved for all of the remaining files except one, which contains some 1.8 million terms in 26 languages. God only knows what the range will be for that behemoth. It's so large that our server times out when I try to upload the whole thing, so I'll probably be doing it piecemeal, which will sidestep the memory issue.

Thank you so much for your help! If I notice anything else interesting, I'll report it.

 

[JoeMo]

I have successfully run that code on three "larger" spreadsheets:

The first had 384,497 terms in 26 languages, and a range of 9,510,00; for N = 10 it completed in about three minutes.

The second had 476,420 terms in 26 languages, and a range of 44,149,000; for N = 10 it immediately gave the memory error. I doubled N to 20, and it completed after about an hour. For me, that is still within the tolerable range, as I could continue working on other, non-Excel tasks in the meantime.

The third had 653,111 terms in 26 languages, and a range of 19,581,565; for N = 10 it completed in about ten minutes.

The rest of the files fall somewhere between the second and the third in terms of the number of terms (which apparently has only a tenuous relationship to the cell range). I believe we can consider the matter resolved for all of the remaining files except one, which contains some 1.8 million terms in 26 languages. God only knows what the range will be for that behemoth. It's so large that our server times out when I try to upload the whole thing, so I'll probably be doing it piecemeal, which will sidestep the memory issue.

Thank you so much for your help! If I notice anything else interesting, I'll report it.

You are welcome. Thanks for the summary of your results. I rarely get feedback for run time results with such large spreadsheets.