Delete rows, speed up?

[shadow12345]

Hi all,

I am using the below code to delete rows from an excel 2007 sheet.... trouble is it take forever! I have 38000 + rows.

Is there anything I can do to speed up more?

code/ Sub Firstdel()

Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 4 Step -1
If Range("AB" & i).Value = "Delete" Then Range(i & ":" & i - 1).Delete Shift:=xlUp
Next i


End Sub \code

 

[MrKowz]

Give this a shot:

Sub Firstdel()
Dim i As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
For i = Cells(Rows.Count, 1).End(xlUp).row To 4 Step -1
    If Range("AB" & i).Value = "Delete" Then Range(i & ":" & i - 1).Delete Shift:=xlUp
Next i
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Also, for the code tags, you need to put brackets around it so it looks like [CODE]'your code here[/CODE]

 

[dave3009]

Code tags [ code ] then end them by using the the same but putting a / after the opening bracket.

Anyhow....on to the code, why not try using autofilter to filter column AB on the value "Delete" then delete those rows. It's a single operation rather than cycling through each cell.

 

[shadow12345]

Ahh, better but still take about 3 or 4 minutes to process. (mine was 15 minutes and still going!)

Anything else i can do, im just try to say if the value in ab is Delete, then delete the row.

 

[MrKowz]

Ahh, better but still take about 3 or 4 minutes to process. (mine was 15 minutes and still going!)

Anything else i can do, im just try to say if the value in ab is Delete, then delete the row.

Consider doing what Dave suggested. Autofilter based on "Delete" and then delete these rows. It will be faster to do it manually like that than it would take to run your current macro. The problem with your macro is that it is looking at 38,000 rows. Not a whole lot you can do to speed it up beyond what has already been done...

I doubt this will be any faster, but give it a shot:

Sub Firstdel()
Dim i As Long
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With
For i = Cells(Rows.Count, 1).End(xlUp).row To 4 Step -1
    If Range("AB" & i).Value = "Delete" Then Rows(i).Delete
Next i
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[dave3009]

Using autofilter method

Sub Macro1()
Dim lst As Long
lst = Range("AB" & Rows.Count).End(xlUp).Row
    Range("$A$3:$AC$" & lst).AutoFilter Field:=28, Criteria1:="Delete"
    Range("$A$4:$AC$" & lst).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp
    Range("$A$3:$AC$" & lst).AutoFilter
End Sub

 

[shadow12345]

I tried using the autofilter method and get and error message saying Microsoft Excel cannot create or use the data range reference because it is too complex...

it debugs at

    Range("$A$4:$AC$" & lst).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp

any ideas?

 

[MrKowz]

The Shift:= declaration was omitted, I beleive

Range("$A$2:$AC$" & lst).SpecialCells(xlCellTypeVisible).EntireRow.Delete Shift:=xlUp

 

[shadow12345]

nope still the same error. i tried pasting the values in AB instead (they were forumla results but still no luck

 

[shadow12345]

This however does appear to be working..... I guess i could just run it a number of times with different ranges till it works?

Sub Macro1()

    Range("$A$1:$AC$10000").AutoFilter Field:=28, Criteria1:="Delete"
    Range("$A$2:$AC$10000").SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp
    Range("$A$1:$AC$10000").AutoFilter
End Sub

What do you think, could I just use that?