delete rows based on specific criteria

[woods2007]

Hi,

What is the fastest (and safest) way to delete rows based on a criteria which is a part of a cell value, within a specific column?

e.g. cell value is 'temp flexi 12345678' and the criteria for deletion is 'temp flexi '

Cheers

 

[SteveO59L]

Use a filter

 

[woods2007]

Use a filter

Cheers,

below is my code:

Dim FinalRow As Long

FinalRow = Cells(Rows.Count, "A").End(xlUp).Row
'
Selection.AutoFilter
ActiveSheet.Range(Cells(1, 1), Cells(FinalRow, 1)).AutoFilter Field:=1, Criteria1:="=temp flexi*"

how do I select the returned rows and delete them?

 

[SteveO59L]

Why are you using code ?

 

[woods2007]

Why are you using code ?

it is one of many steps in a workbook for a daily morning job that we run. We are looking to take the human element out of the process to minimise the risk of missed steps and output errors (it is a large worksheet with many similarly named columns) and so that any team member can run the job.

 

[SteveO59L]

Below code loops through col A, and deletes the entire row, if the word "Subtotal" is found.

See if you can adapt

Dim i As Long
Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
' ** Deletes rows where col A value = "Subtotal"
For i = LASTROW To 1 Step -1
If InStr(Range("A" & i).Value, "Subtotal") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

Next i

 

[woods2007]

Below code loops through col A, and deletes the entire row, if the word "Subtotal" is found.

See if you can adapt

Dim i As Long
Dim LASTROW As Long

LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
' ** Deletes rows where col A value = "Subtotal"
For i = LASTROW To 1 Step -1
If InStr(Range("A" & i).Value, "Subtotal") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

Next i

Thanks for the code. I still have the same problem ie. (using your example my criteria value would be 'Sub' within the full string 'Subtotal')

if i was doing it through excel I would use if(left(cell,3)="Sub","Flag for delete","")

any suggestions?

 

[SteveO59L]

Change this line

If InStr(Range("A" & i).Value, "Subtotal") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

to

If InStr(Range("A" & i).Value, "Sub") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

 

[woods2007]

Change this line

If InStr(Range("A" & i).Value, "Subtotal") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

to

If InStr(Range("A" & i).Value, "Sub") Then Range("A" & i).EntireRow.Delete Shift:=xlUp

That has worked- thanks for bearing with me!