Go Premium for a chance to win a PS4. Enter to Win

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
?
133 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
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

Industry Leaders: 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!

Question has a verified solution.

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

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 …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

885 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