Home » Category » Microsoft Excel

Microsoft Excel: Yet another macro...

146| Wed, 05 Dec 2007 08:06:00 GMT| ioclio| Comments (7)
Hi everybody,
I know it can sound trivial, but I cant solve this problem:
I'm using in an excel macro:

Range(RangeFrom).Copy
Range(RangeTo).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

The fact is that I would like to trim blank spaces that are present in RangeFrom.
So if I have to copy cells A1 (value: " A ") and A2 (value " B ") in the corrisponding cells B1 and B2, I would like to have "A" in B1 and "B" in B2.
How can I get this done?
Thanks in advance.
Father Xmas

Keywords & Tags: macro, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/494749/
 
«« Prev - Next »» 7 helpful answers below.
Sub Test()
Range("A1:A2").Copy Destination:=Range("B1:B2")
End Sub

shades | Tue, 11 Dec 2007 22:00:00 GMT |

Hi

What i think your after is something like this

Sub Test()
With Range("B1:B2")
.FormulaR1C1 = "=TRIM(RC[-1])"
.Formula = .Value
End With
End Sub

all this is doing is using the trim formula from Excel and refrencing the previous column, Then replacing the formula you have with the values it produces.

HTH
David

davidcoutts | Tue, 11 Dec 2007 22:01:00 GMT |

Yep, David, I missed the spaces on each side. I think the CLEAN function works as well.

shades | Tue, 11 Dec 2007 22:02:00 GMT |

hi Shades

The clean function only gets rid of right spaces, while trim gets rid of spaces from both sides, I just thought you misread the question and had no idea when you'd be back so i posted for spead of response

Hope your Well
David

davidcoutts | Tue, 11 Dec 2007 22:03:00 GMT |

Hi, David. That's fine. My posting is often erratic, depending on work load. Glad you stepped in. That's what makes this a community effort.

shades | Tue, 11 Dec 2007 22:04:00 GMT |

Thank you guys,
just another question,
I used:

With Range(MyCell)
.NumberFormat = "General"
.FormulaR1C1 = "=TRIM(" & SheetNameFrom & "!C1)"
.Formula = .Value
.AutoFill Destination:=Range("'" & SheetNameTo & "'!" & RangeTo), Type:=xlFillDefault
End With

I noted that
.FormulaR1C1 = "=TRIM(" & SheetNameFrom & "!C1)"

is not executed properly; in MyCell I get:
=TRIM(SheetNameFrom!$A:$A)

Where am I wrong? :(

ioclio | Tue, 11 Dec 2007 22:05:00 GMT |

the problem is here

.FormulaR1C1 "=TRIM(" & SheetNameFrom & "!C1)"

.FormulaR1C1 uses a different refrencing system so here C1 reffers to Column 1 hence Column A

Looking at your code all i think you have to do is to use .Formula in place of .FormulaR1C1

HTH

David

davidcoutts | Tue, 11 Dec 2007 22:06:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories