How to delete sheets with "Ch" in the sheet name?
 

I don't really understand why need to

set c = nothing

at the end?

Martin's just cleaning up after himself. When object variables are set, it's
better to clear them yourself than to rely on VB doing it. Just in case.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
Martin and Jon, Thanks alot.

Martin's solution could be used for worksheets if

dim c as worksheet

Both of your solution works well for me.

I don't really understand why need to

set c = nothing

at the end?

"Martin Fishlock" wrote:

Terry try this. It may need a little adapting as I'm not quite sure of
your
name specification.

Option Explicit
' deletes all charts in the range ch0..ch9
Sub deletecharts()
Dim c As Chart
Dim chartname As String

Application.DisplayAlerts = False
For Each c In ActiveWorkbook.Charts
chartname = LCase(c.Name)
If Left(chartname, 2) = "ch" Then ' first two letters
If IsNumeric(Mid(chartname, 3, 1)) Then ' next is number
If Len(chartname) = 3 Then ' ok can delete it
c.Delete
End If
End If
End If
Next c
Set c = Nothing
Application.DisplayAlerts = True

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Terry" wrote:

I need to delete all worksheets with name contains "Ch", from "Ch1"
until
"Ch(n)". How to do it? All these sheets are charts.

How to delete sheets with "Ch" in the sheet name?
 

When object variables are set, it's better to clear them yourself than to
rely on VB doing it. Just in case.

So if you set 30 or 40 object variables, you should always add 30 or 40
commands at the end of the routine setting them to nothing.

Not necessary nor is there any advantage.

In fact, there have been posts in VB forums from recognized experts saying
that it is counter productive. With few exceptions, VB is designed to
efficiently manage memory internally.

--
Regards,
Tom Ogilvy


"Jon Peltier" wrote in message
...
I don't really understand why need to

set c = nothing

at the end?

Martin's just cleaning up after himself. When object variables are set,
it's better to clear them yourself than to rely on VB doing it. Just in
case.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
Martin and Jon, Thanks alot.

Martin's solution could be used for worksheets if

dim c as worksheet

Both of your solution works well for me.

I don't really understand why need to

set c = nothing

at the end?

"Martin Fishlock" wrote:

Terry try this. It may need a little adapting as I'm not quite sure of
your
name specification.

Option Explicit
' deletes all charts in the range ch0..ch9
Sub deletecharts()
Dim c As Chart
Dim chartname As String

Application.DisplayAlerts = False
For Each c In ActiveWorkbook.Charts
chartname = LCase(c.Name)
If Left(chartname, 2) = "ch" Then ' first two letters
If IsNumeric(Mid(chartname, 3, 1)) Then ' next is number
If Len(chartname) = 3 Then ' ok can delete it
c.Delete
End If
End If
End If
Next c
Set c = Nothing
Application.DisplayAlerts = True

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Terry" wrote:

I need to delete all worksheets with name contains "Ch", from "Ch1"
until
"Ch(n)". How to do it? All these sheets are charts.

How to delete sheets with "Ch" in the sheet name?
 

I guess you're right. I don't always clean up so well myself. Is the old VB6
and VBA good at this, or do the posts refer to VB.Net?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tom Ogilvy" wrote in message
...
When object variables are set, it's better to clear them yourself than to
rely on VB doing it. Just in case.

So if you set 30 or 40 object variables, you should always add 30 or 40
commands at the end of the routine setting them to nothing.

Not necessary nor is there any advantage.

In fact, there have been posts in VB forums from recognized experts saying
that it is counter productive. With few exceptions, VB is designed to
efficiently manage memory internally.

--
Regards,
Tom Ogilvy


"Jon Peltier" wrote in message
...
I don't really understand why need to

set c = nothing

at the end?

Martin's just cleaning up after himself. When object variables are set,
it's better to clear them yourself than to rely on VB doing it. Just in
case.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
Martin and Jon, Thanks alot.

Martin's solution could be used for worksheets if

dim c as worksheet

Both of your solution works well for me.

I don't really understand why need to

set c = nothing

at the end?

"Martin Fishlock" wrote:

Terry try this. It may need a little adapting as I'm not quite sure of
your
name specification.

Option Explicit
' deletes all charts in the range ch0..ch9
Sub deletecharts()
Dim c As Chart
Dim chartname As String

Application.DisplayAlerts = False
For Each c In ActiveWorkbook.Charts
chartname = LCase(c.Name)
If Left(chartname, 2) = "ch" Then ' first two letters
If IsNumeric(Mid(chartname, 3, 1)) Then ' next is number
If Len(chartname) = 3 Then ' ok can delete it
c.Delete
End If
End If
End If
Next c
Set c = Nothing
Application.DisplayAlerts = True

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Terry" wrote:

I need to delete all worksheets with name contains "Ch", from "Ch1"
until
"Ch(n)". How to do it? All these sheets are charts.

How to delete sheets with "Ch" in the sheet name?
 

As Tom says the consensus amongst the VB Classic experts is best let VB(A)'s
highly efficient 'garbage collection' clean up objects when they go out of
scope. Reasoning being, at best set myObj=Nothing is an unnecessary process
when it's about to go out of scope, at worst releasing in the wrong order
could do damage.

However there seems to be more disagreement how best to clear
'circular-references' in certain scenarios.

Regards,
Peter T

"Jon Peltier" wrote in message
...
I guess you're right. I don't always clean up so well myself. Is the old
VB6
and VBA good at this, or do the posts refer to VB.Net?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Tom Ogilvy" wrote in message
...
When object variables are set, it's better to clear them yourself than
to
rely on VB doing it. Just in case.

So if you set 30 or 40 object variables, you should always add 30 or 40
commands at the end of the routine setting them to nothing.

Not necessary nor is there any advantage.

In fact, there have been posts in VB forums from recognized experts
saying
that it is counter productive. With few exceptions, VB is designed to
efficiently manage memory internally.

--
Regards,
Tom Ogilvy


"Jon Peltier" wrote in message
...
I don't really understand why need to

set c = nothing

at the end?

Martin's just cleaning up after himself. When object variables are set,
it's better to clear them yourself than to rely on VB doing it. Just in
case.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Terry" wrote in message
...
Martin and Jon, Thanks alot.

Martin's solution could be used for worksheets if

dim c as worksheet

Both of your solution works well for me.

I don't really understand why need to

set c = nothing

at the end?

"Martin Fishlock" wrote:

Terry try this. It may need a little adapting as I'm not quite sure
of
your
name specification.

Option Explicit
' deletes all charts in the range ch0..ch9
Sub deletecharts()
Dim c As Chart
Dim chartname As String

Application.DisplayAlerts = False
For Each c In ActiveWorkbook.Charts
chartname = LCase(c.Name)
If Left(chartname, 2) = "ch" Then ' first two letters
If IsNumeric(Mid(chartname, 3, 1)) Then ' next is number
If Len(chartname) = 3 Then ' ok can delete it
c.Delete
End If
End If
End If
Next c
Set c = Nothing
Application.DisplayAlerts = True

End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Terry" wrote:

I need to delete all worksheets with name contains "Ch", from "Ch1"
until
"Ch(n)". How to do it? All these sheets are charts.