Find customer number and delete row

S

serhat

Excel Gurus et al,

I need your help. I want to set up a macro that searches for a custome
number in column G and when it finds the customer number it deletes th
row. Could someone
please help me with the code.

So it would basically search for column G for cust number 29752, 2975
and 29788 and when it would find these numbers it would delete th
entire row they reside in.

Thanks a lot!

S
 
J

Jason Morin

Try this macro:

Sub DeleteRows()
Dim iLastRow As Integer
Dim i As Integer

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 1 Step -1
With Cells(i, "G")
If .Value = 29752 Or _
.Value = 29755 Or _
.Value = 29788 Then
.EntireRow.Delete
End If
End With
Next i

Application.ScreenUpdating = True

End Sub

--

Press ALT+F11, Insert > Module, and paste in the above
macro. Then flip back to XL and run the macro.

HTH
Jason
Atlanta, GA
 
S

serhat

Jason!!

Thanks a lot mate. That worked like a charm. Have a great weekend.

Serha
 
S

serhat

Jason or anybody.. The code works fine when the customer number exists.
but when it doesnt exist a type mismatch error occurs and the macr
chokes.

Could you help me with a fix to get around this?

Thanks
 
D

Dave Peterson

I don't think that was the problem.

About the only mismatches I've seen with this type of code is when you have
errors (#n/a, #ref!,...) in the cell.

You can avoid comparisons like:

Option Explicit

Sub DeleteRows()
Dim iLastRow As Long
Dim i As Long

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = iLastRow To 1 Step -1
With Cells(i, "G")
If IsError(.Value) Then
'do nothing
Else
If .Value = 29752 Or _
.Value = 29755 Or _
.Value = 29788 Then
.EntireRow.Delete
End If
End If
End With
Next i

Application.ScreenUpdating = True

End Sub

Notice that I changed Jason's Integers to Longs, too.
 
S

serhat

Dave I tried what you wrote but I am still getting the mismatch error.
have tried various combinations of your code but all to no avail.

I have attached the test file that I work with. Could you, or anyon
else, try running the code and see if I am doing something obviousl
wrong.

The tab macro run sheet contains the data while the data backup shee
contains a copy of the data. The macro apparently runs and deletes th
customer rows successfully, but when it is done is gives me
"MISMATCH" error.

Thanks for everyones help. I appreciate it.

Serha
 
D

Dave Peterson

I don't open files--attachments or links to files.

When you step through the code (using F8's), what does the offending cell have
as value?
 
S

serhat

The value is the title of the column, "Customer".
It goes through the loops and deletes all the customer rows and choke
during the last record.

Could you please tell me how to start the search from row 2 instead o
row 1.

Thanks.

Serha
 
S

serhat

The value is the title of the column, "Customer".
It goes through the loops and deletes all the customer rows and choke
during the last record.

Could you please tell me how to start the search from row 2 instead o
row 1.

Thanks.

Serha
 
S

serhat

I figured it out Dave. Just changed the Loop to be from last row to
instead of 1.

Thanks a lot for all of your help. I really appreciate it
 
D

Dave Peterson

Glad you found the answer.

serhat < said:
I figured it out Dave. Just changed the Loop to be from last row to 2
instead of 1.

Thanks a lot for all of your help. I really appreciate it.
Click to expand...
 
Top