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.

 

[SkipVought]

if left([D1],2)="SP" then [d1].entirerow.delete

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

 

[Mizzness]

Skip,

Thanks for the guidance but now I get a Compile Error: EndIF without block If

Code:
Sub macro2()
'
Columns("C:C").Select
Range(Selection, Selection.End(xlDown)).Select
If Left([c6], 2) = "SP" Then [c6].EntireRow.Delete
End If
End Sub

Please advise.

 

[EBGreen]

Get rid of the end if. You don't need it.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Mizzness]

EB,

I had tried that but it did not work.
All the code does is highlight col.C.
While that is fine, I then need the macro to take any data starting with "SP" and deleting the row associated with it.

 

[SkipVought]

"I had tried that but it did not work."

Do you mean to tell me that this ONE ROW, ROW 6 did not get deleted?

Check the number of rows in your table.

"All the code does is highlight col.C."

All the code SEEMS to do is select column C. BTW, I answered your original question correctly -- NO END IF!!!

However, what you seem to INTEND to happen is not what you ASKED in your question.

Why don't you ask the real question. Please be CLEAR, CONCISE and COMPLETE.



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

 

[Gavona]

To be honest I would use Autofilter, Custom Criteria.
Use criteria of Equals SP*
That will translate to Starts With SP
Then select the rows and delete them.

Record the above and post back with any problems.

Skip's solution answers your original question but would require you to:
Select the cells you want to test (not the entire column)
then loop through each cell testing it and deleting if required.
When deleting you have to start at the bottom and work up. Consider:
You are in cell A3, it meets your criteria so you delete the row and what was A4 is now A3. Your code however moves on to the new A4 and never tests what is now A3.

So Autofilter easier to understand and probably operates quicker as well.

Gavin

 

[Mizzness]

Skip,

1. My original post did not state what my intention: I need the macro to take any data starting with "SP" in column "C" and deleting the row associated with it.
2. I did take out the End If & yes, the code you supplied does delete that row only.

Gavin,
I agree the Autofilter is easier but would rather use a loop.

Please advise.

 

[AnotherHiggins]

Mizzness:

I second Gavona 's suggestion.

Two good ways to speed up code:
[tab]-avoid using Select whenever possible
[tab]-avoid using loops whenever possible

You don't need to do either.

So what's your business case for wanting a loop?

Turn on your macro recorder (Tools > Macros > Record New Macro) and then do an autofilter (Data > Filter > Autofilter) for Column C > Custom > Begins with > SP. Select all rows other than the header and Delete (Right Click > Delete Row > OK).

Observe the generated code. Post it here if you need help cleaning it up.

[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.

 

[RivetHed]

Then stick it in a loop.

Sub macro2()
Dim r As Range

For Each r In Range("C:C")
If UCase(Left(r, 2)) = "SP" Then r.EntireRow.Delete
Next

End Sub

 

[SkipVought]

Rivit,

Your code will NOT work. When you delete the row, you LOOSE the reference.

If you want to loop, loop from the bottom up.

I agree with the AutoFilter method. MUCH FASTER!!!!!!

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

 

[RivetHed]

Doh!

Completely forgot about stepping backwards.

 

[SkipVought]

I agree the Autofilter is easier but would rather use a loop.

Please advise.

Why use a tool that takes longer to code and takes longer to execute?

WHY do you prefer to use a loop? There must be a good reason.

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

 

[Gavona]

If you do loop then do NOT use the code

For Each r In Range("C:C")

You certainly do not want to loop through all 65,536 cells in a column! Focus first on recording the code needed to select the range you want to work on.
Look up in help: "Looping Through a Range of Cells", "usedrange", "Rows property"

Gavin

 

[Mizzness]

All.

Here is the posted code using the custom filter feature.
My question is how do I adjust it to capture all rows once the filter is on when the first row changes daily ?
From the code, today's data starts at row 56.

Code:
Rows("5:5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=SP*", Operator:=xlAnd
Range("C56").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Rows("5:5").Select
Selection.AutoFilter

Thanx.

 

[SkipVought]

"today's data starts at row 56"

Wierd. Why the changing location?

I suspect that row 56 new data in an existing table. TRUE?

filter on the ENTIRE TABLE!!!!

I advise strongly against this kind of code for autofilter...

Rows("5:5").Select

I begin ALL my data tables in A1. Here's the code that I would use using that assumption...

    [A1].AutoFilter
    [A1].AutoFilter Field:=3, Criteria1:="=SP*"

    with [A1].Currentregion
      .range(.cells(2,1), .cells(.rows.count, .columns.count)).specialcells(xlcellstypevisible).entirerow.delete
    end with

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

 

[Mizzness]

Skip,

The result of the custom filter gives Row 56 the starting row with data that starts with "SP".

I start at row 5 because that is where my header is.
If my starting row for deletion changes (which it will) how do I adjust my code ?

Thanx for your help.

 

[SkipVought]

Did you modify my code to suite and try it?

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

 

[Mizzness]

Yes.

I get the following Run-time error '1004': "Unable to get the Specialcells property of the range class"

Code:
Sub Macro5()
'

[A5].AutoFilter
[A5].AutoFilter Field:=3, Criteria1:="=SP*"

With [A5].CurrentRegion
.Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).SpecialCells(xlcellstypevisible).EntireRow.Delete
End With
End Sub


Putting a filter into code is new for me so thanks for your patience.

 

[SkipVought]

sorry, type

xlCellTypeVisible

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