Faster way to append string in range?

[RockandGrohl]

Hi all,

Am using this:

Dim rng as Range
Dim cel as Range
Set rng = Range("K2:K" & LastrowPPD) 'About 3,500 rows

For Each cel In rng
cel.value = " " & cel.Value & ","
Next cel

Basically just want to add "," to the end of each cell in the range, as well as a space at the start.


Gotta be a fasterway to do it, right?? Takes 4 minutes to do 3,500 rows.

Thanks!

 

[Rick Rothstein]

To add a third option to Joe's list... if a loop ends up being required, first copy the range of cells to a Variant variable after which that variable will contain a two-dimensional array of values (even if the range is a single column or a single row of values) in memory... iterate that array and manipulate the data as needed and, once you are done, assign the array back to the original range it came from (if you want to replace the original data with the new data) or to a different (properly sized) range (if you want to retain your original data). Doing it this way, all calculation are done in memory where processing is extremely fast rather than changing the values cell by cell which is a slow process.

 

[Joe4]

To add a third option to Joe's list... if a loop ends up being required, first copy the range of cells to a Variant variable after which that variable will contain a two-dimensional array of values (even if the range is a single column or a single row of values) in memory... iterate that array and manipulate the data as needed and, once you are done, assign the array back to the original range it came from (if you want to replace the original data with the new data) or to a different (properly sized) range (if you want to retain your original data). Doing it this way, all calculation are done in memory where processing is extremely fast rather than changing the values cell by cell which is a slow process.

Rick,
I think that may be bit of an "advanced" VBA skill. Perhaps a demonstration/example is in order, so people can see exactly what something like this would look like.

 

[RockandGrohl]

Glad we could help & thanks for the feedback.

Hi Fluff, I'm back!
Just looking at this which you wrote:

  With Range("K2:K" & LastrowPPD)
      .Value = Evaluate("if({1},"" ""&" & .Address & "&"","")")
   End With

As a reminder it appends the start of the string with a space and the end with a comma, instantaneously.

I've noticed for certain strings it's replacing the list of pickups with "#VALUE"

Here are some of the strings.

Abingdon, Alcester, Aldershot, Amesbury Countess Services (A303), Andover, Aylesbury, Banbury, Banstead, Basingstoke, Bath, Bebington, Bexhill, Bicester, Birkenhead, Birmingham, Bodmin, Bognor Regis, Bournemouth, Bracknell, Bridgwater, Bridport, Brighton, Bristol, Bristol Cribbs Causeway, Bromley, Bromsgrove, Burgess Hill, Caldicot, Camberley, Camborne, Cardiff, Cardiff Gate Services (M4), Cheltenham, Chepstow, Chester, Chester Services (M56), Chichester, Chieveley Services (M4), Chippenham, Christchurch, Cinderford, Cirencester, Clevedon, Cobham Services (M25), Coleford, Coventry, Crawley, Croydon, Dorchester, Droitwich, Dudley, Dursley, Eastbourne, Eastleigh, Ellesmere Port, Epsom, Evesham, Exeter Services (M5), Falmouth, Fareham, Farnborough, Fleet Services (M3), Gloucester, Gordano Services (M5), Guildford, Hailsham, Halesowen, Hastings, Havant, Hayle, Haywards Heath, Hedge End, Hereford, High Wycombe, Hilton Park Services (M6), Holyhead Ferry Terminal, Horley, Horsham, Hove, Keynsham, Kidderminster, Kingswood, Leamington Spa, Ledbury, Leominster, Lewes, Lewisham, Liskeard, Littlehampton, Llandudno Junction, Ludlow, Lydney, Maidenhead, Malvern, Newbury, Newhaven, Newport (South Wales), Newton Abbot, Oswestry, Oxford, Oxford Peartree Services (A34), Oxford Services (M40), Peacehaven, Pease Pottage Services (M23), Penzance, Pershore, Plymouth, Polegate, Poole, Portsmouth, Princes Risborough, Reading, Redditch, Redhill, Redruth, Ringwood, Ross-on-Wye, Rownhams Services (M27), Salisbury, Saltash, Seaford, Shepton Mallet, Sherborne, Shoreham-by-Sea, Shrewsbury, Slough, Southampton, St Austell, Staines, Stonehouse, Stourbridge, Stratford-upon-Avon, Stroud, Sunbury, Sutton, Swindon, Taunton, Telford, Tewkesbury, Thame, Thornbury, Tiverton Sampford Peverell Services (M5), Totton, Trowbridge, Truro, Wallasey, Wareham, Warwick, Waterlooville, Weston-super-Mare, Weymouth, Winchester, Witney, Woking, Wokingham, Wolverhampton, Worcester, Worthing, Wrexham, Yate, Yeovil

