Incorporating the LEFT function into Code 1

[Mizzness]

All.

In col.D I have some data that is identified as SP3 & SP 50.
How can I incorpoarate the follwing logic into code ?
=if(left(D1,2)=SP,then delete entire row.

Thanx for any help & guidance.

 

[Mizzness]

No problem.

One last thing: how do you disengage the filter to show all the data ?

 

[SkipVought]

1. You could use HELP in VBA.

2. Check out the ShowAllData method.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[AnotherHiggins]

Another problem:

If the With part, take out the dot before Cells - the one highlighted here:
[tab].Range([highlight] . [/highlight]Cells(1, 1)
Otherwise, it will start its delete on row 10, failing to delete any SPs on rows 6-9.

Personally, I would handle it a little differently, like this:

Sub YourMacro
[A5].AutoFilter
    [A5].AutoFilter Field:=3, Criteria1:="=SP*"
    Range([C6], [C6].SpecialCells(xlLastCell)).EntireRow.Delete
[A5].AutoFilter
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

If the With part" should read "In the With part"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Mizzness]

Exactly what I needed.

Thanks Skip for all you help.

 

[AnotherHiggins]

It's obviously up to you, but I'd strongly advise you to heed the warning in my last post (well, last full post). Otherwise you will eventually fail to remove rows that you're expecting to remove.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

oops! John is correct!!!

Just remove the DOTS before the TWO Cells objects.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Mizzness]

Made the change.

Thanks again.