Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed - new user
Hello,
Please help me to write a code to accomplish the following: ..I have a list of account numbers listed in column A (starting from the second row or A2) ..I need the macro to go down the list (while there is still data) and determine if the account numbers are valid (against the list of invalid account numbers) ..And if it finds the invalid account number it should delete the entire row Of course I can determine the invalid accounts by using the VLookUp function and then manually delete the rows but it would be great if it can be down automatically (via macro). Please help and thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed - new user
Try this
With the list of account numbers in column A in Sheet1 And the list of invalid numbers in column A in Sheet2 Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 2 Step -1 If Application.CountIf(Sheets("Sheet2").Columns("A"), _ .Cells(r, "A").Value) 0 Then .Rows(r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "nat" wrote in message ... Hello, Please help me to write a code to accomplish the following: .I have a list of account numbers listed in column A (starting from the second row or A2) .I need the macro to go down the list (while there is still data) and determine if the account numbers are valid (against the list of invalid account numbers) .And if it finds the invalid account number it should delete the entire row Of course I can determine the invalid accounts by using the VLookUp function and then manually delete the rows but it would be great if it can be down automatically (via macro). Please help and thank you in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed - new user
Is there a way to avoid using Sheet2? Can I have a macro
that will loop from one record down to another until it is blank and compares the value (from that record) to the list of invalid values. But I do not want to save the list of invalid values in the same workbook. Is there a way to declare the list in the body of the macro itself? I'm not very familiar with Arrays or how to use them but an Array looks like a list of values. So if I can have a code that will take the value (let's say in cell A2), compare it to the list of values (saved in the macro) and delete the row if the match is not found, that would be great. Any suggestions? -----Original Message----- Try this With the list of account numbers in column A in Sheet1 And the list of invalid numbers in column A in Sheet2 Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows.Count To 2 Step -1 If Application.CountIf(Sheets ("Sheet2").Columns("A"), _ .Cells(r, "A").Value) 0 Then .Rows(r).Delete Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "nat" wrote in message ... Hello, Please help me to write a code to accomplish the following: .I have a list of account numbers listed in column A (starting from the second row or A2) .I need the macro to go down the list (while there is still data) and determine if the account numbers are valid (against the list of invalid account numbers) .And if it finds the invalid account number it should delete the entire row Of course I can determine the invalid accounts by using the VLookUp function and then manually delete the rows but it would be great if it can be down automatically (via macro). Please help and thank you in advance. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed - new user
This solution still requires to keep the list of values in
b1:b3 (which I'm trying to avoid). Plus the range in column A will be changing (row numbers can change). Is there are any other solution that will loop thru column A as long as there is data and compare the data to the list of invalid accounts (but this list should not be saved as a rangge in the workbook). Can it be coded in the body of the macro somehow? -----Original Message----- try this where b1:b3 has your list and a21:a37 has the rows to delete Sub DeleteRowsInList() For Each c In [b1:b3] With Columns(1) For r = 37 To 21 Step -1 With .Cells(r, 1) If .Value = c Then .EntireRow.Delete End With Next End With Next c End Sub -- Don Guillett SalesAid Software "nat" wrote in message ... Hello, Please help me to write a code to accomplish the following: .I have a list of account numbers listed in column A (starting from the second row or A2) .I need the macro to go down the list (while there is still data) and determine if the account numbers are valid (against the list of invalid account numbers) .And if it finds the invalid account number it should delete the entire row Of course I can determine the invalid accounts by using the VLookUp function and then manually delete the rows but it would be great if it can be down automatically (via macro). Please help and thank you in advance. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Needed - new user
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Forms - Tips needed to get started | Excel Discussion (Misc queries) | |||
User Forms - Tip needed to get started | Excel Discussion (Misc queries) | |||
User-friendly lookup solutions needed | Excel Discussion (Misc queries) | |||
user checklist formula needed XP | New Users to Excel | |||
code needed | Excel Programming |