Advice on how to speed up a macro??

[seriouslystuck]

Hi All

I have a macro that cleans up a data set before producing a report. The problem Im having is that I have a loop that looks in column J row by row for the value "E" then deletes the row. There is approx 15000 rows of these in the sheet and the macro takes an age to run! I have turned off screen updating.

Is there any other ways to speed it up??

Thanks

 

[Weaver]

Use autofilter for the value "E", then delete all the visible rows.

If you're unsure how to do this, there are loads of related posts on the board.

 

[Jonmo1]

Can you post the code?
If it's selecting cells, then evaluating and deleting them,
That could be made faster without the use of Select.

Also perhaps using the AutoFilter for "E" then deleting all rows at once.

Something like

Sub Macro1()
With Sheets("Sheet1").Range("A1").CurrentRegion
    .AutoFilter Field:=1, Criteria1:="E"
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Sheets("Sheet1").AutoFilterMode = False
End Sub

 

[seriouslystuck]

Weaver

Had thought about this but I dont want a user to autofilter and delete because there has been mistakes made in the past hence why the macro and to autofilter and delete 10000-15000 rows not in order also takes a lot of computer resource and time. Maybe it is the only way to go tho!

 

[buxtongt]

It would be a manual fix, but could you sort everything by column J and pull and delete the "E" rows? You could probably do something automate this process as well. At least that way it wouldn't have to look through every row. Maybe the select>f5>visible only? Hope this helps.

 

[Weaver]

Sub testfilter()
    Dim headerRow, filterCol, lastRow, firstCol, lastCol
    firstCol = 1 '"A"
    headerRow = 4
    filterCol = 10 '"J"
    lastRow = Cells(Rows.Count, filterCol).End(xlUp).Row
    lastCol = Cells(headerRow, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    With Range(Cells(headerRow, firstCol), Cells(lastRow, lastCol))
        .AutoFilter
        .AutoFilter field:=filterCol - firstCol + 1, Criteria1:="E"
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
End Sub

Set all the values for the headerrow and the first column, in case your data isn't starting in A1

The time overhead might not be as bad as you think, especially if you turn of auto calculation as well.

 

[seriouslystuck]

Thanks for the replies.

The loop im using is

LR = Range("J" & Rows.Count).End(xlUp).Row For i = 1 To LR
With Range("J" & i)
If .Value = "E" Then .EntireRow.Delete
End With
Next i

Its basic I know but so is my VBA skills

 

[Weaver]

When using looping to delete rows, you should work from the bottom up

LR = Range("J" & Rows.Count).End(xlUp).Row   
For i = lr To 1 step -1
    With Range("J" & i)
        If .Value = "E" Then .EntireRow.Delete
    End With
Next i

otherwise excel gets confused about what row it's supposed to be looking at. This might be causing problems by forcing the code to recheck lines that have already been tested and not deleted.

Personally I'm still prefering filtering but give this mod a try and see if it improves the situation.

 

[seriouslystuck]

Hi

I have tried the code that Jonmo1 posted and it works great but how would I filter on two columns?

Thanks

 

[Weaver]

Try doing a 'record macro' whilst manually applying the filters. The resulting code should help you with figuring out what you need to change.