ReDim or Not ReDim

[Mantle51]

Hello,

After much deliberation, I was able to write a routine that determined the length of characters(including " ") in a cell, then using mid statement replaced until there were no more " "
Dim j As Long, varVal As Variant, lVal As Variant
For j = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
varVal = Cells(j, 1).Value
lVal = Len(varVal)

Do Until Mid(varVal, lVal, 1) <> " "
If Mid(varVal, lVal, 1) = " " Then
Cells(j, 1).Value = Mid(varVal, 1, lVal - 1)
lVal = lVal - 1 'This line
End If
Loop

Next j


The problem I was having was resetting the Len(varVal). I kept trying to write:
Len(varVal)=Len(varVal)-1
but was not resetting the Length. Then I dimmed variant lVal and set it to equal variant varVal and this worked. Conceptually, this is new ground for me. By defining a variant from an existing variant, am I foraging into ReDim territory?

Thanks............Mickey

 

[SkipVought]

Hi,

So you're just trying to eliminate spaces within a string in values in cells?

How about Edit/Replace?

ReDim is used for ARRAYS.

"The problem I was having was resetting the Len(varVal). "

The problem is, you never reassign varVal.

Skip,

_[red][/red]

 

[xlbo]

reDim is an ARRAY function. you cannot use it on a string

The issue is that the LEN of a string is just a property. you can only change the string itself. Once this is done, the LEN will change.

Not entirely sure what the code is doing but if for instance, you were getting the last character from the end of the string and then wanted the one after that, you could use:

varVal = Left(varVal,len(VarVal)-1)

What is it that you are actually trying to do ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

You just wanted the RTrim function ?
For j = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
Cells(j, 1).Value = RTrim(Cells(j, 1)).Value
Next j

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Well, your code logic says this:

Loop through column A cells. For each cell in column A, loop through each character of that value. If you find a space then attempt to take the space out.

Problems I see there:
The inner loop is not looping backwards; when deleting/inserting, always go backwards. Also, too many loops. Getting rid of loops will generally and usually speed up your code amazingly and make it quite a bit more efficient. Don't use variables from an objects value when you can use the objects value itself. For example, don't use varVal when you can use Cells(j, 1).Value. There could be more issues but we won't know that if you haven't posted your entire code.

As to what a valid solution might be, it depends on what you're trying to do. If we go solely by your logic you're attempting here, you need the Replace method. Example...

Range("A1").Replace what:=" ", replacement:=""

Now you could loop through all cells in the range and do this, but you don't need to with the Replace method, you can do the entire range at once..

Range("A:A").Replace what:=" ", replacement:=""

Also check characters 160 (space is 32), as it's not a real " " match.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Mantle51]

Thanks very much for the input.