Make a macro more efficient/faster

I am using a macro that when you press a button, it searches in column F for 2410 and hides it when he sees that value. Same with 2510. Only problem is, it takes like 5 min if it has to go through a database of 1000 rows. Anyone got a good suggestion how I can make this faster or have a better idea for a macro?

I attached an example, which only shows a small proportion. I also need to add a macro like that for the columns, could use the same, but would be great if someone can teach me a better way.

Please Login or Register  to view this content.

edit: I can't change the merged cells in the file, I know they are just crap, but it's a must and can't do anything about it.

Please Login or Register  to view this content.

Regards, TMS

Or, better:

Please Login or Register  to view this content.

Regards, TMS

Hi JHonneyboy;

I've taken a look at the code and done a couple quick optimization changes.

Please Login or Register  to view this content.

The first, and most substantial, is starting with application.screenupdating =False, and then changing it back to true at the conclusion. Otherwise, it's going to constantly calculate and update what the layout is going to be to the end user throughout every hide/unhide event.

Secondly, I removed the with statement and consolidated that down into a single line. I'm not sure if it's necessary, or really adding too much power, but typically I try to avoid using With statements if it's going to be effecting a single line.

Lastly, I removed the clause at the conclusion to set rng = nothing. Since the variable is Dim'ed within the Sub, and not set as a public variable, the variable will not persist beyond the sub's execution. Therefore, no reason to clear it out at completion. This is not really an efficiency gain, as it's like, a billionth of a nanosecond for excel to handle setting the variable... just something I'd let you know about.

Give that a shot and see if it still meets all of your expectations and needs. And if not, let me know!

Geeze... competition is fierce on the contributor side today...

Please Login or Register  to view this content.

This line of code, and the corresponding End If is redundant, given that you have set it to a fixed range on the line before testing it.

Regards, TMS

@Miraun: I don't think your code/oprimisation works. The r.MergeArea is important when linked to the .Cells(1) check

Regards, TMS

You're right... Didn't get any syntax errors when running, but just noted that it wasn't fully hiding the non-2410/2510 values... Fixed it would be:

Please Login or Register  to view this content.

@ragulduy: I haven't tested your code. My point was related to Miraun's code, which doesn't work. r.MergeArea.Cells(1).Value isn't the same as r.Value

Please Login or Register  to view this content.

Regards, TMS

I meant post #9 haha, can't keep up.

Edit: Just re-read things, I had thought there was a test to see if the cell value was blank before running the hide command, which I saw as being redundant as I was setting rng to the non-blank cells. Having re-read the code though, not the case, so just ignore me!

@ragulduy: no, it was a general observation to the OP, and anyone who is interested, that that line of code is redundant. In all the "optimisations", it has been left in.

If we're voting, I guess my preferred solution is in post #4

Regards, TMS

You're welcome. Thanks for the rep.