Accrington, Altrincham, Amesbury Countess Services (A303), Andover, Ashford, Aylesbury, Banbury, Banstead, Barrow-in-Furness, Basildon, Basingstoke, Bath, Bebington, Beccles, Bedford, Belper, Bexhill, Bicester, Biggleswade, Birkenhead, Bishop's Stortford Birchanger Green Services (M11), Blackburn, Bletchley, Bodmin, Bognor Regis, Bolton, Boston, Bournemouth, Bracknell, Bradford, Braintree, Bridgwater, Bridgwater Services (M5), Brighton, Bristol, Bristol Cribbs Causeway, Broadstairs, Bromley, Bromsgrove, Burgess Hill, Burton-upon-Trent, Bury, Bury St Edmunds, Camberley, Camborne, Cambridge, Canterbury, Chapel St Leonards, Charnock Richard Services (M6), Chatteris, Chelmsford, Cheltenham, Cheshunt, Chester, Chesterfield, Chichester, Chieveley Services (M4), Chippenham, Chipping Sodbury, Christchurch, Cinderford, Cirencester, Clacket Lane Services (M25), Cleethorpes, Clevedon, Coalville, Cobham Services (M25), Colchester, Coleford, Corley Services (M6), Coventry, Crawley, Crewe, Crooklands, Croydon, Cullompton Services (M5), Dartford, Derby, Diss, Doncaster, Dorchester, Dover Port, Downham Market, Dudley, Dunstable, East Cowes, Eastbourne, Eastleigh, Ellesmere Port, Enfield, Epsom, Evesham, Exeter Services (M5), Fareham, Faversham, Ferrybridge Services (M62), Fleet Services (M3), Folkestone Stop 24 Services (M20), Gainsborough, Gloucester, Gordano Services (M5), Gorleston, Grantham, Gravesend, Great Yarmouth, Grimsby, Halesowen, Halifax, Harlow, Harrogate, Hastings, Havant, Haywards Heath, Hedge End, Hemel Hempstead, Hereford, Herne Bay, High Wycombe, Hilton Park Services (M6), Hinckley, Hitchin, Holbeach, Hopwood Park Services (M42), Hucknall, Huddersfield, Hunstanton, Huntingdon, Immingham, Ipswich, Keighley, Kempston, Kettering, Keynsham, Kidderminster, Kidsgrove, King's Lynn, Kingswood, Knaresborough, Knutsford Services (M6), Lancaster, Leamington Spa, Ledbury, Leeds, Leek, Leicester, Leicester Forest East Services (M1), Leighton Buzzard, Leominster, Letchworth, Lewisham, Leyland, Lincoln, Liskeard, Littlehampton, Liverpool, Long Eaton, Loughborough, Louth, Lowestoft, Ludlow, Luton, Lydney, Mablethorpe, Maidenhead, Maidstone, Maidstone Services (M20), Malvern, Mansfield, March, Margate, Market Deeping, Market Harborough, Melksham, Melton Mowbray, Milton Keynes, Nelson, Newark, Newcastle-under-Lyme, Newhaven, Newmarket, Newport (Isle of Wight), Newton Abbot, North Hykeham, Northampton, Northampton Services (M1), Northwich, Norwich, Nottingham, Orpington, Oswestry, Oxford, Oxford Peartree Services (A34), Oxford Services (M40), Peacehaven, Pease Pottage Services (M23), Penrith, Penzance, Pershore, Peterborough, Peterborough Services (A1(M)), Plymouth, Polegate, Poole, Portsmouth, Preston, Ramsgate, Rawtenstall, Rayleigh, Reading, Reading Services (M4), Redditch, Redruth, Ringwood, Ripley (Derbs.), Rochester, Romford, Ross-on-Wye, Rownhams Services (M27), Rugby, Runcorn, Rushden, Ryde, Sale, Salisbury, Saltash, Sandbach Services (M6), Sandown, Sandy, Scunthorpe, Seaford, Selby, Sevenoaks, Shanklin, Sheffield, Shoreham-by-Sea, Shrewsbury, Sittingbourne, Skegness, Skipton, Sleaford, Slough, Solihull, South Mimms Services (M25), South Molton, Southampton, Southampton Ferry Terminal, Southend, Spalding, St Albans, St Austell, St Helens, St Ives (Cambs.), St Neots, Stafford, Staines, Stamford, Stanford-le-Hope, Stevenage, Stoke-on-Trent, Stonehouse, Stourbridge, Stowmarket, Stratford-upon-Avon, Stroud, Sutton, Swadlincote, Swindon, Taunton, Telford, Tewkesbury, Thame, Thetford, Thurrock Services (M25), Tibshelf Services (M1), Tiverton Sampford Peverell Services (M5), Totton, Trowbridge, Truro, Ulverston, Uttoxeter, Uxbridge, Walsall, Wareham, Warrington Lymm Services (M6), Warwick, Warwick Services (M40), Watford, Watford Gap Services (M1), Wellingborough, Wellington (Shrops.), Welwyn Garden City, Weston-super-Mare, Weymouth, Whitstable, Widnes, Wigan, Winchester, Winsford, Wisbech, Witney, Wolverhampton, Woodall Services (M1), Woolley Edge Services (M1), Worcester, Worksop, Worthing, Wrexham, Yeovil, York

