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)
"scott" <(E-Mail Removed)> wrote in message
news:uGta%(E-Mail Removed)...
> 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.
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>
>>
>
>
Similar Threads
