replacing values in the worksheet

[baha17]

Good Morning Everyone,
I am working on a project which is hopefully leading to end soon. There are some codes which slows down the whole operation. I tried to identify those code and I found one of the code takes about a minute. The purpose of that code to clear the spaces that comes from database by exporting it to excel file, then putting 0 or * on those cells. I used several for each loop in that code.I am just wondering is there any way to run that code faster?
Thank you very much for the help.Here is my code

Sub GivingBlankFigures_Swing()
Dim cel As Range
Dim LastRow As Long
LastRow = Sheets("SwingFloorMap").Range("A65536").End(xlUp).Row
For Each cel In Sheets("SwingFloorMap").Range("D2:K" & LastRow)
If cel = "         " Then   'there are 9 spaces here
cel = 0
End If
If cel = "" Then
cel = "*"
End If
Next cel
For Each cel In Sheets("SwingFloorMap").Range("L2:L" & LastRow)
If cel = "         " Then   'there are 9 spaces here

cel = 0
End If
If cel = "" Then
cel = 0
End If
Next cel
For Each cel In Sheets("SwingFloorMap").Range("M2:O" & LastRow)
If cel = "         " Then 'there are 9 spaces here

cel = "*"
End If
If cel = "" Then
cel = "*"
End If
Next cel
For Each cel In Sheets("SwingFloorMap").Range("P2:P" & LastRow)
If cel = "         " Then
cel = 0
End If
If cel = "" Then
cel = 0
End If
Next cel
For Each cel In Sheets("SwingFloorMap").Range("Q2:S" & LastRow)
If cel = "         " Then
cel = "*"
End If
If cel = "" Then
cel = "*"
End If
Next cel
End Sub

 

[rjwebgraphix]

From the best I can tell from the code, the full range covers from D2 to S & lastrow. I don't see any skipped columns, so you could easily get away with cutting it down to a single loop. I'm sure it will save some time.

Sub GivingBlankFigures_Swing()

Dim cel As Range
Dim LastRow As Long
Dim rng As Range

LastRow = Sheets("SwingFloorMap").Range("A65536").End(xlUp).Row
Set rng = Sheets("SwingFloorMap").Range("D2:S" & LastRow)

For Each cel In rng
    If cel = "         " Then   'there are 9 spaces here
        cel = 0
    End If
    If cel = "" Then
        cel = "*"
    End If
Next cel

End Sub

 

[excelpunk]

Select all the range where you want to replace '9 spaces' to 0 and do a replace all (use exact match for better results).. similarly for other replacements.
You can record a macro if you want vba code for this.

 

[rjwebgraphix]

If for any reason you need to skip columns, you can also set your range like this, which will allow you to pick and choose your ranges and still only run through the loop once.

I just did 10200 rows in all of about 5 seconds.

Punk's suggestion of find and replace is good, but the unfortunate part is that, the best I can tell, you can't really find an empty cell to replace with the * and end up having to run it through a loop anyway. If you're already having to run through the loop anyway, taking care of both in one shot really wouldn't be faster one way or the other. You'll save most of your time with just a single loop.

Sub GivingBlankFigures_Swing()

Dim cel As Range
Dim LastRow As Long
Dim rng As Range

LastRow = Sheets("SwingFloorMap").Range("A65536").End(xlUp).Row
Set rng = Sheets("SwingFloorMap").Range("D2:K" & LastRow & _
                                       ",L2:L" & LastRow & _
                                       ",M2:O" & LastRow & _
                                       ",P2:P" & LastRow & _
                                       ",Q2:S" & LastRow)

For Each cel In rng
    If cel = "         " Then   'there are 9 spaces here
        cel = 0
    End If
    If cel = "" Then
        cel = "*"
    End If
Next cel

End Sub

 

[rjwebgraphix]

Ignore my blabbering about find & replace. Tested at 55300 rows. Loop method took 29 seconds. Find and replace method took 16 seconds.

Here's the Find and Replace method.

Sub GivingBlankFigures_Swing()

Dim cel As Range
Dim LastRow As Long
Dim rng As Range

LastRow = Sheets("SwingFloorMap").Range("A65536").End(xlUp).Row
Set rng = Sheets("SwingFloorMap").Range("D2:S" & LastRow)

rng.Replace What:="         ", Replacement:="0", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
rng.Replace What:="", Replacement:="*", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

End Sub

 

[baha17]

Hi,
First of all thank you very much for your inputs, I replaced it with the code in the last post from rjwebgraphix and it runs alot faster now. Thank you very much.
Maybe I need to retype some of the other codes too.
Have a good day

 

[rjwebgraphix]

Maybe I need to retype some of the other codes too.

I recently went through my main project and did the same thing and cut a macro that took 10 min to run down to about a minute through various techniques I picked up here and there.