Clunky Slow Code, Hides Empty Rows, Takes too long...

Hello, I've written code that hides rows on different sheets if they are empty and in one case changes column width to keep the formatting looking ok. I've used non-contiguous named ranges because there are multiple places on each row that could (or could not) be empty. It does exactly what I want, but it takes a long time to process and will only get longer if I continue to add new sheets. I'm pretty new to VBA, but I know I didn't write it well as I am iterating through multiple ranges and performing tests on each cell. I'm pretty sure I could use an array, but I failed at coding that myself. Moreover, using xlspecialcellblanks isn't working, because the "empty" cells have formulas in them. Any help would be greatly appreciated! Thanks very much.

Please Login or Register  to view this content.

Oh well, thanks for trying anyways. I was thinking maybe there was an array solution, so I could reference the array index instead of each cell but I don't know how to do it. I know I could get the range to be hidden set dynamically to a variable but then I couldn't get the column width part to work too.

Hi

try this code.

Please Login or Register  to view this content.

Brilliant Question by the way. It got me thinking.

This will un-hide rows that contain numbers or text, whether they are constants or returned by a formula.

No loops are involved.

Enjoy.

Hi mehmetcik,

Thanks so much for your reply! Unfortunately, I want to hide rows that have formulas (or references) but are returning blanks due to no input in the reference cell. Your code unhides these which are a reference to empty cells. I tried IF(ref="","",ref) in the cells as well and still no dice. I think that is why the "Empty" property has worked in my code. I think I may have to set the area to be hidden first by a loop and then hide that. I'm really new with arrays, and I got one that seems to iterate through an index of cells, but for some reason (even though I can see it has the correct reference in debug) it doesn't recognize the cell... maybe I need to redim the array, but again, I am inexperienced with them.

For Example, I use the following code and I can see the varray referencing cells it should be unhiding... but, it doesn't unhide them as my original For...Each loop would...

Please Login or Register  to view this content.

Hi

All you have to do is to hide the formulas returning blanks I would have thought.

Hi

All you have to do is to hide the formulas returning blanks I would have thought.

Please Login or Register  to view this content.

Thanks again for your response! Oddly enough, this code works but only if my formulas have the IF(ref="","",ref) convention, but unfortunately, it puts me back in the same position of iterating through many cells and the time it takes to process is about the same as my original code. I'm not sure if you were referring to hiding the formulas (as in protect sheet) or just hiding 0s... I'm also not sure why your first code didn't work but it did with the FOR EACH loop. I even tried defining the ranges.

I actually did get my array solution to check the IF<>EMPTY condition based on array index but then I run into another issue: using Union to join two non-contiguous ranges. Which is why in the following code I had to dim my array as A:C and then of course in the scope of things I am once again not speeding up the process very much. This has been a difficult one for me.

Please Login or Register  to view this content.