Slow Running Code?

[Prevost]

Hi There. I am running the code shown below and it is going incredibly slow. When I manually run the code (pressing F8) I notice there is a long pause when the Cell length = 6 between the lines ' Cell = MyLeft & ".00" ' and the 'End If' statement. Can anyone see why? Does the size of the file have an impact on how quickly the code runs (this file is 34 MB)? Or how many lines of code are in a module?

I am just cleaning up/formatting data in a spreadsheet and I have written small macros like this before and never had any issues.

Thanks.

Sub FormatUWFrameWindingData()

    Dim MyLeft, MyRight
    Dim Cell As Range
    
        For Each Cell In Selection
            If Len(Cell) = 6 Then
                MyLeft = Cell
                Cell = MyLeft & ".00"
            ElseIf Len(Cell) = 8 Then
                MyLeft = Left(Cell, 6)
                MyRight = Mid(Cell, 7, 2)
                Cell = MyLeft & "." & MyRight
            End If
        Next Cell
        
End Sub

 

[Fazza]

Maybe it is running slowly because it loops through every cell in a selection? This is inherently slow. Reading & writing one cell at a time. I think that would be the main reason. Additionally variables are not dimmed. Checking LEN() is a fast operation, so that is good to see. I prefer explicit properties (like cell.value2) rather than assuming defaults (such as MyLeft = Cell) and avoiding the name Cell for a variable.

Without knowing what is being done it is difficult to advise changes. Maybe the loop (cell by cell in selection) can be avoided such as by borrowing a worksheet range and filling with a formula, then copy the values to the destination cells, then erase the borrowed range. Or if a loop is absolutely required, temporarily using an array - so read the selection to an array, loop through & modify, write back to worksheet. This has just one worksheet read & one worksheet write operation.

regards