Kindly notice the spaces infront of the description
macro required , also need to know how it works with
formula too
Kindly notice the spaces infront of the description
macro required , also need to know how it works with
formula too
Hi makinmomb
I don't know where I got this Code but you're welcome to itI don't know what this means![]()
Option Explicit Sub Main() Dim Ws As Worksheet Dim TrimRng As Range Dim LR As Long Set Ws = Worksheets("Sheet1") With Ws LR = .Range("B" & .Rows.Count).End(xlUp).Row Set TrimRng = Ws.Range("B1:B" & LR) Call TrimRange(TrimRng) Set TrimRng = Nothing End With End Sub Sub TrimRange(ByRef MyRng As Range) Dim vY 'as Variant With MyRng vY = Evaluate("IF(ROW(" & .Address & "),TRIM(" & .Address & "))") End With MyRng.Resize(UBound(vY, 1), 1).Value = vY End Sub
also need to know how it works with
formula
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Formula
In C1 type =TRIM(B1) and copy down.
VBA
![]()
Option Explicit Sub trimB() Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Row Dim i As Long For i = 1 To lr Range("B" & i).Value = Trim(Range("B" & i)) Next i End Sub
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
![]()
Option Explicit Sub sCleanSpaces() Dim lLR As Long lLR = Range("B" & Rows.Count).End(xlUp).Row With Range("C1:C" & lLR) .Formula = "=TRIM(B1)" .Value = .Value .Copy .Offset(0, -1) .Delete End With End Sub
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I notice that you have accessed the board a couple of times since these solutions were provided. Do they meet your needs?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Or, another yet shorter option:
![]()
Sub sCleanSpaces_RS() ' based on solution from RomperStomper in another thread ' http://www.excelforum.com/excel-programming-vba-macros/1015119-substitute-for-with-a-with-statement.html Dim lLR As Long lLR = Range("B" & Rows.Count).End(xlUp).Row Range("B1:B" & lLR).Value = Application.Trim(Range("B1:B" & lLR).Value) End Sub
Regards, TMS
I mark all threads solved , I use this commonly ,
I also add reputation to most answers and I use
these as daily work tools , I have only been busy
this morn since we have a gigantic sale starting
tomorrow
TMS the macro works super fine , thanks , I also
saved to my collection the trim of alansidman , short
and sweet
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks