Macro for Excel 2007 Thread poster: Daniel Pestana
|
|---|
Daniel Pestana 
Portugal
Local time: 02:10
Member (2010)
English to Portuguese
+ ...
Hi everyone,
I was wondering if any of you knows how to create Macros in Excel. I'm still learning how to master the VBA language, but I can't get this one to work. I'm building a glossary and when I paste the entries into the cells I have several empty spaces prior to the entry itself, for example:
1 [4 empty spaces] shares
2 [6 empty spaces] debentures
I need to create a macro that manages to erase only those empty spaces before the entry, meaning, ... See more Hi everyone,
I was wondering if any of you knows how to create Macros in Excel. I'm still learning how to master the VBA language, but I can't get this one to work. I'm building a glossary and when I paste the entries into the cells I have several empty spaces prior to the entry itself, for example:
1 [4 empty spaces] shares
2 [6 empty spaces] debentures
I need to create a macro that manages to erase only those empty spaces before the entry, meaning, it should erase the empty spaces until the first character occurs.
Is this possible? Maybe there's a function to do this, but I couldn't find it. I tried to create a small VBA program using TRIM, but it didn't work. Example:
Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub
Thanks in advance for your help.
Best. ▲ Collapse
| | | | | One solution | Sep 26, 2010 |
Public Sub RemoveSpaces()
Application.ActiveCell.Value = Trim(Application.ActiveCell.Text)
End Sub
This should do. If you want to remove the spaces before the word use LTrim.
Cheers,
Narcís
[Edited at 2010-09-26 20:06 GMT]
| | | | Daniel Pestana
Portugal
Local time: 02:10
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER | Thank you for your quick reply Narcis | Sep 26, 2010 |
Hi Narcis,
It's a pleasure to e-meet you. Your solution gave me the answer to what was wrong with my code. Thank you. I tried both codes, yours and mine, but unfortunately none of them worked. So here's how I solved the problem:
Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
... See more Hi Narcis,
It's a pleasure to e-meet you. Your solution gave me the answer to what was wrong with my code. Thank you. I tried both codes, yours and mine, but unfortunately none of them worked. So here's how I solved the problem:
Sub TrimEText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
Dim MyCell As Range
On Error Resume Next
For Each MyCell In Selection.Cells
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), " ", "")
Next
On Error GoTo 0
End Sub
This one works fine, although it still has some bugs (you need to run the macro a couple of times until all the empty spaces are removed). The best thing about this macro is it works with a range of cells, while the other code was only for a single active cell. I truly appreciate your help. Thank you very much.
Best.
[Edited at 2010-09-26 22:37 GMT] ▲ Collapse
| | | | | A quick suggestion without so much research... | Sep 27, 2010 |
How about a search and replace for any " " to ""...???
double space to nothing...
????
| | |
|
|
|
| function to get rid of unecessary empty spaces | Sep 27, 2010 |
There is a nice Excel function that eliminates all empty spaces, except for single empty spaces between the words: TRIM. It is in the list of text functions.
[Edited at 2010-09-27 08:56 GMT]
| | | | | I wouldn't use a macro at all... | Sep 27, 2010 |
If you do not want to clean up excessive spaces inside the entries you can just use a formula in the cell next to the one you're trying to clean up, e.g. write "=TRIM(A1)" in cell A2 and then propagate that downwards.
Copy the resulting column to the clipboard and Paste Special as Text to receive cleaned-up entries that you can then move to the original column.
HTH,
Benjamin
P.S.: Oscar's idea is not b... See more If you do not want to clean up excessive spaces inside the entries you can just use a formula in the cell next to the one you're trying to clean up, e.g. write "=TRIM(A1)" in cell A2 and then propagate that downwards.
Copy the resulting column to the clipboard and Paste Special as Text to receive cleaned-up entries that you can then move to the original column.
HTH,
Benjamin
P.S.: Oscar's idea is not bad, but fraught with the problem that there might be spaces missing in the end!
[Edited at 2010-09-27 10:55 GMT] ▲ Collapse
| | | | Daniel Pestana
Portugal
Local time: 02:10
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER | The Macro I wrote does that | Sep 28, 2010 |
oscarvil wrote:
How about a search and replace for any " " to ""...???
double space to nothing...
????
Hi Oscarvil,
Thanks for your reply. Yes, the macro I wrote does that. I use the function SUBSTITUTE.
Thanks for your help.
Best,
Daniel
| | | | Daniel Pestana
Portugal
Local time: 02:10
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER | TRIM doesn't work on the Active Cell | Sep 28, 2010 |
elena_patineuse wrote:
There is a nice Excel function that eliminates all empty spaces, except for single empty spaces between the words: TRIM. It is in the list of text functions.
[Edited at 2010-09-27 08:56 GMT]
Hi Elena,
Thanks for your suggestion. The purpose of the Macro is to avoid using functions on other columns (cells). If I use that function (or LTRIM) I populate another column with the same information. I want to use it on the active cells.
Thanks for your help. Truly appreciate it.
Best,
Daniel
| | |
|
|
|
Daniel Pestana
Portugal
Local time: 02:10
Member (2010)
English to Portuguese
+ ...
TOPIC STARTER | TRIM doesn't work on the Active Cell | Sep 28, 2010 |
tectranslate wrote:
If you do not want to clean up excessive spaces inside the entries you can just use a formula in the cell next to the one you're trying to clean up, e.g. write "=TRIM(A1)" in cell A2 and then propagate that downwards.
Copy the resulting column to the clipboard and Paste Special as Text to receive cleaned-up entries that you can then move to the original column.
HTH,
Benjamin
P.S.: Oscar's idea is not bad, but fraught with the problem that there might be spaces missing in the end!
[Edited at 2010-09-27 10:55 GMT]
Hi Benjamin,
As I said to Elena, the purpose of the Macro is to avoid using functions on other columns (cells). If I use that function (or LTRIM) I populate another column with the same information. I want to use it on the active cells. It saves a little bit of time. Besides, I want to share this Macro with some colleagues that have a hard time while handling Excel. A sort of Open Source Macro
Thanks for all your help Benjamin. Truly appreciate it.
| | | | | a small modificaton | Sep 28, 2010 |
In order to avoid repetitive running of your macro, you can use (I only show a replacement of your cycle's body) :
For Each MyCell In Selection.Cells
sstr = Trim(MyCell.Value)
oncemore:
sstr = Replace(sstr, " ", "")
wh = InStr(sstr, " ")
If wh 0 then goto oncemore
MyCell.Value = sstr
Next
That will remove all spaces. Or this variant (I write [two spaces] where you should acutally type two spaces, I am afraid html code of this fo... See more In order to avoid repetitive running of your macro, you can use (I only show a replacement of your cycle's body) :
For Each MyCell In Selection.Cells
sstr = Trim(MyCell.Value)
oncemore:
sstr = Replace(sstr, " ", "")
wh = InStr(sstr, " ")
If wh 0 then goto oncemore
MyCell.Value = sstr
Next
That will remove all spaces. Or this variant (I write [two spaces] where you should acutally type two spaces, I am afraid html code of this forum will only show one space?):
For Each MyCell In Selection.Cells
sstr = Trim(MyCell.Value)
oncemore:
sstr = Replace(sstr, "[two spaces]", " ")
wh = InStr(sstr, "[two spaces]")
If wh 0 then goto oncemore
MyCell.Value = sstr
Next
should remove all leading and training spaces and leave only one space inside the cell. From your description, I can't see which of them you actually need/want.
Antonin ▲ Collapse
| | | | | | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Macro for Excel 2007 | Anycount & Translation Office 3000 | Translation Office 3000
Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.
More info » |
| | Trados Studio 2022 Freelance | The leading translation software used by over 270,000 translators.
Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop
and cloud solution, empowering you to work in the most efficient and cost-effective way.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
