delete row containing specific values | VBA | Add to code

Yes cell A1 contains a column heading called STOCK CODE and is the only row containing this value that should not be deleted.

Your idea sounds very logical

Mike

Hi Mike,

Would you like me to provide a working example, or is that something you can do by simply setting the "Macro Recorder" prior to starting the manual process, then stopping immediate afterwards?

If you would like me to proceed, would it be possible to provide (attach/upload) a sample workbook with some typical examples in column [A] for me to test my code against?

I only really need 20 or 30 values in column [A] with at least two examples of each "STOCK CODE" you wish to remove.

If not, that's fine, I understand the potential sensitivity of the data.

^ From Rgonzo1971's implementation of the suggestion I proposed above...

 myRng.AutoFilter Field:=1, Criteria1:=Array( _
        "~~~~~~~~~~~", "LOCALINDSUP", "STOCK CODE"), Operator:=xlFilterValues

This is why I wished to confirm the exact values, as the first value within the question looks like eleven hyphen characters ("-"), but when copied they seem to change to tilde characters ("~").

Also, if the value is a set of hyphens, I wished to know if they were prefixed within the cell with an apostrophe ('), or not.

Corrected code

Sub parseLISsalesdata()
'
' parseLISsalesdata Macro
'

'
    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(23, 1), Array(45, 1), Array(63, 1)), _
        TrailingMinusNumbers:=True
    Range("A2:D2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWorkbook.Worksheets("lissalesaugust").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("lissalesaugust").Sort.SortFields.Add Key:=Range( _
        "A2:A27085"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("lissalesaugust").Sort
        .SetRange Range("A2:D27085")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Set myRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    myRng.AutoFilter Field:=1, Criteria1:=Array( _
        "-----------", "LOCALINDSUP", "STOCK CODE"), Operator:=xlFilterValues
    myRng.Offset(1).Resize(myRng.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp
    myRng.AutoFilter
End Sub

Select allOpen in new window

it is not necessary to know if there is a ' with the filter

The values were waves "~"

I tested Rgonzos first code and it worked perfectly.

A good contribution from both of you.

Mike

Thanks Mike.

If any of the rows are blank before the last row in column [A], then the code will need revising slightly, but I am glad we reached a workable solution for you.

it is not necessary to know if there is a ' with the filter

It may not be essential but probably wise to test any proposed solution with, & also without, this character to avoid potential issues.