Solved: Delete information in a cell which also exist in another cell

[b4davidd]

Hey Guys

Can someone help me with the following problem. I would like to delete information in a cell which also is existent in another cell.

I have two different cases which I would love to solve all in one go

Situation 1

Cell A1
HSBC
Cell B1
HSBC

-> I want HSBC to be deleted in cell B1

Situation 2
Cell A2
HSBC;
Arab Bank Group
Cell B2
Sumitomo Mitsui Banking Corp;
HSBC;
Arab Bank Group

-> I only want HSBC; and Arab Bank Group; to be deleted. Sumitomo Mitsui Banking Corp should remain in B2


A remark:
1) The cell information contain line breaks (divided with ENTER)

[Oorang]

The most important thing here to remember is that when you delete a row, all the rows shift up, so when if you loop from top to bottom, and you delete row 4, row 5 becomes 4. So when you examine 5 next you are actually looking at 6 having skipped 5. You can dodge this issue by going from the bottom to the top. Try this out:
Option Explicit

Public Sub RemoveUnwantedRows()
'-------------------------------------------------------------------------------
' Procedure : RemoveUnwantedRows
' DateTime : 10/13/2007 01:58 PM 13:58
' Author : Aaron Bush
' Purpose : Remove rows in ective sheet when value in column A is the same as
' value in column B.
'-------------------------------------------------------------------------------
On Error GoTo Err_Hnd
Const lngStep_c As Long = -1
Const lngClmnA_c As Long = 1
Const lngClmnB_c As Long = 2
Dim ws As Excel.Worksheet
Dim lngRow As Long
Dim lngTopRow As Long
Dim lngBtmRow As Long
With Excel.Application
.EnableEvents = False
.ScreenUpdating = False
.Cursor = xlWait
.EnableCancelKey = xlErrorHandler
.StatusBar = "Working..."
End With
Set ws = Excel.ActiveSheet
With ws.UsedRange
lngTopRow = .Row
lngBtmRow = .Rows.Count
End With
For lngRow = lngBtmRow To lngTopRow Step lngStep_c
If ws.Cells(lngRow, lngClmnA_c).Value = ws.Cells(lngRow, _
lngClmnB_c).Value Then
ws.Rows(lngRow).Delete
End If
Next
Exit_Proc:
On Error Resume Next
With Excel.Application
.EnableEvents = True
.ScreenUpdating = True
.Cursor = xlDefault
.EnableCancelKey = xlInterrupt
.StatusBar = False
End With
Exit Sub
Err_Hnd:
VBA.MsgBox "Error " & VBA.Err.Number & _
" in procedure RemoveUnwantedRows of Module Module1" & vbNewLine & _
VBA.Err.Description, vbMsgBoxSetForeground Or vbSystemModal, _
"Error - VBAProject.Module1.RemoveUnwantedRows"
Resume Exit_Proc
End Sub

[b4davidd]

Dear Aaron

Thanks a lot for your answer. Your code works perfect for the solution of one of my problems.

However as you can see I have just changed the thread a little bit cause I just got new information and realized that the problem is a little bit more complex. And i try to solve the whole problem in one go.

So actually i dont need to delete the row anymore, but only information in the Cell from column B that is also existing in the Cell of column A.

I am really really sorry for kinda wasting your time with answering my first thread.

Do you think you have an answer for changed thread? (and I wont change it again!! PROMISED!)

Sorry once more!!

[Oorang]

OK well that's no problem, you should be able to edit the code I posted above to do that. Just use the ClearContents method instead of delete. All you really need to do is send it through a series of "IF" statments to see if your condition is met. Then clear the cell.

[b4davidd]

Thanks a lot for your support!

i have solved the problem with the code!

many thanks

David

[mdmackillop]

Hi David,
If this is solved, please mark it so using the Thread Tools dropdopwn.
Regards
MD