Removing spaces from value using VBA

[Guest]

I need to define a value for rangename. I have this working so far:

rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" & i).Value
Debug.Print rangename

Unfortunately, the range name has SPACES in it and when I use it to create a
named range, it GACKS. How do I remove the spaces from this?

Thanks,
Barb Reinhardt

 

[OZDOC]

look at the trim function it may help

 

[Guest]

Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am not
sure how to add it to what I have. Suggestions?

Thanks,
Barb Reinhardt

 

[OZDOC]

Range("a" & i).Value

I am guessing this is where the name comes from so this reference cell can
youshow me what you have in it ? formula ?

 

[Joe HM]

Hello -

You can use the following ...
rangename = Instr(rangename, " ", "")

Joe

 

[Joe HM]

Darn ... I must be stupid ... here we go ...
rangename = Replace(rangename, " ", "")

Sorry about that ...
Joe

 

[Guest]

Perhaps this will help... Note Application.Trim and Trim are different. You
probably want application.substitute

Sub TrimTest()
Dim str As String

str = " This Is Only A Test "
MsgBox Trim(str)
MsgBox Application.Trim(str)
MsgBox Application.Substitute(str, " ", "")
End Sub

 

[Guest]

The value in this cell is something like "Company Name" and I need to change
it either to "CompanyName" or "Company_Name". It's printing what's there,
but range names can't have spaces and I need to remove them. I'd like to
know how I use the SUBSTITUTE function with this

rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" & i).Value

So that "Company Name" can be converted to "Company_Name".

 

[Joe HM]

Here we go ...
rangename = Replace(Workbooks(curbook).Worksheets(datasheet).Range("a"
& i).Value, " ", "_")

Joe

 

[OZDOC]

Jim, the reason wanted to see the formula in the cell is it may be possible
to re write it to fix the problem,

i.e. you could if it is looking up a reference trim from that ref point, you
could use find to find the space then use left function right function etc,
but it is hard to sort something without this, in other words fix it before
the macro not in the macro ? don't know if it was you want but just an idea