delete rows that don't contain text

[Natdan]

I have used subtotal on my data and have copied and paste special'd my data
to another sheet, I know want to delete all the rows that dont have the word
"*Total*" in them. I am fairly new to vba and would appreciate any help
anyone can give me.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/delete.htm

Try the AutoFilter example
http://www.rondebruin.nl/delete.htm#AutoFilter

Read the comments in the code

 

[ryguy7272]

I think this will do what you want:
Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range

DeleteValue = "*Total*"
With ActiveSheet
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete

End With
.AutoFilterMode = False
End With
End Sub

Notice: the range is Column A, specifically A1:A100.
Modify this to suit your needs.

Regards,
Ryan--

 

[Natdan]

Thanks for your help guys
Ryan, that is actually the exact opposite of what I want to do, I actually
want to keep the rows that have the word total in them and delete the rest.
when I subtotalled my data, then copied and pasted specialled it, the rows I
want to keep have cells that are e.g. "1 Total".

Ron I went to your website and followed some of the examples, I was able to
delete all the names except "ron" which is similar to what I want to do
except that I want to delete rows with cells like "1 total", but leave the
rows with "1"

Any help either of you can give me would be great.

 

[Ron de Bruin]

want to keep the rows that have the word total in them and delete the rest.

Try
DeleteValue = "<>*total*"

Like this

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the value that you want to delete
'Tip: use DeleteValue = "<>ron" to delete rows without ron
DeleteValue = "<>*total*"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

 

[Jarek Kujawa]

try this one:

Sub test()
For Each cell In Selection
If cell Like "*Total*" Then
cell.Rows.EntireRow.Delete
End If
Next
End Sub

 

[Ron de Bruin]

Look out

If you delete rows start at the bottom and go up

 

[Jarek Kujawa]

Ron, you're right
sorry

 

[Natdan]

Thanks Ron and Jarek

Ron your macro worked perfectly, I could not get Yours to work Jarek but
that probably me. I'm a complete novice. But enjoying the challenge

 

[Jarek Kujawa]

it's not you, it's my mistake
use Ron's pls