• Status: Solved
• Priority: Medium
• Security: Public
• Views: 347

# Excel 2007 Macro to delete rows

Hello,

I'm involved in a data migration project and I want to tidy the information in my CSV before uploading anything to the new system.

I have an excel sheet with over 50,000 entries (stock items) associated to suppliers by the supplier code number in column G.

I need to delete every line of stock associated to a supplier which I haven't purchased in the last three years. I have the list of suppliers (368 in total) and what I need to do is to delete every row where any of the 368 suppliers codes appears in column G.

Can someone help me with a macro that checks in my list of 368 and delete every row in which any of those supplier codes in Column G gives a positive match?

Thanks,

Fernando
0
Fernandino1977
1 Solution

Commented:
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.
0

Commented:
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
``````

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

Author Commented:
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
0

Commented:
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.

Kris
0

Author Commented:
Sorry, I thought I attached the sample file before.

Attached now.

Fernando
Sample.xlsx
0

Commented:
The attached file has code to do what you requested.

To execute... run the "DeleteSuppliers" macro

``````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, 7).Value) > 0 Then
Cells(i, 7).EntireRow.Delete
End If
Next i

End Sub
``````

Note: LastCell is user defined function also included... it returns a cell as a range that is the last used cell on the specified sheet.
Copy-of-Sample.xlsm
0

Author Commented:
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

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
0

Author Commented:
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
0

Author Commented:
Excellent solution, to the point, with a working code and a sample file to apply to code.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.