• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Macro for TRIM

karthick87

karthick87

Member
Hi Geeks!

Go0d day.

Can i have a VBA Macro that trims all the values in the given sheet / Column?

Thanks in advance.
 
Code: 
Dim c As Range
For Each c In Range("A1:A10")
  c = Application.Trim(c)
Next

if you have a lot of blank cells you may want to use the slightly faster
Code: 
Dim c As Range
For Each c In Range("A1:A10")
  if c.text<>"" then c = Application.Trim(c)
Next
 
Hi Hui,

Thank you. But i would like to TRIM all the values in Sheet-1 "Column A", I cant specify a specific range there as it keeps changing. Any way?
 
Hi Karthick,

A little tweak in Hui's code will get you that.


Code: 
Dim c As Range, row as long
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 
For Each c In Range("A1:A" & row)
if c.text<>"" then c = Application.Trim(c)
Next

Cheers,
BD
 
Hi karthick...

Did you tried to run your code in your actual sheet.. (which is still invisible for us..)

If the worksheet is blank.. then..
row = ActiveSheet.Cells.Find(What:="*", After:=Cells(Cells.Rows.Count, Cells.Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row will be Nothing, and trying to get its Row property will cause error 91..

try this..

Code: 
Sub test()
  For Each C In Intersect(ActiveSheet.UsedRange, Columns(1))
  C.Value = Trim(C)
  Next
End Sub
 
Last edited:
Hi, karthick87!
You're using Debraj(ex-Roy)'s version, so keep it as it was originally, replacing this:
Sheet1.UsedRange("A")
by this:
Sheet1.UsedRange
Regards!
 
It's a quite old post, but i wanted to know a little tweak to the existing Macro. Now I wanted to TRIM two columns in the active sheet. Could some one help me with the modifications? Thanks.

Here is the current Macro that I am using for TRIM function which TRIMS only the columnA, now I wanted to TRIM both A and B..

Sub test()
For Each c In Intersect(ActiveSheet.UsedRange, Columns(1))
c.Value = Trim(c)
Next
End Sub
Click to expand...
 
Thank you Monty, that works like a charm :-) One more help, if possible.. Similarly is there way to clear the usedRanges from Column A1, Column B1, Column C1 and Column D1 using a Macro?
 
I am actually getting the below error on running the macro for clear.

Run-time error-'9':
Subscript out of range
Click to expand...

Also I have forgotten to mention, clearcontents should be applied starting from the UsedRanges from ColumnA2,ColumnB2,ColumnC2 & ColumnD2 as I have titles in A1,B1,C1,D1
 
Also I have forgotten to mention, clearcontents should be applied starting from the UsedRanges from ColumnA2,ColumnB2,ColumnC2 & ColumnD2 as I have titles in A1,B1,C1,D1

Current changes is clearing Column E as well (which is not needed).
 
Yes the clear button is working fine. It was my mistake while copy/pasting the macro's from one excel sheet to another.. The clear works fine.
 
You must log in or register to reply here.
Back
Top