Code Needed - new user

  • Thread starter Thread starter nat
  • Start date Start date
N

nat

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.
 
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
 
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)




"nat" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
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
 
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
(e-mail address removed)
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.
Click to expand...


.
Click to expand...
 
try this where your list is stored in an array

mm = Array(1, 2, 4)
For Each c In mm

--
Don Guillett
SalesAid Software
(e-mail address removed)
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
(e-mail address removed)
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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top