delete row containing specific values | VBA | Add to 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
End Sub

Open in new window


I need to add to this code so that once the stages in the code above are complete it will then carry out the following.....

1. Delete all rows where column A from A2 downwards contains any of the following three values "~~~~~~~~~~~"      "LOCALINDSUP"      "STOCK CODE"
mikes6058Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Does cell [A1] contain text, like a column heading?

(Programmatically) An (Auto)Filter could be added to cell [A1], then the appropriate explicit values could be filtered, if any rows are visible these could then be deleted, & the AutoFilter removed (so that just the rows not matching any of the three values remain).
mikes6058Author Commented:
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
[ fanpages ]IT Services ConsultantCommented:
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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Rgonzo1971Commented:
HI,

pls try

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

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
^ 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.
Rgonzo1971Commented:
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

Open in new window

it is not necessary to know if there is a ' with the filter
mikes6058Author Commented:
The values were waves "~"

I tested Rgonzos first code and it worked perfectly.

A good contribution from both of you.

Mike
[ fanpages ]IT Services ConsultantCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.