Why does this code work only after saving my workbook?

[VBAProIWish]

Hello All,

I have a really strange problem here.

I have this macro (below) in it's own module. It's part of a very large routine with over 30 modules. I have one module that is used to "Call" all 30+ modules and it works perfectly....except that the below code won't work until I save the workbook, then I run JUST that module and it works.

Note that I have already confirmed that my first module (the one that "Calls" all the other modules) IS calling this module, so I know that is not the issue.



Nutshell version

1. I run my routine with 30 modules
2. All modules run EXCEPT module #30
3. I save the workbook and RE-Run JUST module #30 and it works!


Why won't it work without have to save the workbook? All the other modules work without saving?

By the way, the code below deletes all worksheets that don't have data below row 1.


Anyone know why?

For Each WS In Worksheets
    Select Case WS.Name
        Case "X", "Y"
            'Do Nothing
        Case Else
            If WS.Cells.SpecialCells(xlCellTypeLastCell).Row > 1 Then
                'Do Nothing
            Else
                WS.Delete
            End If
    End Select
Next WS

Thanks much!

 

[AlphaFrog]

.SpecialCells(xlCellTypeLastCell) doesn't update until you save. So if you've cleared data on those sheets, xlCellTypeLastCell may return an unexpected result.

Maybe try something like this...

Application.DisplayAlerts = False
For Each ws In Worksheets
    Select Case ws.Name
        Case "X", "Y"
            'Do Nothing
        Case Else
            If ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row = 1 Then
                ws.Delete
            End If
    End Select
Next ws
Application.DisplayAlerts = True

 

[VBAProIWish]

AlphaFrog,

Yesss! That was it!

Thank you for that update, all works well now

​