Trim function in a macro

[Domingos Junqueira]

Hi,

I would like to use a trim function to delete spaces before and after a
text in an entire column, but so far I could only use it in a cell:

Sub mac1()
Dim testing As String
testing = ActiveSheet.Range("A1")
ActiveSheet.Range("B1") = Trim(testing)
End Sub

Thanks for any help,

--
Domingos Junqueira

[Gary Keramidas]

will this work for you?


Sub mac1()
Dim lastrow As Long
Dim cell As Range
Dim rng As Range
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:A" & lastrow)
For Each cell In rng.Cells
cell.Offset(0, 1).Value = Trim(cell)
Next cell
End Sub

--


Gary


"Domingos Junqueira" <domingos.cjm@globo.com> wrote in message
news:u7MDRZo5FHA.3388@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I would like to use a trim function to delete spaces before and after a
> text in an entire column, but so far I could only use it in a cell:
>
> Sub mac1()
> Dim testing As String
> testing = ActiveSheet.Range("A1")
> ActiveSheet.Range("B1") = Trim(testing)
> End Sub
>
> Thanks for any help,
>
> --
> Domingos Junqueira
>

[Nigel]

Trim will only act on a single string at a time. If you need to trim ALL
used cells in a column then apply a loop to sequentially trim each.
Something like....

Sub TrimColumn()
Dim LastRow as Long, xr as Long
LastRow = Cells(Rows.Count,1).End(xlup).Row
For xr = 1 to LastRow
Cells(xr,2) = Trim(Cells(xr,1))
Next xr
End Sub


--
Cheers
Nigel



"Domingos Junqueira" <domingos.cjm@globo.com> wrote in message
news:u7MDRZo5FHA.3388@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> I would like to use a trim function to delete spaces before and after
a
> text in an entire column, but so far I could only use it in a cell:
>
> Sub mac1()
> Dim testing As String
> testing = ActiveSheet.Range("A1")
> ActiveSheet.Range("B1") = Trim(testing)
> End Sub
>
> Thanks for any help,
>
> --
> Domingos Junqueira
>
>