Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VBA to find a value and select a range | Excel Worksheet Functions | |||
select date range then find average of values in another cell | Excel Worksheet Functions | |||
help with find value select range and paste to diff sheet, multipl | Excel Programming | |||
Find and select maximum value in a range | Excel Programming | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) |