Replace characters in columns in excel

I need some help in replacing carriage returns that is occuring in the excel file using vbscript.

Anyone to guide me?

Thanks

Give something like this a try:

Sub remove10()
Dim Rng, r As Range
'removes carriage returns from A1 down
Set Rng = Range(Cells(1, 1), _
Cells(ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row, 1))
For Each r In Rng
r.Value = Application.Substitute(Trim(CStr(r.Value)), Chr(10), "")
Next r
End Sub

HTH--Lonnie M.

sorry for the inconvience

im writing in vbscript and im rather a newbie

Set objXL = WScript.CreateObject ("Excel.Application")
Set objWb = objXl.WorkBooks.Open(GetPath+file)

I have objXL and objWB open..
so how i can make use of ur code snippet to remove the carriage return in the worksheet?

Thanks a lot

is there any help?

set objRange = objXL.Range("A1:I56")
objRange.Select
objRange.Replace Chr(10), ""


i have manage to come out with something like this it replaces the carriage return with empty string..


another question,
how can i select all the range in the worksheet if there are fields in it?

Hi Moonwalker,

Try:

Set ObjRng = Activesheet.UsedRange


---
Regards,
Norman



"moonwalker" <moonwalker.23rh7y_1140831001.7763@excelforum-nospam.com> wrote
in message news:moonwalker.23rh7y_1140831001.7763@excelforum-nospam.com...
>
> set objRange = objXL.Range("A1:I56")
> objRange.Select
> objRange.Replace Chr(10), ""
>
>
> i have manage to come out with something like this it replaces the
> carriage return with empty string..
>
>
> another question,
> how can i select all the range in the worksheet if there are fields in
> it?
>
>
> --
> moonwalker
> ------------------------------------------------------------------------
> moonwalker's Profile:
> http://www.excelforum.com/member.php...o&userid=31766
> View this thread: http://www.excelforum.com/showthread...hreadid=516190
>

dim objWks as object
set objwks = objWB.worksheets(1)
set objRange = objwks.usedrange
'or set objRange = objwks.Cells
'you don't need to select it.
objRange.Replace Chr(10), ""

====
I'm not sure what "if there are fields in it" means???


moonwalker wrote:
>
> set objRange = objXL.Range("A1:I56")
> objRange.Select
> objRange.Replace Chr(10), ""
>
> i have manage to come out with something like this it replaces the
> carriage return with empty string..
>
> another question,
> how can i select all the range in the worksheet if there are fields in
> it?
>
> --
> moonwalker
> ------------------------------------------------------------------------
> moonwalker's Profile: http://www.excelforum.com/member.php...o&userid=31766
> View this thread: http://www.excelforum.com/showthread...hreadid=516190

--

Dave Peterson