If Then to delete sheet if B1 blank

[Carole O]

Excel 2003 SR2

I'm moving a large file into row range worksheets (5000 lines go into each
worksheet) which is then tested for blanks in Column B. If there are blanks
the row is deleted. Then I test to see if the worksheets past the 20000th
row are blank (B1 will always be blank if the whole worksheet is blank). If
true, delete the worksheet without displaying the warning.

My problem is the code (below) deletes the worksheet whether B1 is blank or
not ! I've cut and pasted other examples to get this formula and probably
did not cut enough or paste enough.

Any help would be appreciated!

CaroleO

If IsEmpty(Worksheets("20000").Range("B1")) Then
Application.DisplayAlerts = False
Sheets("20000").Delete
Application.DisplayAlerts = True
Else
Sheets("20000").Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End If
If IsEmpty(Worksheets("25000").Range("B1")) Then
Application.DisplayAlerts = False
Sheets("25000").Delete
Else
Sheets("25000").Select
Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

 

[Dave Peterson]

Is this code in a general module or is it in a worksheet module (maybe run by
clicking on a commandbutton from the Control toolbox toolbar?)

If it's in a general module, I don't see a problem (no testing, though).

If it's behind a worksheet, then any unqualified ranges will belong to the
worksheet that owns the code.

In any event, I'd use code like this to avoid the select's:

With Worksheets("20000")
If IsEmpty(.Range("B1").value) Then
Application.DisplayAlerts = False
.delete
Application.DisplayAlerts = True
Else
on error resume next 'just in case there are no blanks
.Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
on error goto 0
End If
End With

And I didn't see anything wrong with the isempty() portion. I don't have a
guess why you're seeing the worksheet get deleted.

 

[Barb Reinhardt]

Try this on a copy

Sub Testing()
dim aWS as worksheet

application.displayalerts = false

'Deletes all worksheets that have B1 empty
for each aws in activeworkbook.worksheets
if isempty(aws.range("B1"))then
aws.delete
end if
next aws
application.displayalerts = true

End Sub

HTH,
Barb Reinhardt