Activecell.offset alternative

[Boltsie]

Hi,

I'm developing a spreadsheet which is looking at over 500 rows. Depending on a marker hidden in a column of each row, the code decides what it is going to do with a row. For example, Insert a row underneath, hide it, clear it's contents etc.

Using activecell.offset to move to each row to analyse what to do it with, I believe is causing the procedure to run incredibly slowly.

Is there an alternative that can be used other than physically moving to each row at a time? I basically need to get this routine running faster!

I'm a novice at this - you may have gathered!

Thanks for all your help.

[JBeaucaire]

Absolutely what you're describing can be more efficient. The goal is to minimize the number of times a physical change is made to the worksheet.

So, if you're going to be deleting some rows, a routine that goes through and collects all the rows to be deleted, then deletes them all at once at the end, would be superiorto one that deleted rows one at at time.

If you're going to be hiding rows, a second routine to do that afte the first is done deleting can be used to hide a collection of rows all at once.

Finally, if you're going to be clearing entire rows, the same technique can be used a third time to collect the rows to clear, then do it all at once at the end.


If you provide the following I can help more specifically:

1) The column to evaluate
2) The first row of data (often row1 or more are headers that need to be ignored)
3) A list of the values and what do for each

[Boltsie]

Thanks for your help.

This isn't going to be mega easy to explain. A worksheet is being built from another worksheet in the workbook, but the new worksheet require formatting, rows hiding etc depending on the markers in the column. The markers for ease of explanation are in column D.

If there is a '0' in D and the cell in E isn't blank, the row needs auto fitting to height and then moving down for the next row to be analysed.

If there is a number, 1 to 15 in column D and E isn't blank, the row needs auto fitting to height and a new row inserting underneath.

If there is a number 1 to 15 in column D and E is blank, the contents require clearing and the row hiding.

I assume the code for each would be relatively similar. There are around 550 rows to analyse.

Does any of that make sense?

Thanks again.

[JBeaucaire]

Add what is the criteria for deleting rows?

Also, why are we clearing the contents of a row and then hiding it instead of simply removing the unwanted row?

[Boltsie]

Add what is the criteria for deleting rows?

Also, why are we clearing the contents of a row and then hiding it instead of simply removing the unwanted row?

No rows require deleting, just hiding.

Formula on another spreadsheet is relying on there being a set number of rows. It's complicated! :-)

The contents require clearing as there are certain ranges which are using the countA function. Hidden rows need to be clear.

The 'template' data is ever changing which is why it has to be done this way. Some rows hidden on one build, may not be on another. Another worksheet analyses the data on this sheet to generate reports.

Does that make any sense??

[JBeaucaire]

Something like this to demonstrate the technique and you can tweak from there:

Please Login or Register  to view this content.

[Boltsie]

Hi,

This has helped me learn loads. Thanks so much!

I have used the code as you have shown, it has sped things up, but the routine is still running a little slowly unfortunately. I will have to look into it further, but this is such a better way of doing it rather than activecell.offset!

You have been really helpful, thanks again.