Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with string functions please | New Users to Excel | |||
String Functions | Excel Programming | |||
STRING functions | Excel Worksheet Functions | |||
Passing a String in Array to Range as String | Excel Programming | |||
help with string functions | Excel Programming |