How can I speed up this sheet event code?

[BeachRock]

This event code runs and works but it is extremely slow. I found it at http://www.vbaexpress.com/kb/getarticle.php?kb_id=512 It was only tested with Excel 2000 and 2003 but it does exactly what I needed so I tried it in 2010. I'm hoping someone can help to speed it up by making it more compatible with 2010 or just replace it completely with something known to work in 2010 to accomplish the same but isn't slow.

I downloaded the example file and ran it (in 2010 as .xls macro enabled) and it works very fast, almost immediately for the 16 rows within it's range. I'm using it for 180 rows. Still, it doesn't seem like it should take as long as it does, around two minutes for my additional rows.

Basically, I need the code to only check the cells in A1:A180 and hide all rows where the cell in column A is blank. Those that are not blank will always have a 1 in them. It has to be a sheet event and needs to be fast since the sheet may be selected often.

Please Login or Register  to view this content.

Thanks for any help or suggestions you can provide!

[patel45]

I tested it, less than 2 seconds for 180 rows

[JosephP]

I reckon you oughta just use an autofilter

[BeachRock]

Hi patel45 and JP. Thanks for replying.

Joseph, unfortunately, I can't use auto-filter because I'm trying to automate the process completely so other users who know little to nothing about Excel will be able to do everything without help.

patel45, Thank you for testing this on 2010 for me. I'm shocked it ran so quickly for you. Did you have any data at all in columns A through T? My sheet will have data in all of those columns when the formulae in them match certain criteria from another sheet. Maybe this is the problem of why it's running so slow for me??