Keep function result after removing referenced column

[Guest]

Greetings:

I have programmed the following formula to extract a substring from
one column on my worksheet to another:

ActiveCell.FormulaR1C1 = "Zone"
Range("C2:C" & LastRow).FormulaR1C1 = "=LEFT(RC[-1], SEARCH(""-"",
RC[-1])-1)"

The problem is that once I have finished extracting my substring, I want
to delete the FROM column while still keeping the results of the function
in the TO column. If I simply delete the FROM column, I get #REF! errors
in all the cells of my TO column.

Can anybody point out a simple way around this problem? Is there a simple
way to convert the function results to a static value and then remove
the FROM column?

Regards,

Charles

 

[Guest]

Select TO column
Copy
PasteSpecial/Values (into the same TO column)

Regards,
Stefi


„Charles in Iraq†ezt írta:

 

[Roger Govier]

Hi Charles

After your code, insert the following 2 lines

Range("C2:C" & LastRow) = Range("C2:C" & LastRow).Value
Columns("B:B").Delete Shift:=xlToLeft

 

[Guest]

Thanks. That worked.

Select TO column
Copy
PasteSpecial/Values (into the same TO column)

Regards,
Stefi


„Charles in Iraq†ezt írta:

Greetings:

I have programmed the following formula to extract a substring from
one column on my worksheet to another:

ActiveCell.FormulaR1C1 = "Zone"
Range("C2:C" & LastRow).FormulaR1C1 = "=LEFT(RC[-1], SEARCH(""-"",
RC[-1])-1)"

The problem is that once I have finished extracting my substring, I want
to delete the FROM column while still keeping the results of the function
in the TO column. If I simply delete the FROM column, I get #REF! errors
in all the cells of my TO column.

Can anybody point out a simple way around this problem? Is there a simple
way to convert the function results to a static value and then remove
the FROM column?

Regards,

Charles