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" <(E-Mail Removed)> wrote in message news5DAB6F4-4E39-4741-BD85-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message news5DAB6F4-4E39-4741-BD85-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message news5DAB6F4-4E39-4741-BD85-(E-Mail Removed)...
> > >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" <(E-Mail Removed)> wrote in message news:A3DCACD8-5C66-4DA4-99DD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news5DAB6F4-4E39-4741-BD85-(E-Mail Removed)...
>> > >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" <(E-Mail Removed)> wrote in message news:994ed8ee-a17c-46ae-8572-(E-Mail Removed)...
> 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