Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

705 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