Delete Matched Cells

[scott]

LISTING 1 below shows a range of A1:C9 where the values in column C are a
formula that finds values that are equal in column A and B. I need a way to
go down column A and B and delete any cells that are "matched" in Column C.
The trick here is once a cell is deleted in Column A for example that has a
match in Column B, I must remember that value because the value will no
longer exist in Column C because it's values are derived by a matching
formula.

Can someone help me create a Do While statement that goes down Column C and
deletes any cells in Column A and B that appear in Column C?

Note: The number of rows will constantly be changing, so the Range will
always vary. Also, Row 2 is blank, but I have a line shown below.

Any help would be appreciated.


LISTING 1:

BOL Billed Matched
--------------------------------
199111 196711
198199 198199
198100 198100 198100
196699
196711 196711
196699 196699
196694 198199

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim FindRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow
If .Cells(i, "A").Value <> "" Then
FindRow = 0
On Error Resume Next
FindRow = Application.Match(.Cells(i, "A"), .Columns(3), 0)
On Error GoTo 0
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "A").Value = ""
End If
End If
If .Cells(i, "B").Value <> "" Then
FindRow = 0
On Error Resume Next
FindRow = Application.Match(.Cells(i, "B"), .Columns(3), 0)
On Error GoTo 0
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "B").Value = ""
End If
End If
Next i

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
If LastRow >= i Then
For i = i To LastRow
On Error Resume Next
FindRow = Application.Match(.Cells(i, "B"), .Columns(3), 0)
On Error GoTo 0
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "B").Value = ""
End If
Next i
End If

End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[scott]

Thanks, it does the job, but can you give me a brief description of the flow
of action for this code? I'd like to learn a little from it, but I'm not
that familiar with some of this syntax.

If you have any links to sites that would help me learn how to move around a
sreadsheet, they would be very appreciated.

 

[JLGWhiz]

Hi Scott, don't know exactly what you are looking for in the way of help to
move around the spreadsheet, but this site gives some very basic information
on how to write macros.

http://www.excel-vba.com/excel-vba-contents.htm

 

[Bob Phillips]

Here's the code with some annotation

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim FindRow As Long

With ActiveSheet

'work out where the last row of data is
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'simple loop from the first to last row
For i = 1 To LastRow
'if column A for this row isn't blank
If .Cells(i, "A").Value <> "" Then
'use the Excel MATCH function to see if it exists in column
C
FindRow = 0
On Error Resume Next
FindRow = Application.Match(.Cells(i, "A"), .Columns(3), 0)
On Error GoTo 0
'if it does then pastevalue to the C value to
' and clear the value in column A
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "A").Value = ""
End If
End If
'then do the same stuff for column B
If .Cells(i, "B").Value <> "" Then
FindRow = 0
On Error Resume Next
FindRow = Application.Match(.Cells(i, "B"), .Columns(3), 0)
On Error GoTo 0
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "B").Value = ""
End If
End If
Next i

'and finally we just check in case there are still some left in
column B
' and process them as before
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
If LastRow >= i Then
For i = i To LastRow
On Error Resume Next
FindRow = Application.Match(.Cells(i, "B"), .Columns(3), 0)
On Error GoTo 0
If FindRow > 0 Then
.Cells(FindRow, "C").Value = .Cells(FindRow, "C").Value
.Cells(i, "B").Value = ""
End If
Next i
End If

End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)