VBA Replace does not work

[PedroVision]

tempRowCounter = Replace(tempRowCounter, " ", "")

Trying to remove all spaces from a text string...

I've tried 67 different ways...and all I keep getting from VBA is...see image...


I just think this is, or should be difficult...but I'm not having any luck getting past this...any help would be humbly appreciated!

Many thanks...

 

[Macropod]

There is no image, but neither is there anything wrong with the code you posted - as far as it goes.

 

[PedroVision]

There is no image, but neither is there anything wrong with the code you posted - as far as it goes.

I don't there's anything wrong with it either...BUT...it doesn't work...see image...

(Thank you for replying...I'M STUCK!!)

 

[Macropod]

The code as posted works for me:

Sub Test()
Dim tempRowCounter As String: tempRowCounter = "Hello World. Today is a beautiful day"
tempRowCounter = Replace(tempRowCounter, " ", "")
MsgBox tempRowCounter
End Sub

 

[jolivanes]

This works for me without error. Change references as required.

Sub Maybe_So()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Value = Replace(c.Value, " ", "", 1)
    Next c
End Sub

Forgot the F5 again. Sorry Paul.

 

[Scott Huish]

This works for me without error. Change references as required.

Sub Maybe_So()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        c.Value = Replace(c.Value, " ", "", 1)
    Next c
End Sub

Forgot the F5 again. Sorry Paul.

That could be done without a loop.

Sub Maybe_More_So()
Range("A:A").Replace " ", ""
End Sub

 

[PedroVision]

Another example...purported to work...It does not work on my computer...Excel 2019...W11...nothing exotic...

Remove all spaces from text​

Trim will only remove extra spaces in between words, but to remove all spaces in a string of text, you can use the Replace Function:

Sub ReplaceExample
MsgBox Replace " I love excel ", " ", "")
'Result is: "Iloveexcel"
End

VBA Trim, LTrim, and RTrim Functions - Remove Spaces From Text

This tutorial will demonstrate how to use the Trim, LTrim, and RTrim VBA functions as well as the Trim worksheet function. Trim Function The VBA Trim

www.automateexcel.com

 

[PedroVision]

I'm in VBA...trying to write data to my spreadsheet...

I need to remove the blank spaces from my data BEFORE it gets written to the worksheet.

MyData = Replace(MyData, " ", "")

What am I missing here??

 

[Skyybot]

What is the value of MyData at this line of code when you step through it? Is MyData of String data type?

 

[PedroVision]

What is the value of MyData at this line of code when you step through it? Is MyData of String data type?

Here is another example...again, it does not work...

Sub ReplaceExample
MsgBox Replace " I love excel ", " ", "")
'Result is: "Iloveexcel" <----NOPE! IT DOESN'T WORK
End

I am in VBA...data is about to be loaded into a listbox...I am working with row numbers...and preparing to write those row numbers into the first (of three) column...BUT I must make them all the same length by adding leading zeros...when I take Row 1-9, I need to add 3 leading zeros the row number...for some reason when adding the "000" to the row number, Excel insists on inserting a blank space in between...thus, "000 1"...I need to remove this blank space BEFORE writing to my listbox.