View Full Version : Solved: Delete information in a cell which also exist in another cell
b4davidd
10-13-2007, 10:39 AM
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
10-13-2007, 11:07 AM
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
10-13-2007, 11:15 AM
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
10-13-2007, 03:50 PM
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
10-13-2007, 06:42 PM
Thanks a lot for your support!
i have solved the problem with the code!
many thanks
David
mdmackillop
10-14-2007, 01:49 AM
Hi David,
If this is solved, please mark it so using the Thread Tools dropdopwn.
Regards
MD
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.