Find End in Undefined Range > Select Range > Trim

[SteveT]

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

 

[Bernie Deitrick]

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

 

[Gary''s Student]

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.

 

[Michael]

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

 

[SteveT]

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 !