I need help to check why below VBA code take longer time while execute, its being more faster once test at first trials but now its take may be 10 - 20 mints although the data not more than 500 Row & 15 Columns

Sub Cell_Formatting()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim ws As Worksheet

    For Each ws In Worksheets
        If ws.Name = "1" Or ws.Name = "2" Or ws.Name = "3" Or ws.Name = "4" Then
            ws.Select

            lastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
            Range("A1:O" & lastRow).Select

            For Each self In Selection.SpecialCells(xlCellTypeConstants)
                With Selection
                    .Borders.Weight = xlThin
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
            Next
    
        End If
    Next ws
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

I don't believe you need a loop over each individual cell:

For Each ws In Worksheets
   If ws.Name = "1" Or ws.Name = "2" Or ws.Name = "3" Or ws.Name = "4" Then
       Dim rng As Range
       Set rng = Nothing

       On Error Resume Next
       Set rng = ws.Columns("A:O").SpecialCells(xlCellTypeConstants)
       On Error GoTo 0

       If not rng Is Nothing Then
           rng.Borders.Weight = xlThin
           rng.HorizontalAlignment = xlCenter
           rng.VerticalAlignment = xlCenter
       End If
   End If
Next