NEED HELP ON CREATING DELETE MACRO FOR TWO COLUMNS
Posted by YANECKC on December 10, 2001 12:52 PM
I NEED TO CREATE A DELETE MACRO.
EXCEL SHEET LOOKS LIKE BELOW.
================================
ID ACCOUNT
1123456 0991234519
1123456 0991234527
1123456 1234567890
1123456 2345678901
1123456 3456789012
2222222 0000000711
2222222 0000000810
3333333 0000000710
3333333 0991234519
3333333 0991234527
3333333 1236890646
3333333 2234567890
4444444 0991234519
4444444 0991234527
5555555 7678790754
6666666 0000000071
6666666 0000000711
6666666 0991234519
6666666 0991234527
7777777 0000000710
7777777 0000000810
7777777 0991234519
7777777 0991234527
7777777 1234567890
7777777 3456789098
8888888 0991234519
8888888 0991234527
9999999 5678799808
----------------------------------------------
I NEED TO DELETE ROW IF THERE ARE TWO SAME ID ONLY AND ACCOUNT =0991234519 AND 0991234527.
THAT MEANS ID 7777777 MET THAT REQUIREMENT AND THE MACRO WOULD DELETE
THOSE TWO ROWS. THE NEXT ID TO MET THOSE REQUIREMENT IS 88888888 AND THOSE TWO ROWS WOULD BE DELETED.
THE SHEET WOULD LOOK LIKE BELOW AFTER THE DELETE MACRO WAS RUN.
================================
ID ACCOUNT
1123456 0991234519
1123456 0991234527
1123456 1234567890
1123456 2345678901
1123456 3456789012
2222222 0000000711
2222222 0000000810
3333333 0000000710
3333333 0991234519
3333333 0991234527
3333333 1236890646
3333333 2234567890
5555555 7678790754
6666666 0000000071
6666666 0000000711
6666666 0991234519
6666666 0991234527
7777777 0000000710
7777777 0000000810
7777777 0991234519
7777777 0991234527
7777777 1234567890
7777777 3456789098
9999999 5678799808
----------------------------------------------
YANECKC
Posted by Damon Ostrander on December 11, 2001 12:18 AM
Hi Yaneckc,
Can you clarify why id 1123456 on the first two lines did not meet the criteria you described?
Damon
Posted by YANECKC on December 11, 2001 6:54 AM
DAMON
1123456 DID NOT MEET THAT CRITERIA BECAUSE THERE WERE THREE OTHER ACCOUNTS (1234567890,2345678901,3456789012)
ONLY TIME YOU DELETE A ROW IF ID HAS ONLY TWO ACCOUNTS AND THEY MUST BE 0991234519 AND 0991234527.
YANECKC
Posted by Damon Ostrander on December 11, 2001 12:21 PM
Hi again Yaneckc,
Thanks for clarifying. Here's a macro:
Sub DelAccounts()
' Deletes accounts 0991234519 and 0991234527 if they have same ID
' and no other accounts with same ID. IDs assumed to be in column 1,
' Account numbers in column 2 starting at row 2 on the active worksheet.
Dim iRow As Long
iRow = 3
Do
If Cells(iRow, 2) = "0991234527" Then
' check previous account
If Cells(iRow - 1, 2) = "0991234519" Then
' finally check to see if any other accounts with same ID
If Cells(iRow - 2, 1) <> Cells(iRow, 1) And _
Cells(iRow + 1, 1) <> Cells(iRow, 1) Then
' delete these accounts
Rows(iRow).Delete
Rows(iRow - 1).Delete
iRow = iRow - 2
End If
End If
End If
iRow = iRow + 1
Loop Until IsEmpty(Cells(iRow, 1))
End Sub
Happy computing.
Damon
Posted by YANECKC on December 11, 2001 5:37 PM
iRow = 3 Do If Cells(iRow, 2) = "0991234527" Then ' check previous account If Cells(iRow - 1, 2) = "0991234519" Then ' finally check to see if any other accounts with same ID If Cells(iRow - 2, 1) <> Cells(iRow, 1) And _ Cells(iRow + 1, 1) <> Cells(iRow, 1) Then ' delete these accounts Rows(iRow).Delete Rows(iRow - 1).Delete iRow = iRow - 2 End If End If End If iRow = iRow + 1 Loop Until IsEmpty(Cells(iRow, 1))
THANK YOU DAMON. THAT IS EXACTLY WHAT I WAS LOOKING FOR.
YANECK
