VBA Trim

[lazenca]

Hi I would like to have a simple basic VBA trim function with no complications at all. Just a simple trim vba code like:

=trim( Selected/Active cells)

But I just couldn't think of how to do it, even with recording.

Thanks

[GOOS]

This is about as simple as you can get...

Sub VBATrim()

ActiveCell = Trim(ActiveCell)

End Sub

[lazenca]

lol Cool thanks!

Thats just what I need.

Hmm ok now a little bit of challenge to add to this function. I don't know if its just me but any of u guys knew that when you do a manual trim on excel itself, it will not remove leading 0's infront of any string.

However, if use a trim from the VBA function, the 0's would be removed.

Any solutions?

Thanks

[shg]

Trim doesn't remove leading zeros; it removes leading and trailing spaces. See Help.

The WorksheetFunction.Trim and VBA Trim functions are different. Help explains both.

[GOOS]

I've passed an integer to the 'Trim' function in the 'Immediate' window and it did remove the leading zeros. I didn't find any specific mention of this in either 'Help File' Reference.

However, if the ActiveCell is formatted as a number, the leading zeros would be removed on input in the sheet. If formatted as 'Text', a string value is passed to the VBA function thereby alleviating the concern for dropping zeros.

Are you having a practical issue that I'm not understanding?

[lazenca]

Hi once again, if I am not wrong, the default cell formats would be a General Format, so lets say I did a trim with the VBA function, it will remove the leading zeroes.

Well of course I can change it into text format first before I do anything but this means that I need to change it for every single worksheet that I need to work on. I need to save as much time as possible.

Thanks

PS: Hey Goos, the trim function seem to work only on one cell. Lets say I would like to select multiple or a whole column/row, is it possible to edit?

[GOOS]

I think your situation must be that the data has Leading Apostrophes. Without converting the cells to anything other than 'General', you can implement a numeric check, as well as cycle through all cells in a selection this way:

Please Login or Register  to view this content.

I've tried this with a few different types of cells and I think it works as you'll need. Just understand that if there is a function or formula in the cell, you'll wind up getting the results' values.

[lazenca]

Hey goos, it works! =)

Well it will be best if you can explain to me the syntax and logic behind this coding of yours cuz I am still new at VBA and I wanna get as much exposure as possible, its wonderful =)

Thanks

[GOOS]

I'd love to. If you mark this post as resolved, I'll be happy to do a private thing so we don't pollute the postings. I'll send you a private message and you can send me an e-mail if you like. ~R

[shg]

Please keep the exchanges in the forum -- that's the point of a forum.

[GOOS]

Well, okay. I'll comment the following:

Please Login or Register  to view this content.

In the line that says 'rngCell = "'" & Trim(CStr(rngCell))', t'he ampersand '&' is basically a textual operator, like '+' or '*' are mathematical operators. It says 'Take whatever is in front of me, and stick it to whatever is after me.

The 'For Each' and 'Next' lines are wrappers which work together to do everything in between the two lines multiple times.

Not sure if this helped. If not, just let me know.

[lazenca]

Please Login or Register  to view this content.

Hmm I am not quite sure about this part. You see, when there are leading zeroes in the cell, it is only visible when its cell format is in 'Text'. But for this case, it asked if it is a 'Number'?

Explain please.

Thanks alot