Need help with deleteing sheets using VBA

[Dan Thompson]

Ok the object of this code is that once it is done running that It will
cumulatively Select all sheets in a workbook that are NOT named "STable" or
"SChart" much in the same way as if you were to manually hold down the Cntrl
button and start clicking (selecting) multiple sheets with in a workbook

Why doesn't this work ?

here is my code:

Sub Test()
Dim y as integer
Dim t as interger

For y = 1 To SheetCount
If Not Sheets(y).Name = "STable" Then
If Not Sheets(y).Name = "SChart" Then
t = t + 1
ReDim Preserve SheetsToDel(t)
SheetsToDel(t) = Sheets(y).Name
End If
End If
Next y

For y = 1 To UBound(SheetsToDel())
If y = UBound(SheetsToDel()) Then
StrA = Chr(34) + SheetsToDel(y) + Chr(34)
Else
StrA = Chr(34) + SheetsToDel(y) + Chr(34) + ","
End If
StrB = StrB + StrA
Next y
Sheets(Array(StrB)).Select
End Sub

This doesn't work and yet if I just try this line of code:

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

It works. My StrB is the same thing as manually typing in the string the
the line above so what gives any thoughts ??

 

[JLatham]

I wouldn't even worry with the array, you've got all of the sheet names
needing to be deleted in SheetsToDel(), just use it:

Application.DisplayAlerts = False ' prevent nags
for y = Lbound(SheetsToDel) to UBound(SheetsToDel)
If SheetsToDel(y)<>"" Then
Sheets(SheetsToDel(y)).Delete
End IF
Next
Application.DisplayAlerts=True

Take note of the way the reference to the SheetsToDel array is made in the
LBound() and UBound statements also.

 

[Dan Thompson]

Thanks JLatham your code works fine

although I'm a little confused about this part of your code works

If SheetsToDel(y)<>"" Then
Sheets(SheetsToDel(y)).Delete
End IF

If I am getting this right that part in your code says that
If the value of SheetsToDel(y) greater than or less than any of the table
names
contained within SheetsToDel() array then delete those sheets which seems
backwards to me because all the values of that array are the names of the
sheets to which I want to delete. And yet it works and deletes all the
sheetnames with in the SheetsToDel() array and leaves the remaining sheets
still in the workbook.
Which is exactly what I wanted it to do. Perhaps you can explain the path of
logic behind this part of your code cause even though it works I don't
understand why ?

Thanks again
Dan Thompson

 

[Dan Thompson]

Ok disregard my last reply to your post I figured it out although I also
noticed that
Line [If SheetsToDel(y)<>"" Then] is not nessicary

It works fine with just [Sheets(SheetsToDel(y)).Delete] by itself

Thanks for your help it was very usefull.

cheers.

 

[JLatham]

That trap was in there just in case there is an element of the array that
doesn't have a sheet name in it. Have to go back and look at your original
code, I didn't look closely initially. But if you redimension the array to
hold another name but there is no other name to put into it, the UBound()
element of it would be empty - this prevents an error if that turns out to be
the case. But if working without it, great.

Ok disregard my last reply to your post I figured it out although I also
noticed that
Line [If SheetsToDel(y)<>"" Then] is not nessicary

It works fine with just [Sheets(SheetsToDel(y)).Delete] by itself

Thanks for your help it was very usefull.

cheers.

Thanks JLatham your code works fine

although I'm a little confused about this part of your code works


If I am getting this right that part in your code says that
If the value of SheetsToDel(y) greater than or less than any of the table
names
contained within SheetsToDel() array then delete those sheets which seems
backwards to me because all the values of that array are the names of the
sheets to which I want to delete. And yet it works and deletes all the
sheetnames with in the SheetsToDel() array and leaves the remaining sheets
still in the workbook.
Which is exactly what I wanted it to do. Perhaps you can explain the path of
logic behind this part of your code cause even though it works I don't
understand why ?

Thanks again
Dan Thompson