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!

 

[JoeMo]

Welcome to the forum!

You can give this macro a try. Assumes your data begin in A1 so reliability code columns are C, F, I, .... Assumes there are no formula cells in your data.

Sub Che_Broludo()
'Assumes first cell/column is A1/A:A
Dim R As Range, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
V = R.Value
For i = LBound(V, 1) To UBound(V, 1)
    For j = 3 To UBound(V, 2) Step 3
        If V(i, j) Like "*1*" Or V(i, j) Like "*2*" Then
            V(i, j - 1) = ""
            V(i, j - 2) = ""
        End If
    Next j
Next i
Application.ScreenUpdating = False
R.Value = V
Application.ScreenUpdating = True
End Sub

 

[Che_Broludo]

Thank you so much, Joe!

I was excited to receive your reply, and I'm feeling optimistic. However, I neglected to include two vital pieces of information:

1) My data (for these purposes) begin at B1. A1 contains a master ID code for the "entry" to which all of the multilingual "terms" in that row apply. It is not repeated; it only appears in A1. But this is easily remedied by changing Range("A1") to Range("B1"), correct?

2) The cells containing the Reliability Codes, alas, do not merely contain a number from 1-4. They look like this:

[TABLE="width: 338"]
<tbody>[TR]
[TD="width: 338"]reliabilityCode: 3
subjectField: 1631, 2826

For my purposes, nothing in that cell matters except the single-digit number that follows "reliabilityCode."

Is there a way to tweak that code?

Thank you again![/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

Thank you so much, Joe!

I was excited to receive your reply, and I'm feeling optimistic. However, I neglected to include two vital pieces of information:

1) My data (for these purposes) begin at B1. A1 contains a master ID code for the "entry" to which all of the multilingual "terms" in that row apply. It is not repeated; it only appears in A1. But this is easily remedied by changing Range("A1") to Range("B1"), correct?

2) The cells containing the Reliability Codes, alas, do not merely contain a number from 1-4. They look like this:

[TABLE="width: 338"]
<tbody>[TR]
[TD="width: 338"]reliabilityCode: 3
subjectField: 1631, 2826

For my purposes, nothing in that cell matters except the single-digit number that follows "reliabilityCode."

Is there a way to tweak that code?

Thank you again![/TD]
[/TR]
</tbody>[/TABLE]

You are welcome.

Easily remedied, but not by changing A1 to B1. Replace the code I posted earlier with the code below and let's see if it works for you.

Sub Che_Broludo()
'Assumes first cell/column is A1/A:A. First ReliabilityCode column is D
'ReliabilityCode column repeats every 3 columns
Dim R As Range, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
V = R.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
Application.ScreenUpdating = False
R.Value = V
Application.ScreenUpdating = True
End Sub

 

[Che_Broludo]

You are welcome.

Easily remedied, but not by changing A1 to B1. Replace the code I posted earlier with the code below and let's see if it works for you.

Sub Che_Broludo()
'Assumes first cell/column is A1/A:A. First ReliabilityCode column is D
'ReliabilityCode column repeats every 3 columns
Dim R As Range, V As Variant, i As Long, j As Long
Set R = Range("A1").CurrentRegion
V = R.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
Application.ScreenUpdating = False
R.Value = V
Application.ScreenUpdating = True
End Sub

Thank you again! I did discover in the meantime that the A1/B1 swap was not at all the answer. Instead, I found a rather crude (but effective) workaround: I just copied the contents of A1 and deleted the whole column. Then I used find and replace to eliminate "reliabilitycode:" and a find-and-replace-plus-asterisk to eliminate "subjectfield:" and everything after it. Having thus brought the columns "in line" and eliminating everything but the 1-4 digit, your original code worked like a dream.

But I still have a lot of these left to work through, so I will be very grateful if your latest suggestion does the trick. I will let you know!

 

[Che_Broludo]

It works! Thank you!

 

[JoeMo]

It works! Thank you!

You are welcome - thanks for the reply.

 

[Che_Broludo]

Dear JoeMo,

It's working beautifully for the smaller files, but for the larger ones, I'm getting the "Out of memory" error. Any suggestions?

 

[JoeMo]

Dear JoeMo,

It's working beautifully for the smaller files, but for the larger ones, I'm getting the "Out of memory" error. Any suggestions?

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?

 

[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?

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