Clear the cell contain from one cell if another cell matches starting 4 texts

aayaanmayank

Board Regular
Joined
Jul 20, 2018
Messages
157
Hi All, can any one help me on this i have two columns if column A starting 4 texts match with column B starting 4 texts then clear the contain from cell which is in column A

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 210"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]MIAMIFLORIDAUSA088317434[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD]MIAMI[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 210"]
<tbody>[TR]
[/TR]
[TR]
[TD]PORTLOREGONUSA972082168[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]PORTLAND[/TD]
[/TR]
[TR]
[TD][TABLE="width: 210"]
<tbody>[TR]
[TD]SUITE1500DISTRICT1018
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]CLEVELAND[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try this VBA code:
Code:
Sub MyClear()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows of data
    For r = 1 To lr
'       Check to see if first 4 characters of columns A and B match
        If Left(Cells(r, "A"), 4) = Left(Cells(r, "B"), 4) Then
'           Clear entry in column A
            Cells(r, "A").ClearContents
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Last edited:
Upvote 0
Thanks for your help..

i have made this code, but every time after certain run i get Run time 6 OVERFLOW error.

lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row


For U = 2 To lastrow1

Set MYNAME2 = Cells(U, "J")
Set MYNAME3 = Cells(U, "L")
MY = Left(MYNAME2, Len(MYNAME2) * 0.3)

x = Len(MY)
y = Left(MYNAME3, x)
If y = MY Then


shgroup.Cells(U, "j").Clear
Else:
End If
Next U
 
Upvote 0
The code you posted looks quite different form what you asked.

Which line gives you the error?
What are your values of MYNAME2 and MYNAME3 when you get the error?

What is "shgroup" and how it is being defined?
Why did you not include that "shgroup" reference in setting MYNAME2 and MYNAME3, but used it everywhere else?
Note that if you are on a different sheet when the code is run, MYNAME2 and MYNAME would pull from the active sheet, and not "shgroup".
 
Upvote 0
Which line gives you the error? MY = Left(MYNAME2, Len(MYNAME2) * 0.3)
What are your values of MYNAME2 and MYNAME3 when you get the error? these two cell contains address so it might be text or string or both.
What is "shgroup" and how it is being defined? Sheet name Set shgroup = ThisWorkbook.Worksheets("Grouping Data_DQ")MYNAME2 & MYNAME2 are running from shgroup which is active
 
Upvote 0
Does it make any difference if you change these lines:
Code:
[COLOR=#333333]Set MYNAME2 = Cells(U, "J")[/COLOR]
[COLOR=#333333]Set MYNAME3 = Cells(U, "L")[/COLOR]
To this:
Code:
[COLOR=#333333]Set MYNAME2 = [/COLOR][COLOR=#333333]shgroup.[/COLOR][COLOR=#333333]Cells(U, "J")[/COLOR]
[COLOR=#333333]Set MYNAME3 = [/COLOR][COLOR=#333333]shgroup.[/COLOR][COLOR=#333333]Cells(U, "L")[/COLOR]

If not, try running this variation of the code, and let me know what the message box returns:
Code:
Sub MyMacro()

    Dim shgroup As Worksheet
    Dim lastrow1 As Long, U As Long
    Dim MYNAME2 As Range, MYNAME3 As Range
    Dim MY As String
    Dim x As Long
    Dim y As String
    
    Set shgroup = ThisWorkbook.Worksheets("Grouping Data_DQ")

    lastrow1 = shgroup.Range("B" & Rows.Count).End(xlUp).Row

    On Error GoTo err_chk
    
    For U = 2 To lastrow1
        Set MYNAME2 = Cells(U, "J")
        Set MYNAME3 = Cells(U, "L")
        MY = Left(MYNAME2, Len(MYNAME2) * 0.3)
        x = Len(MY)
        y = Left(MYNAME3, x)
        If y = MY Then
            shgroup.Cells(U, "j").Clear
        Else
        End If
    Next U

    On Error GoTo 0
    
    Exit Sub
    
err_chk:
    MsgBox "U = " & U & vbCrLf & "MYNAME2 = " & MYNAME2 & vbCrLf & "MYNAME3 = " & MYNAME3, vbOKOnly, "ERROR!"

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top