CODEWITHSUNDEEP
excelvbafor-loopfreeze
Jesse
Jesse

Reputation: 7

Problems with a for-loop statment that almost freezes my program

I have two for-statements that work, however, they go through so many cells that they end up freezing my excel for about 15 sec to half a min. I need help finding a way to run the code without freezing excel.

One of my codes does a formula if the statement is correct in the cell (running from row 5 down an unstated amount of rows). And my second code changes the NumberFormat to "0,00", which is problamatic cause I have a lot of cells with numbers in them. It would probably be best to write the code in a different way than with a For-If statement, but I wasn't able to get it to work any other way than what I have below. I've tried adding an DoEvents, which changes nothing, the program still freezes.

First code which causes my program to run slow:

For x = 5 To Rows.Count
If Application.WorksheetFunction.IsNumber(ws.Cells(x, 2)) = True Then
    ws.Cells(x, 14).FormulaR1C1 = "Formula 1"
    ws.Cells(x, 15).FormulaR1C1 = "Formula 2"
    ws.Cells(x, 16).FormulaR1C1 = "Formula 3"
    ws.Cells(x, 17).FormulaR1C1 = "Formula 4"
    ws.Cells(x, 18).FormulaR1C1 = "Formula 5"
ElseIf Application.WorksheetFunction.IsNumber(ws.Cells(x, 2)) = False Then
    Exit For
End If
Next x

Second code which causes my program to freeze for a few seconds before finishing:

For y = 2 To Columns.Count
For x = 5 To Rows.Count
If Application.WorksheetFunction.IsNumber(ws.Cells(x, y)) = True Then
    ws.Cells(x, y).NumberFormat = "0.00"
    ws.Cells(x, y).HorizontalAlignment = xlCenter
    ws.Cells(x, y).VerticalAlignment = xlCenter
Else
    Exit For
End If
Next x, y

Upvotes: 0

Views: 45

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

First of all a "freeze" means in most cases that your code is still running (which can be a longer time than you expect).

The issue in your code is, that you run your loop through all rows/columns even if they are not used (empty).

For x = 5 To Rows.Count

Limit your loops to the actual amount of data. Find the last used row like

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'last used row in column A

and use this in your loop 

Dim iRow As Long
For iRow = 5 To LastRow

You can also find the last used column with the same method

Dim LastCol As Long
LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 'last used column in row 1

and use this in your other loop 

Dim iCol As Long
For iCol = 2 To LastCol

So you would end up with something like

Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'last used row in column A

Dim LastCol As Long
LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column 'last used column in row 1 

Dim iRow As Long, iCol As Long
For iCol = 2 To LastCol 
    For iRow = 5 To LastRow
        If Application.WorksheetFunction.IsNumber(ws.Cells(iRow, iCol)) = True Then
            ws.Cells(iRow, iCol).NumberFormat = "0.00"
            ws.Cells(iRow, iCol).HorizontalAlignment = xlCenter
            ws.Cells(iRow, iCol).VerticalAlignment = xlCenter
        Else
            Exit For
        End If
    Next iRow
Next iCol

Note that I gave your counter variables x and y more meaningful names so you always know which is the row counter and which the column counter.

Alternative to your loop

Use the Range.SpecialCells method to find all cells with numbers and format them at once wich should be amazingly faster.

Dim CellsWithNumbers As Range
Set CellsWithNumbers = ws.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

If Not CellsWithNumbers Is Nothing Then
    With CellsWithNumbers 
        .NumberFormat = "0.00"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
End With

If you want to find all formulas with a numeric result use xlCellTypeFormulas instead.

Dim CellsWithNumbers As Range
Set CellsWithNumbers = ws.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)

If Not CellsWithNumbers Is Nothing Then
    With CellsWithNumbers 
        .NumberFormat = "0.00"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
End With

If you want constant and formula numbers use both one after the other.

Upvotes: 1

Related Questions