.net - 优化 vba 宏 excel 2007 的循环

Question

我有这段有效的代码。它会向下移动一个范围并删除空行，如果第一个字符不是数字或负号，则将其分隔到不同的列中。
这段代码有效。但对于我需要它处理的数据量来说，它太慢了。感谢任何人就如何优化此代码并使其更快的建议。

我已经关闭了自动计算。屏幕更新。和应用程序的可见性。

Dim rng As Range
Dim i As Long
Dim Tracking As Long

Dim textval As String
Dim limitz As String
Dim remaining As String

Range("B1").End(xlDown).Offset(0, 5).Select

Set rng = Range("G2", ActiveCell).Select

i = 1
Range("G2").Select

For Tracking = 1 To rng.Rows.Count

    textval = rng.Cells(i).Value
    limitz = Left(textval, 1)

    If limitz = "" Then
      rng.Cells(i).EntireRow.Delete
     ElseIf limitz <> "0" And limitz <> "1" And limitz <> "2" And limitz <> "3" And limitz <> "4" And limitz <> "5" And limitz <> "6" And limitz <> "7" And limitz <> "8" And limitz <> "9" And limitz <> "-" Then
      remaining = Right(textval, Len(textval) - 1)
      rng.Cells(i) = remaining
      rng.Cells(i).Offset(0, 1).Value = limitz
      i = i + 1
     Else
      i = i + 1
    End If

Next

Answer 1

没有那么多看起来明显低效的代码。

以下是一些我能告诉您的提示:

• 不要选择单元格，除非你真的被迫(因为它不在你的循环内，这不是最糟糕的事情)
• 尝试解析范围而不是使用Long
• 使用诸如 IsNumeric 之类的 vba 语句更改您的测试
• 使用 With 避免多次调用一个对象

这是一个尝试(我可能改变了一些行为，因为我无法理解您是否想解析单元格或行):

Sub test()
  Dim rng As Range, row As Range
  Dim i As Long

  Dim textval As String
  Dim limitz As String
  Dim remaining As String

  Set rng = Range("G2", Range("B1").End(xlDown).Offset(0, 5))
  i = 1

  For Each row In rng.Rows
      With row
        textval = .Cells(i).Value
        limitz = Left(textval, 1)

        If limitz = "" Then
            .Cells(i).EntireRow.Delete
        ElseIf limitz <> "-" And Not IsNumeric(limitz) Then
          remaining = Right(textval, Len(textval) - 1)
          With .Cells(i)
            .Value = remaining
            .Offset(0, 1).Value = limitz
          End With
          i = i + 1
         Else
          i = i + 1
        End If
      End With
  Next
End Sub