Script out of range...help!!!

[chiragggg]

Hi,

I seem to keep on getting an error with this macro, it says script out of range. Any suggestions?

Sub finaldelsheets()
Dim Ws As Worksheet
Dim c As Range

Application.DisplayAlerts = False
For Each c In ActiveSheet.Range("z:z").Cells
Sheets(c.Value).Delete
Next c
Application.DisplayAlerts = True

End Subhttp://www.travelwasp.co.uk/cgi-bin...etPrice=182&HotelSelPrice=196&programme=10439

 

[Richard Schollar]

Presumably you don't have all of column Z full of sheet names to delete (would be the biggest workbook I've ever seen with at least 65536 worksheets!). try this:

Sub finaldelsheets()
Dim Ws As Worksheet
Dim arr
 
arr = Range("Z1:Z" & Cells(Rows.Count,"Z").End(xlUp).Row).Value
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In Worksheets
  If Isnumeric(Application.Match(ws.Name,arr,0)) Then _
      ws.Delete
Next ws
Application.DisplayAlerts = True

End Sub

 

[chiragggg]

a ha!

Makes sense now, thank you very much. I will giv it a test.