Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to create a vba code to run on the sheet to automatically remove all spaces in cells B3, B4 and B12?

Posted on 2014-11-03
7
Medium Priority
?
135 Views
Last Modified: 2014-11-04
I need a macro to automatically run on sheet1 to automatically remove all spaces in cells B3, B4 and B12.
0
Comment
Question by:kbay808
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:ReneD100
ID: 40420044
Clarify 'remove spaces' please. Should 'This is a test' become 'Thisisatest'? Or leading spaces? Or fields contain no data but spaces?
0
 

Author Comment

by:kbay808
ID: 40420054
Before
   This is a test   1

After
Thisisatest1
0
 
LVL 5

Accepted Solution

by:
ReneD100 earned 2000 total points
ID: 40420087
There are a few ways to do this, here's an example.
In Sheet1 VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        If Target.Row = 3 Or Target.Row = 4 Or Target.Row = 12 Then
            Target.Value = Replace(Target.Value, " ", "")
        End If
    End If
End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:kbay808
ID: 40420193
It works great, but it won't work unless I delete the existing macro already in that sheet.  How do I combine the two codes into one?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range, c As Range
    Dim cVal

    Const myR As String = "B5,B9" '<- Your range(s)
    
    Set changed = Intersect(Target, Range(myR))
    If Not changed Is Nothing Then
        Application.EnableEvents = False
        For Each c In changed
            cVal = c.Value
            Select Case True
                Case IsEmpty(cVal), IsNumeric(cVal), _
                        IsDate(cVal), IsError(cVal)
                    ' Do nothing
                Case Else
                    c.Value = UCase(cVal)
            End Select
        Next c
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
 
LVL 5

Expert Comment

by:ReneD100
ID: 40421486
I see you already asked this question somewhere else and got an answer. So you're all set.
0
 

Author Comment

by:kbay808
ID: 40421890
Yes, thank you
0
 

Author Closing Comment

by:kbay808
ID: 40421891
Thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question