VBA delete spaces within a string

Question

I want to be able to delete all of the spaces from a group of cells so that the cell contents result in just being a string of information that I want. I have come up with a code but it doesn't work and I can't figure out whether I have to use a replace function or not

My code is:

Sub Tester()

Dim SearchString As String
Dim cell As Range
Dim LastRowSource As Long
Dim ws1 As Worksheet

Set ws1 = ActiveWorkbook.ActiveSheet

SearchString = " " 'look for strings containing ( )

With ws1

    LastRowSource = .Cells.Find(" ", [B2], , , xlByRows, xlPrevious).Row

    For Each cell In .Range("B2:B" & LastRowSource)

            If InStr(cell.Value, SearchString) > 0 Then
                cell.Value = Replace(" ", "")
         End If
    Next cell
End Sub

You don't need VBA, just use Find and Replace from the sheet. — SJR
– SJR, Commented Jan 10, 2018 at 10:57

Andy G · Accepted Answer · 2018-01-10 11:02:15Z

1

If you need to do this programmatically then, recording a Macro using the Replace dialog generates this code:

Sub Macro1()
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Substitute your range for Selection. You do not need to iterate each cell.

answered Jan 10, 2018 at 11:02

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rory · Accepted Answer · 2018-01-10 11:03:56Z

0

You don't need to loop:

With ws1
    .Range("B2", .Cells(.rows.count, "B").End(xlUp)).Replace " ", vbnullstring, xlpart
End With

answered Jan 10, 2018 at 11:03

Rory

34.2k5 gold badges40 silver badges42 bronze badges

2 Comments

user9103716 Over a year ago

how do i specify this to work on the whole worksheet? @Rory

Rory Over a year ago

Just: ws1.Cells.Replace " ", vbnullstring, xlpart

Vityata · Accepted Answer · 2018-01-10 11:01:34Z

Try like this:

LastRowSource = 10000. If it works, then the problem is the way you are setting the last row. Thus, add the LastRow() function to your code, it should work:

LastRowSource = LastRow ws1

Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function

Function from here - https://www.rondebruin.nl/win/s9/win005.htm

Collectives™ on Stack Overflow

VBA delete spaces within a string

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related