Excel 2007 Macro to delete rows

Did you mean to attach a sample?  

It's not clear whether column G contains a list of all suppliers or if column g contains only the suppliers to delete.

Let's say your supplier code for each row appear in column A
in cells J1, type the formula:
=IF(COUNTIF(G:G;K1)>0;1;0)
It will be used as a test to check if the line which number will be put in K1 has to be deleted or not. It counts the times the value in K1 appears in column G.

Then, in a Macro, type:

For i = 1 to 50000
   Cells(1,11).Value = Cells(i,1).Value   ' Copies the current supplier code to K1 to test
   If Cells(1,10)=1 then   ' If the column has to be deleted
      Range("A" & i & ":" & "F" & i).Select   ' Stop at column F not to erase the Suppliers column in G
      Selection.Delete Shift:= xlUp
      i= i-1   ' Move up 1 row because all lines below i moved up too with the delete
   End If
Next i

Select allOpen in new window

Adapt the code in function of which column has the supplier code ...

Hi, I should have explained a little better.

I'm attaching a sample file to make it easier. 1st tab has a 4 stock items and 2nd tab the suppliers code which I want to delete.

What I need is a Macro that reads from the suppliers code and if there is a positive match between any of those codes and the values in column G (in the stock list) then delete the whole row.

In my sample the macro should delete rows 1, 3 and 4 and but not 2.

Thanks,

Fernando

Hi

I assume your data in A1:G50000 on Sheet1

Your supplier list (368) in L2:L369

K1 must be empty

in K2

=NOT(ISNUMBER(MATCH(G2,$L$2:$L$369,0)))

Insert a worksheet

Go to Data > Advanced Filter > Check on Copy to another location

List Range: Sheet1!$A$1:$G$50000
Criteria Range: Sheet1!$K$1:$K$2

Copy to: A1

Click on OK.


Note: adjust the sheet name

Kris

Sorry, I thought I attached the sample file before.

Attached now.

Fernando
Sample.xlsx

Hello Buttersk,

Thanks for your help. I can see it works perfectly fine in your sample, but for some reason I can't make it work in my document.

My real document is pretty much the same, but the Stock List tab has many more columns and the one holding the supplier's code is column 34

This is the adjusted code:


Sub DeleteSuppliers()

    Dim myLastCell As Range
    Dim i As Long
   
   
    Set myLastCell = LastCell(Sheets("Stock List"))
   

    For i = myLastCell.Row To 1 Step -1
       If Application.WorksheetFunction.CountIf(Sheets("Suppliers Code").Range("B:B"), Cells(i, 34).Value) > 0 Then
            Cells(i, 34).EntireRow.Delete
       End If
    Next i


End Sub


Function LastCell(ws As Worksheet) As Range
'
' Note "&" denotes a long value; "%" denotes an integer value
 
  Dim LastRow&, lastCol%

' Error-handling is here in case there is not any
' data in the worksheet

  On Error Resume Next

  With ws

  ' Find the last real row

    LastRow& = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the last real column

    lastCol% = .Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

  End With

' Finally, initialize a Range object variable for
' the last populated row.

  Set LastCell = ws.Cells(LastRow&, lastCol%)

End Function

No luck :(

Fernando

Yeeeeyyy!!! I've managed to make it work but changing the suppliers code to column 7 and it worked.

Still not sure why it didn't work before as I changed the value, but I guess I'm reading the code wrong.

Thanks a lot,

Fernando

Excellent solution, to the point, with a working code and a sample file to apply to code.