Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109505

Re: Huge data set Find-Replace

Show all headers | View raw

> Is it possible to use something like this example for find replace 
> except just for single sheet with a huge data set, say 300,000 - 
> 600,000 cells?
>
> The built in find replace is quite slow.
>
> This looks like it loads the sheet (actually a file) into a Variable 
> and does the find replace on the variable... is that correct?
>
Yes, that's correct! Doesn't address your data being in a worksheet 
unless you export the sheet to a delimited file first, then import it 
back after replacing the string.

I recall posting code to efficiently 'dump' a sheet into a 
tab-delimited file and vice versa. You'll have to search topics (in a 
few groups here that you frequent) for something containing "importing 
from a text file" Basically, you can...
    call the export procedure;
    call 'ReadTextFile' to dump the data into a string var;
    replace text;
    call 'WriteTextFile' to dump the string back into the file;
    then call the import procedure
..to 'dump' the file back into the sheet.

Might seem a bit convoluted but all the procs are reusable. There are 
addition procs to convert 2D array to 1D and vice versa. All is very 
fast compared to working directly in the sheet!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-10-31 19:17 -0700
  Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 00:21 -0400
    Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 00:27 -0400
      Re: Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-11-01 01:11 -0700
        Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 15:19 -0400
          Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-01 19:12 -0400
            Re: Huge data set Find-Replace "L. Howard" <lhkittle@comcast.net> - 2016-11-01 18:01 -0700
              Re: Huge data set Find-Replace GS <gs@v.invalid> - 2016-11-02 00:47 -0400

csiph-web