Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim function in a macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim function in a macro
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trim function in a macro
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRIM function | Excel Worksheet Functions | |||
trim function | Excel Worksheet Functions | |||
Need help with TRIM function | Excel Worksheet Functions | |||
Trim function | Excel Worksheet Functions | |||
Trim function in VBA | Excel Programming |