using string functions within a range?
 

I think I know the answer to this question, but I am looking for a
suggestion to try...

Background
I have an inefficient piece of code that is determining whether to
process a record before copying it into the macro file. I was doing
this line by line originally but it is turning out that it has to
process approx. 30,000 rows (takes 10 minutes to run). So I am in the
process of converting this part of the macro to use auto filter.

Question - Can I apply string functions to a range? I think the
answer to this is no.

The original logic i used to copy the one line was:
wsTempTab.Cells(lcurrentTempRecord, 20).Value = Trim
(WWExtractInput.Worksheets(1).Cells(x, 8).Value) &
WWExtractInput.Sheets(1).Cells(x, 20).Value

I can create 2 range objects (each about 30,000 rows of one column
each) and use the union on the ranges to combine them and paste into
one column, but I need to do a trim to the content of the rows within
the ranges first.

Does anyone have a suggestion to do this efficiently?

Thanks in advance,
Enzo

using string functions within a range?
 

You can do something liket his with your range objects

myRange.formulaR1C1 = "=TRIM(RC8 & RC20)"

(untested)
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Enz" wrote:

I think I know the answer to this question, but I am looking for a
suggestion to try...

Background
I have an inefficient piece of code that is determining whether to
process a record before copying it into the macro file. I was doing
this line by line originally but it is turning out that it has to
process approx. 30,000 rows (takes 10 minutes to run). So I am in the
process of converting this part of the macro to use auto filter.

Question - Can I apply string functions to a range? I think the
answer to this is no.

The original logic i used to copy the one line was:
wsTempTab.Cells(lcurrentTempRecord, 20).Value = Trim
(WWExtractInput.Worksheets(1).Cells(x, 8).Value) &
WWExtractInput.Sheets(1).Cells(x, 20).Value

I can create 2 range objects (each about 30,000 rows of one column
each) and use the union on the ranges to combine them and paste into
one column, but I need to do a trim to the content of the rows within
the ranges first.

Does anyone have a suggestion to do this efficiently?

Thanks in advance,
Enzo