VBA: Question

[SteveOranjin]

Hello There,

I'm learning a lot about VBA but I have a quick question.

If a macro can be performed more than once on a given cell, will it be performed as many times as the rules which govern it allow? Or will it only make that initial run through and then have to be run again to get the rest.

An example: If I have fifty spaces in a cell, and I set up a macro to turn all Double Spaces (that is "_""_" lines added for clarity) into single spaces - will the macro run and reduce the spaces in the cell from 50 to 25, and then proceed to the next cell and perform the function there, or if it reduces the number of spaces to 25, and detects that there are more spaces, it will continue to delete them until there are none left.

Steve

 

[mole999]

you could run this twice

    Range("A1:J10000").Select
    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Selection.Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[Joe4]

you could run this twice

Running it just twice won't get you down from 50 to 1.

You could make use of the Find command, which returns an error if it cannot find what it is looking for. Then, we could put the code in a Loop, and tell it to exit when it finds an error (meaning that there are no more to find).

That code might look something like this:

Sub GetRidOfMultipleSpaces()

    On Error GoTo exit_loop
    Do
        Range("A1:J10000").Find(What:="  ", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        Range("A1:J10000").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Loop
    
exit_loop:
    Err.Clear
    On Error GoTo 0
    
    MsgBox "Done!"
        
End Sub

 

[RoryA]

If you are trying to trim the contents of cells, you can use Application.Trim in a loop through the cells, rather than having to process the cells repeatedly.

 

[Eric W]

Depending on what your actual goal is, you could use one of the built-in functions.

    Range("A1").Value = Worksheetfunction.Trim(Range("A1").Value)

will compress all multiple spaces down to 1. A string of all spaces will turn to an empty string though.

 

[Joe4]

If you are trying to trim the contents of cells, you can use Application.Trim in a loop through the cells, rather than having to process the cells repeatedly.

I have used TRIM many times for extra spaces at the beginning or end of entries. I am embarrassed to say that I never realized it would work on spaces in the middle of entries too!

So I learned something new today!

 

[SteveOranjin]

I have used TRIM many times for extra spaces at the beginning or end of entries. I am embarrassed to say that I never realized it would work on spaces in the middle of entries too!

So I learned something new today!

I just want to make sure I am understanding this correctly. It will only go through the first iteration of that and reduce the number of spaces down to 25, correct?

 

[Joe4]

I am not sure which one you want to happen, just go down to 25 and go all the way down to 1.

If you just want 25, then simply run through it once, like:

        Range("A1:J10000").Replace What:="  ", Replacement:=" ", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

If you want it to go down to 1, using the TRIM method mentioned by Rory and Fluff would be the most efficient way.

 

[Fluff]

If you want it to go down to 1, using the TRIM method mentioned by Rory and Fluff would be the most efficient way.

Shouldn't that be Rory & Eric

 

[SteveOranjin]

Shouldn't that be Rory & Eric

I wasn't looking for an answer. What I was curious about was the answer to the question. I wanted to know if - from a conceptual standpoint - each VBA code is performed only once or if it was performed in each cell it evaluates until there are no more evaluations for it to make.

Steve