对于循环设置，“字体"和“范围内部"花费的时间太长 [英] For loop setting Font and Interior of Range taking way too long

问题描述

我的工作表中包含很多数据(近14.000行和13列).

I have a sheet with a lot of data (almost 14.000 rows and 13 columns).

我正在此工作表中运行For循环，但有时需要2分钟以上才能完成.同样，应用程序在For循环中没有响应.

I am running a For loop within this sheet but it takes sometimes over 2 minutes to complete it. Also the application is not responding during the For loop.

有没有一种方法可以重写我的循环，使其运行得更快?

Is there a way I can re-write my loop so it will run a lot faster?

这是我的代码:

For counter = 1 To Rows.Count
    If Cells(counter, 13).Value > 500 Then
        Cells(counter, 13).Interior.ColorIndex = 37
        Cells(counter, 13).Font.Color = Black
        Cells(counter, 13).Font.Bold = True
    End If
    count = count + 1
    Application.StatusBar = count
Next counter

先谢谢您了:).

推荐答案

避免在范围内循环.您可以通过遍历数组并在数组之后进行格式化来加快代码的速度.此外，您可以将状态栏计数的循环分成几部分.

Avoid looping through a range. You can speed up your code by looping through an array and do formatting after it. Furthermore you could split your loop for the status bar count into portions.

代码

Option Explicit

Public Sub Greater500()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")
Dim v As Variant
Dim i As Long, n As Long, m As Long, r As Long
Dim t As Double
' stop watch
  t = timer
' get last row in column M
  n = ws.Range("M" & ws.Rows.Count).End(xlUp).Row
' get values to one based 2dim array
  v = ws.Range("M1:M" & n).value
' clear existing colors over the WHOLE column to minimize file size
      ws.Range("M:M").Interior.ColorIndex = xlColorIndexNone

  For i = 1 To n
      ' avoid troubles with formula errors, e.g. divisions :/ zero
        If IsError(v(i, 1)) Then
      ' check condition (neglecting date, string and boolean data types)
        ElseIf Val(v(i, 1)) > 500 Then
           ws.Cells(i, 13).Interior.ColorIndex = 37
           ws.Cells(i, 13).Font.Color = vbBlack
           ws.Cells(i, 13).Font.Bold = True
        End If
  Next i
  MsgBox "Time needed: " & Format(timer - t, "0.00") & " seconds."
End Sub

这篇关于对于循环设置，“字体"和“范围内部"花费的时间太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！