Excel Help

[soze]

I am in over my head and hope someone can help.

We have a sheet with about 40000 stock numbers and we want to delete all the spares for now. They all end in -001. I found the macro below that deletes the row fine if you put the whole part number in but does nothing if I try to use *-001. I know nothing about these Macros can anyone who does let me know if what i want is possible?

Spoiler

Sub Delete_Based_on_Criteria()

' This macro will delete an entire row based on the presence of a
'predefined word or set of words.  If that word or set of words is
'found in a cell, in a specified column, the entire row will be 'deleted

Dim X As Long
Dim Z As Long
Dim LastRow As Long
Dim FoundRowToDelete As Boolean
Dim OriginalCalculationMode As Long
Dim RowsToDelete As Range
Dim SearchItems() As String

Dim DataStartRow As Long
Dim SearchColumn As String
Dim SheetName As String

 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Choose the row you want the search and delete to start on
' Choose the column to search and delete to use for deletion
' Choose the sheet in the workbook you want this macro to be run on

DataStartRow = 1
SearchColumn = "A"
SheetName = "etcstock"

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Enter the terms you want to be used for criteria for deletion
' All terms entered below are CASE SENSITIVE and need to be
'seperated by a comma

SearchItems = Split("609945-001", ",")

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

On Error GoTo Whoops
OriginalCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

With Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, SearchColumn).End(xlUp).Row
For X = LastRow To DataStartRow Step -1
FoundRowToDelete = False
For Z = 0 To UBound(SearchItems)
If InStr(.Cells(X, SearchColumn).Value, SearchItems(Z)) Then
FoundRowToDelete = True
Exit For
End If

Next

If FoundRowToDelete Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = .Cells(X, SearchColumn)
Else
Set RowsToDelete = Union(RowsToDelete, .Cells(X, SearchColumn))
End If

If RowsToDelete.Areas.Count > 100 Then
RowsToDelete.EntireRow.Delete
Set RowsToDelete = Nothing
End If
End If

Next

End With
If Not RowsToDelete Is Nothing Then
RowsToDelete.EntireRow.Delete
End If

Whoops:
Application.Calculation = OriginalCalculationMode
Application.ScreenUpdating = True

 
End Sub

 

[caLLous]

Bleh, it's very possible but my head isn't providing the answers at the mo. It could probably be done a few different ways, using RIGHT(cells, 3) to just check the last 3 digits in each cell against "001" or maybe a hidden column with "=if(RIGHT(cell,3)="001","jfoisdofidsjio","") and then do the macro delete on the hidden column if "jfoisdofidsjio" exists.

Please back your worksheet up first though.

 

[caLLous]

Sub del_001()
    Dim rng As Range
    Dim what As String
    what = "-001"
    Do
        Set rng = ActiveSheet.Columns[B](x)[/B].Find(what)
        If rng Is Nothing Then
            Exit Do
        Else
            Rows(rng.Row).Delete
        End If
    Loop
End Sub

Try that. Change the (x) to whatever number column you store the stock numbers in.

This just checks if the cell contents include "-001" anywhere in them so if there is a chance "-001" appears anywhere earlier in the stock number than at the end then this will be a problem.

 

[soze]

Thanks for your help its all sorted now.

 

[MYstIC G]

Next time use a filter and set it to

Ends With: -001