Accrington, Altrincham, Amesbury Countess Services (A303), Andover, Ashford, Aylesbury, Banbury, Banstead, Barrow-in-Furness, Basildon, Basingstoke, Bath, Bebington, Beccles, Bedford, Belper, Bexhill, Bicester, Biggleswade, Birkenhead, Bishop's Stortford Birchanger Green Services (M11), Blackburn, Bletchley, Bognor Regis, Bolton, Boston, Bournemouth, Bracknell, Bradford, Braintree, Bridgwater, Bridgwater Services (M5), Brighton, Bristol, Bristol Cribbs Causeway, Broadstairs, Bromley, Bromsgrove, Burgess Hill, Burton-upon-Trent, Bury, Bury St Edmunds, Caldicot, Camberley, Cambridge, Canterbury, Cardiff, Cardiff Gate Services (M4), Chapel St Leonards, Charnock Richard Services (M6), Chatteris, Chelmsford, Cheltenham, Chepstow, Cheshunt, Chester, Chesterfield, Chichester, Chieveley Services (M4), Chippenham, Chipping Sodbury, Christchurch, Cinderford, Cirencester, Clacket Lane Services (M25), Cleethorpes, Clevedon, Coalville, Cobham Services (M25), Colchester, Coleford, Colwyn Bay, Conwy, Corley Services (M6), Coventry, Crawley, Crewe, Crooklands, Croydon, Cullompton Services (M5), Dartford, Derby, Diss, Doncaster, Dorchester, Dover Port, Downham Market, Dudley, Dunstable, Eastbourne, Eastleigh, Ellesmere Port, Enfield, Epsom, Evesham, Exeter Services (M5), Fareham, Faversham, Ferrybridge Services (M62), Fleet Services (M3), Folkestone Stop 24 Services (M20), Gainsborough, Gloucester, Gordano Services (M5), Gorleston, Grantham, Gravesend, Great Yarmouth, Grimsby, Halesowen, Halifax, Harlow, Harrogate, Hastings, Havant, Haywards Heath, Hedge End, Hemel Hempstead, Hereford, Herne Bay, High Wycombe, Hilton Park Services (M6), Hinckley, Hitchin, Holbeach, Hopwood Park Services (M42), Hucknall, Huddersfield, Hunstanton, Huntingdon, Immingham, Ipswich, Keighley, Kempston, Kettering, Keynsham, Kidderminster, Kidsgrove, King's Lynn, Kingswood, Knaresborough, Knutsford Services (M6), Lancaster, Leamington Spa, Ledbury, Leeds, Leek, Leicester, Leicester Forest East Services (M1), Leighton Buzzard, Leominster, Letchworth, Lewisham, Leyland, Lincoln, Littlehampton, Liverpool, Llandudno, Llanelli, Long Eaton, Loughborough, Louth, Lowestoft, Ludlow, Luton, Lydney, Mablethorpe, Maidenhead, Maidstone, Maidstone Services (M20), Malvern, Mansfield, March, Margate, Market Deeping, Market Harborough, Melksham, Melton Mowbray, Milton Keynes, Neath, Nelson, Newark, Newcastle-under-Lyme, Newhaven, Newmarket, Newport (South Wales), North Hykeham, Northampton, Northampton Services (M1), Northop, Northwich, Norwich, Nottingham, Orpington, Oswestry, Oxford, Oxford Peartree Services (A34), Oxford Services (M40), Peacehaven, Pease Pottage Services (M23), Penrith, Pershore, Peterborough, Peterborough Services (A1(M)), Polegate, Poole, Port Talbot, Portsmouth, Preston, Queensferry, Ramsgate, Rawtenstall, Rayleigh, Reading, Reading Services (M4), Redditch, Rhyl, Ringwood, Ripley (Derbs.), Rochester, Romford, Ross-on-Wye, Rownhams Services (M27), Rugby, Runcorn, Rushden, Sale, Salisbury, Sandbach Services (M6), Sandy, Scunthorpe, Seaford, Selby, Sevenoaks, Sheffield, Shoreham-by-Sea, Shrewsbury, Sittingbourne, Skegness, Skipton, Sleaford, Slough, Solihull, South Mimms Services (M25), Southampton, Southend, Spalding, St Albans, St Helens, St Ives (Cambs.), St Neots, Stafford, Staines, Stamford, Stanford-le-Hope, Stevenage, Stoke-on-Trent, Stonehouse, Stourbridge, Stowmarket, Stratford-upon-Avon, Stroud, Sutton, Swadlincote, Swansea, Swindon, Taunton, Telford, Tewkesbury, Thame, Thetford, Thurrock Services (M25), Tibshelf Services (M1), Tiverton Sampford Peverell Services (M5), Totton, Trowbridge, Ulverston, Uttoxeter, Uxbridge, Walsall, Wareham, Warrington Lymm Services (M6), Warwick, Warwick Services (M40), Watford, Watford Gap Services (M1), Wellingborough, Wellington (Shrops.), Welwyn Garden City, Weston-super-Mare, Weymouth, Whitstable, Widnes, Wigan, Winchester, Winsford, Wisbech, Witney, Wolverhampton, Woodall Services (M1), Woolley Edge Services (M1), Worcester, Worksop, Worthing, Wrexham, Yeovil, York

