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
126 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 500 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 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