Find End in Undefined Range Select Range Trim
 

Hello,

I'm working with .CSV worksheet that arrives already 'Text to Colmuns....'
The data is delimited resulting in various data positioning.

Can someone help with macro that will
1) Find 'END CELL' In sheet given random placement of data
2) Select Range.A1: 'END CELL'
3) Trim all cells within the Range

I've searched through the posted solutions on the subject and tried various
combinations of posts but having no luck

Many thanks / Happy Holidays
Steven

Find End in Undefined Range Select Range Trim
 

Steve,

Sub TryNow()
Dim myC As Range
For Each myC In Cells.SpecialCells(xlCellTypeConstants)
myC.Value = Application.Trim(myC.Value)
Next myC
End Sub

HTH,
Bernie
MS Excel MVP


"SteveT" wrote in message
...
Hello,

I'm working with .CSV worksheet that arrives already 'Text to Colmuns....'
The data is delimited resulting in various data positioning.

Can someone help with macro that will
1) Find 'END CELL' In sheet given random placement of data
2) Select Range.A1: 'END CELL'
3) Trim all cells within the Range

I've searched through the posted solutions on the subject and tried various
combinations of posts but having no luck

Many thanks / Happy Holidays
Steven

Find End in Undefined Range Select Range Trim
 

First extend activesheet.usedrange back to A1 (may not be necessary):

Sub trimum()
Set rr = Range(Range("A1"), ActiveSheet.UsedRange)
For Each r In rr
r.Value = Trim(r.Value)
Next
End Sub


If your "random placement of data" is not activesheet.usedrange, then update
the second statement accordingly.
--
Gary''s Student - gsnu200759


"SteveT" wrote:

Hello,

I'm working with .CSV worksheet that arrives already 'Text to Colmuns....'
The data is delimited resulting in various data positioning.

Can someone help with macro that will
1) Find 'END CELL' In sheet given random placement of data
2) Select Range.A1: 'END CELL'
3) Trim all cells within the Range

I've searched through the posted solutions on the subject and tried various
combinations of posts but having no luck

Many thanks / Happy Holidays
Steven

Find End in Undefined Range Select Range Trim
 

Sub Findntrim()
LastCell = Range("A65536").End(xlUp).Row

For i = 1 To LastCell
Cells(1, i).Value = Trim(Cells(1, i))
Next i
End Sub
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"SteveT" wrote:

Hello,

I'm working with .CSV worksheet that arrives already 'Text to Colmuns....'
The data is delimited resulting in various data positioning.

Can someone help with macro that will
1) Find 'END CELL' In sheet given random placement of data
2) Select Range.A1: 'END CELL'
3) Trim all cells within the Range

I've searched through the posted solutions on the subject and tried various
combinations of posts but having no luck

Many thanks / Happy Holidays
Steven

Find End in Undefined Range Select Range Trim
 

Bernie / Student of Gary / Michael

Thank You All for the quick responses!

Bernie, Your solution worked instantly - perfect
Student of Gary, Your's took nano second longer :) - Thanks
Michael, No Luck Sir :)

Again - Thanks !



"SteveT" wrote:

Hello,

I'm working with .CSV worksheet that arrives already 'Text to Colmuns....'
The data is delimited resulting in various data positioning.

Can someone help with macro that will
1) Find 'END CELL' In sheet given random placement of data
2) Select Range.A1: 'END CELL'
3) Trim all cells within the Range

I've searched through the posted solutions on the subject and tried various
combinations of posts but having no luck

Many thanks / Happy Holidays
Steven