Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Matched Cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Matched Cells
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Matched Cells
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" wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Matched Cells
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 "scott" wrote: 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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Matched Cells
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count matched cells | Excel Worksheet Functions | |||
Delete all rows if criteria not matched | Excel Discussion (Misc queries) | |||
looking to delete "non-matched" data | Excel Discussion (Misc queries) | |||
compare two text columns and find the closest matched pair of cells | Excel Programming | |||
Compare cells and grab if matched | Excel Programming |