Stuck with slow macro - Hide rows and auto fit

[dhrconcept]

Dear all, I've been reading this forum for quite some time now and every time the forum helped me solve my excel of VBA problem. Except this time.

I have a problem with the speed of the following macro:

Please Login or Register  to view this content.

The macro should kick in when field E2 is changed (based on a validated list).

My sheet consists of aprox 600 rows. The sheet processes a report where some of the rows should be hidden and all non-hidden rows should be auto fitted.

'Column A' is blank of filled with an "X". All rows with a blank cell in 'column A' should be hidden, all other should be shown.
'Columns AJ to AO' are used to auto fit the rows. These columns are adjusted to the length of specific merged columns, since auto fit doesn't work on merged cells/colums.

The main problem: it takes about 15 to 20 seconds to process hide/show and auto fit. It shouldn't take this long, but I've been tinkering with this macro several times now, without much result. Any advice?

Thanks in advance! Regards, D.

Edit: I have used the search function (and googled ofc) but I can't get some of the alternatives to run. I only have basic VBA knowledge.

[walruseggman]

Try:

Please Login or Register  to view this content.

[dhrconcept]

Try:

Thanks! It does work a lot quicker, however, it unfortunately only works on totally blank cells.
The cells in column A have a small formula in them like: =IF(D100="";"";"X") to determine whether or not the row should be hidden.

Is there a workaround for this?

[walruseggman]

Well since your formula is looking to see if D is blank, can't you just change the code to look at column D, and therefore not even need the X's in column A?

Please Login or Register  to view this content.

[dhrconcept]

That won't work. A cell in column A doesn't always have to correspond with the cell in column D on the same row. Eg: Cell A1 could check contents for cell D2.

A1 (Cell D2="") - Row 1 Should be hidden
A2 (Cell D2="") - Row 2 Should be hidden
A3 (Cell D2="") - Row 3 Should be hidden
A4 (Cell D5="text") - Row 4 should be shown
A5 (Cell D5="text") - Row 5 should be shown

[walruseggman]

Then try this. Won't be as fast as my last approach, but should be faster then your original code. Would be interested to know how long it takes to run on your data set.

Please Login or Register  to view this content.

[dhrconcept]

I appreciate your help, thanks
The code works but bugs on the following line

Please Login or Register  to view this content.

As for runtime:
Original macro (TS Post): 19,31 sec
Post #6 macro: 18,9 secs

Would it be a solution to populate a specific column (eg column AC) with a 1 or a 2 based on the X in column A? (or edit the formulas in column A to give a 1 or a 2). Would that make it possible to use the lightning fast macro you gave before?

[bakerman2]

How about this one ?

Please Login or Register  to view this content.

[dhrconcept]

It works without any errors, however, runtime is still 19,39 seconds for each cycle.

I suspect the 'Next' cycle is making it go slow, correct?

[bakerman2]

Post a small example workbook which reflects actual situation so we have something to work with.
I've made an example workbook myself with formula like Post #3 and it evaluates 600 formulas and hides rows in 0.1 sec so something else must be going on.

[dhrconcept]

Sorry for my absence, holiday.

Thanks for the explanation. I will try to post an example workbook next week!