VBA Code stops executing, but doesn't throw errors

[thborng]

Hi everyone!

I'm writing some code to delete hidden rows in a spreadsheet, and it is not working...

The worksheet I'm trying it on has a Table (the kind with [@ColumnName] references), which I filtered to show only some rows that match a condition. The others are, of course, hidden. If I run the code on this worksheet, it just STOPS executing after the first time it runs the "ActiveSheet.rows(rowNum).EntireRow.Delete" line. It doesn't even get to the "End If" instruction. It just stops!

There's two things that puzzle me: first, that it actually DOES delete the row just before it stops executing; second, that it takes a few seconds to execute that instruction, so I guess something is going wrong there.

Here's the code:

Sub DeleteHiddenRows()
    Dim rowNum As Integer
    rowNum = ActiveSheet.UsedRange.rows.Count
    Application.ScreenUpdating = False
    For i = rowNum To 2 Step -1
        If ActiveSheet.rows(i).EntireRow.Hidden Then
            ActiveSheet.rows(i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Please help!

P.S.: I've also tried this code with a simple worksheet only containing values from 1 to 15 in cells A1 to A15, and hiding rows 3,4,5,7,9 and 12, and it works like a charm... My guess is the problem lies with the Table.

 

[Andrew Poulsom]

Maybe Post #3 here will help you:

http://www.mrexcel.com/forum/excel-questions/691216-delete-filtered-rows-table-excel-2007-a.html

 

[thborng]

It actually worked now... All I had to do was to disable automatic calculation during the execution of my code. The final working code is:

Sub DeleteHiddenRows()
    Dim rowNum As Integer
    rowNum = ActiveSheet.UsedRange.rows.Count
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    For i = rowNum To 2 Step -1
        If ActiveSheet.rows(i).EntireRow.Hidden Then
            ActiveSheet.rows(i).EntireRow.Delete
        End If
    Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub