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.

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

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

Read the comments in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natdan" wrote in message ...
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.

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


--
RyGuy


"Ron de Bruin" wrote:

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

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

Read the comments in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natdan" wrote in message ...
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.

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.

"ryguy7272" wrote:

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


--
RyGuy


"Ron de Bruin" wrote:

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

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

Read the comments in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natdan" wrote in message ...
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.

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natdan" wrote in message ...
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.

"ryguy7272" wrote:

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


--
RyGuy


"Ron de Bruin" wrote:

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

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

Read the comments in the code

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Natdan" wrote in message ...
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.

try this one:

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

Look out

If you delete rows start at the bottom and go up

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jarek Kujawa" wrote in message ...
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, you're right
sorry

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" wrote:

try this one:

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

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