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
123 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Welcome to part one of a multi-part tutorial series, VBScript for Windows System Administrators.  The goal of this series is to teach non-programmers how to write useful VBS code to automate their environment, and perform tasks faster, and in a more…
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 …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now