Speeding up my macro

[jsuarez199]

Hey Guys!

Any ideas on how to speed this macro up?

Please Login or Register  to view this content.

Also, any tips on how to speed up my macros and excel in General? Thanks!

[:) Sixthsense :)]

May be this...

Please Login or Register  to view this content.

[mattmania]

A standard way to speed up macros is to turn off screen updating. You'll notice the screen flickers as Excel displays each output of the macro, considerably slowing down the start-finish time. To resolve this, start your macro with:
Application.ScreenUpdating = False
and end it with
Application.ScreenUpdating = True

Hope this helps
Matt

[Palmetto]

Optimize Slow Code in VBA
Speed up VBA Code

Not that it is slowing your code down, but your first Select Case statement is useless as it does nothing, but it still must be read and processed.
Rather than looping, use AutoFilter and delete all visible rows in one step. It is faster to loop through a short list of filter criteria than every single cell.
In VBA, it is seldom, if ever, necessary to Activate or Select sheets/ranges/cells, etc. - - just act on them directly

Please Login or Register  to view this content.

Example using AutoFilter

Please Login or Register  to view this content.

[JosephP]

perhaps

Please Login or Register  to view this content.

[jsuarez199]

Thanks a lot to everyone!

Palmetto, how would you implement that on the attached sheet? it seems like checking cell by cell is far too slow...

[Palmetto]

Cell-by-cell checking is exactly what your current code is doing.
Perhaps I am missing it, but I don't see where you have clearly established your criteria for deleting rows.
Your code, which doesn't/shouldn't work (because it loops in the wrong direction) just deleting all rows.

Once you define your criteria for which rows to delete, I (or others) can provide some help.

how would you implement that on the attached sheet.

That is what my code example shows. By defning the entire range and filtering it for, say, RF_CORP_TMT, this line of code would delete all rows at once.

Please Login or Register  to view this content.

We could then pass another criteria to the filter to continue deleting groups of records, looping only through a short list of criteria and not cell-by-cell.

[jsuarez199]

Thanks for the help, I have tried to implement it in order two filter out two different criterias, but I get a type mismatch error.

Could you tell me what I am doing wrong? Thanks

[Palmetto]

Well . . you tried to use my code as is and it was provided only as an example - not the solution since I did not have your workbook when I wrote it.

You placed the code in the ThisWorkbook module, but it should be placed in a standard module.

Attached is a working example. I opted to maintain a criteria list on a separate sheet as this approach provides more flexibility for a criteria list than hardcoding a list and using an array. If your criteria list will always be the same, then you might want to use an array of values.

Included in this version is an option to clear the criteria list on sheet2. Sheet references are to sheet code names, so change them as needed.

Please Login or Register  to view this content.

Excel_Forum_DeleteRows.xlsm

[snb]

Please Login or Register  to view this content.