Find End in Undefined Range > Select Range

Find End in Undefined Range > Select Range > Trim

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SteveT - 29 Nov 2007 18:01 GMT

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

Reply to this Message

Bernie Deitrick - 29 Nov 2007 18:13 GMT

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

> Hello,
>
[quoted text clipped - 11 lines]
> Many thanks / Happy Holidays
> Steven

Reply to this Message

Gary''s Student - 29 Nov 2007 18:18 GMT

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.

Signature

Gary''s Student - gsnu200759

> Hello,
>
[quoted text clipped - 11 lines]
> Many thanks / Happy Holidays
> Steven

Reply to this Message

Michael - 29 Nov 2007 18:20 GMT

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

Signature

If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.

> Hello,
>
[quoted text clipped - 11 lines]
> Many thanks / Happy Holidays
> Steven

Reply to this Message

SteveT - 29 Nov 2007 18:54 GMT

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 !

> Hello,
>
[quoted text clipped - 11 lines]
> Many thanks / Happy Holidays
> Steven

Reply to this Message