Macro Too Slow

[DanG]

I have acquired this macro and on a small (>1000 rows?) scale works great.
However, I have 43,000 rows X 10 columns or so and that's where things get ugly, I have not been patient enough to see how long it would take.

I am new to VBA, but went through the basics to check if it had calculating & screenupdating set (and it does). Is there a better way to do this or any adjustments that can be made to make it faster?

Sub FixProper()

    Dim LCell As Range
        

    'Turn off screen updating to increase performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

        
        'Convert all constants and text values to proper case
        For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
            LCell.Formula = StrConv(LCell.Formula, vbProperCase)
        Next
        
        
    For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
            
        '--- this is where you would code generalized changes for lastname
        '--- applied to names beginning in position 1 of cell
        If Left(LCell.Value, 2) = "Mc" Then LCell.Value = _
           "Mc" & UCase(Mid(LCell.Value, 3, 1)) & Mid(LCell.Value, 4, 99)
        If Left(LCell.Value, 3) = "Mac" _
               And Left(LCell.Value, 4) <> "Mack " Then LCell.Value = _
            "Mac" & UCase(Mid(LCell.Value, 4, 1)) & Mid(LCell.Value, 5, 99)
           '-- do not change Mack   Mackey  Mackney  or any Mack...
        If Left(LCell.Value, 2) = "O'" Then LCell.Value = _
           "O'" & UCase(Mid(LCell.Value, 3, 1)) & Mid(LCell.Value, 4, 99)
        If Left(LCell.Value, 6) = "Po Box" Then LCell.Value = _
            "P.O." & Mid(LCell.Value, 3, 99)
        If Left(LCell.Value, 4) = "P.o." Then LCell.Value = _
            "P.O." & Mid(LCell.Value, 5, 99)
        If Left(LCell.Value, 5) = "P.O.b" Then LCell.Value = _
            "P.O. " & Mid(LCell.Value, 5, 99)
      Next

MsgBox "Please double check for potential errors." & vbCr & "This does not guarantee accuracy for people's names.", vbExclamation, "Reminder"

    'Turn screen updating back on
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Thank you

 

[Brianwarnock]

A quick look suggests every cell goes through every check. Once you have a hit no more checks can be true therefore I would change the one line Ifs to block ifs and after a hit GoTo Loopnext: label just prior to the Next command.

Brian

 

[Brianwarnock]

A second look shows that the main promlem is that you loop through all of the cells twice move

'Convert all constants and text values to proper case
                   LCell.Formula = StrConv(LCell.Formula, vbProperCase)

into the start of the second loop and get rid of the first.

Oh! and I think thst there should be no space here

<> "Mack "


Brian

 

[DanG]

Hi Brian,

Thanks for the hints, I will give it a go. I understand pretty much what your saying I think.

But, in troubleshooting it, I had just used the first part of the code and was having the same issue.

First part usded...

  For Each LCell In Cells.SpecialCells(xlConstants, xlTextValues)
            LCell.Formula = StrConv(LCell.Formula, vbProperCase)
        Next

 

[Brianwarnock]

Those 486s are pretty slow

I created 43000 * 10 mcclay, did the changes I suggested and ok I was going to get a hit on the first check but my 7 year old 1600mhz took just over 1 min to finish the change.

Brian

Just remembered I also changed the last mid to only handle the correct length ie for "mc" Mid(LCell.Value, 4, len(lcell)-3)

 

[DanG]

I work for one of the largest companies in the world and so I think you can be rest assured that as far as the equipment goes...definately suspect.

Thank you again I'll attempt to modify as you advised and let you know.

Thanks!