Delete Visible Rows

[vmjan02]

I have this code and is working all fine, but the only problem is its taking a lot of time, just for 2000 rows it took 20 min.
is there a way as how can i faster this process. I do have formula in other sheet . Any idea.

Its just auto filter the row, and deletes the visible rows.

[CODE=vba]Sub filtration()
Dim x As Long

Application.ScreenUpdating = False

    With ActiveSheet
        'Remove any filters
        If .AutoFilterMode Then .AutoFilterMode = False
        'Define last used row in column 5/"E"
        x = .Cells(.Rows.Count, 49).End(xlUp).Row
              
        With .Range("AW1").Resize(x)
            'Apply filter to range E1:Ex
            .AutoFilter

            'Filter for "A"
            .AutoFilter Field:=1, Criteria1:="0"

            'Turn off warning pop-up message
            Application.DisplayAlerts = False

            'Offset by 1 from E1 (keep header), delete visible rows
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Here how can we faster the same'

            'Turn on warning pop-up message
            Application.DisplayAlerts = True
        End With
        
        'Remove filter
        .AutoFilterMode = False
    End With
Application.ScreenUpdating = True
End Sub

[/CODE]

 

[mole999]

if you sort your data, and then delete that can be quicker

 

[vmjan02]

I did tried that as well, but still it takes time.

 

[mole999]

ow many formulas relate across the sheet

 

[vmjan02]

not much formulas,

 

[mole999]

when you step through the routine, does it step out and back as parts change

you might add Application.EnableEvents = False

and make true at the end otherwise you can loose control of the screen

also consider taking display alerts outside of the loop, you are turning off and on as it runs

 

[vmjan02]

ok, sure. Let me try