It seems the ones that error out are absolutely massive strings, my typical string length is more like this:

Felixstowe, Ipswich, Harwich, Frinton-on-Sea, Clacton-on-Sea, Colchester, Braintree, Chelmsford, Brentwood, Romford, Harlow

Is there a size limit on evaluate for strings?

Cheers.

 

[Fluff]

Yes there is a limit on what evaluate can handle, try this instead

   Dim Ary As Variant
   Dim r As Long
   
   Ary = Range("K2:K" & LastrowPPD).Value2
   For r = 1 To UBound(Ary)
      Ary(r, 1) = " " & Ary(r, 1) & ","
   Next r
   Range("K2:K" & LastrowPPD).Value = Ary

 

[RockandGrohl]

Yes there is a limit on what evaluate can handle, try this instead

   Dim Ary As Variant
   Dim r As Long
  
   Ary = Range("K2:K" & LastrowPPD).Value2
   For r = 1 To UBound(Ary)
      Ary(r, 1) = " " & Ary(r, 1) & ","
   Next r
   Range("K2:K" & LastrowPPD).Value = Ary

Wow. Equally as rapid. Pssst, can you explain what "If({1}" means in your original evaluate? Cheers!

 

[Fluff]

It basically forces the evaluate to look at each row individually.

 

[RockandGrohl]

It basically forces the evaluate to look at each row individually.

Ah!! Excellent, so in that case I can add another part to the evaluate that's basically like this:

With Range("K2:K" & LastrowPPD)
.Value = Evaluate("if({1},"" ""&" & .Address & "&"","")")
.Value = Evaluate(if cell in ad = "making own way" then .address & " & "", Making own way, ""
End With

Obviously I have no idea how to write the syntax for it, but that's the logic.

All moot as the for r = 1 to UBound logic you provided works instantly, so in that case I want to add a second condition to that:

   Dim Ary As Variant, Ary2 as Variant
   Dim r As Long
  
   Ary = Range("K2:K" & LastrowPPD).Value2
   Ary2 = range("AD2:AD" & LastrowPPD).Value2
   For r = 1 To UBound(Ary)
      Ary(r, 1) = " " & Ary(r, 1) & "," & Ary2
   Next r
   Range("K2:K" & LastrowPPD).Value = Ary

In that case, if the cells in Ary2 are either "Making own way," or blank, this should work nicely, right?

 

[Fluff]

It would need to be

Ary(r, 1) = " " & Ary(r, 1) & "," & Ary2(r, 1)

 

[RockandGrohl]

Hi Fluff, figured that out and yes it works

Just ran into a problem where the references I'm looking up against are numbers stored as text, which was creating errors, which was stopping this from running. A quick text-to-columns and all sorted now.

Apologies for the duplicate thread, I saw that as I had marked this post as solved, I did think that people would not think to click on it to offer help as it appeared already solved.

Thanks again for the help. I'm surprised doing essentially a loop that checks each cell is so fast - can you offer an explanation as to why this is instant, whereas if I looped through each cell and added the space and trailing comma, while also checking if the parallel cell in column AD had a value is so slow? Is it because a traditional do until loop has you activating the next offset cell each time?

Cheers.

 

[Fluff]

With an array you are doing everything in memory, which is a lot faster then constantly reading from & writing to the sheet