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

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
Asked:
Fernandino1977
1 Solution
 
Ken ButtersCommented:
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
 
sbaughanCommented:
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

Open in new window


Adapt the code in function of which column has the supplier code ...
0
 
Fernandino1977Author 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
krishnakrkcCommented:
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
0
 
Fernandino1977Author Commented:
Sorry, I thought I attached the sample file before.

Attached now.

Fernando
Sample.xlsx
0
 
Ken ButtersCommented:
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

Open in new window


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
 
Fernandino1977Author 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

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
0
 
Fernandino1977Author 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
 
Fernandino1977Author Commented:
Excellent solution, to the point, with a working code and a sample file to apply to code.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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