#### AusSteelMan

##### Board Regular

- Joined
- Sep 4, 2009

- Messages
- 208

Below is a portion of code from a sub from a large worksheet table (~75000 rows A:BB columns).

I currently select a range manually, then run the sub.

Code:

```
For Each cell In rng
cell.Formula = "=concatif([Material Number],[@[Material Number]],[No_at_Loc],CHAR(10))"
cell.Copy
cell.PasteSpecial xlPasteValues
Next cell
```

I ended up with this since Excel kept "Not Responding" when letting the table automatically fill and calculate.

As can be seen, the formula I require is a UDF.

This UDF interrogates the entire "Material Number" column to check for the "Material Number" from that particular row, in order to then return concatenated text from the "No_at_Loc" column with CHAR(10) as a separator.

I say this only to inform you that this calculation is time consuming due to the number of rows in the "Material Number" column it is checking. Basically it is a SUMIF for text, hence ConcatIf.

I have found it also takes a very long time for the worksheet to recalculate with such a hungry UDF, hence the Paste Values action. I already have the sheet in manual calculation to prevent lengthy delays while it recalcs without me wanting it to. Also, I don't need it to be live information. Once it has calculated I am happy for it to be text anyway.

It is taking around 0.8 seconds per cell to complete. So for the 75000 odd cells I need to do it will take around 17 hours. I am OK with this (it can run overnight) so long as it doesn't crash. I do get "not responding" come up on the VBA editor (and sometimes the Excel worksheet), but Excel keeps on chugging on anyway, eventually giving me a result (around 15 mins for around 1000 cells).

**So my question to you good folk: is there a better method for performing this loop that is more efficient?**

(this is as much for my education as it is for my workbook)

Many thanks for considering my problem and for any help you may provide.

Cheers,

Darren