Deleting blank worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lvenom - 14 Jun 2006 23:41 GMT

My data is imported from a seperate program and the other progra
automatically opens a new work book when the import starts. My problem
the data only resides on the first worksheet and then there are 2 to
blank worksheets. Is there any way to right a macro to delete only th
blank worksheets? Thank you for any help provided

Reply to this Message

tony h - 15 Jun 2006 00:01 GMT

try

Sub a()
Dim rng As Range
Dim wks As Worksheet

Application.DisplayAlerts = False
For Each wks In ThisWorkbook.Worksheets()
If wks.UsedRange.Cells.Count <= 1 Then
wks.Delete
End If
Next
Application.DisplayAlerts = True

End Sub

note that the count test is against a value of 1

Hope this help

Reply to this Message

Lvenom - 15 Jun 2006 00:11 GMT

Thank you for the quick response. I tried the code supplied but I get a
error at

wks.Delete
Doesn't recognize this I guess. I am working with Excel 97 so maybe
just have a tired program...

Reply to this Message

tony h - 15 Jun 2006 00:28 GMT

The code does assume you have data on at least one worksheet. If none
have data on them then it will error on trying to delete the last
sheet.

So import your data then run the macro

Signature

tony h

Reply to this Message