如何在Excel VBA中停止单元格滚动

Question

我有一个函数，它会根据左侧单元格的值更新单元格。不幸的是，我不得不在函数结束之前添加最后一行代码，这样表格就可以向上滚动了，因为当我点击运行宏的按钮时，每个单元格都被选中，这使得它滚动到数据的最后一行（600行）。

如果您能告诉我如何避免这种情况，我将非常感激。

代码如下：

Sub Button2_Click()
Dim regExM As New RegExp
Dim regEx As New RegExp
Dim matches, level

regExM.Pattern = "(M)(\d)"
regEx.Pattern = "[^-](.{0})(\d)"
regExM.Global = False

  Range("J2").Activate
  ' Set Do loop to stop when an empty cell is reached.
  Do Until IsEmpty(ActiveCell)
     If regExM.Test(ActiveCell.Value) Then
        Set matches = regExM.Execute(ActiveCell.Value)
        For Each Match In matches
            level = matches(0).SubMatches(1) + 3
            ActiveCell.Offset(0, 1).Value = level
        Next
     ElseIf regEx.Test(ActiveCell.Value) Then
        Set matches = regEx.Execute(ActiveCell.Value)
        For Each Match In matches
            level = matches(0).SubMatches(1)
            ActiveCell.Offset(0, 1).Value = level
        Next
     End If
     ' Step down 1 row from present location.
     ActiveCell.Offset(1, 0).Activate
  Loop
  Range("A1").Select
End Sub

谢谢你

- Crouzilles

Answer 1

在宏运行期间，您可以关闭屏幕更新。

Sub Button2_Click()
Application.ScreenUpdating = False
 '...code
  Range("A1").Select
Application.ScreenUpdating = True
End Sub

- user2140173

---

谢谢，我会使用你的解决方案，但我也从之前帖子的回复中学到了一些东西。 - Crouzilles

Answer 2

我认为没有必要激活单元格。只需要循环遍历范围并对值进行操作即可。这样应该会更快，而且在运行时避免移动工作表。

Option Explicit

Sub Button2_Click()
Dim regExM As New RegExp
Dim regEx As New RegExp
Dim matches, level
Dim lr As Long
Dim i As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


lr = ActiveSheet.UsedRange.Rows.Count

regExM.Pattern = "(M)(\d)"
regEx.Pattern = "[^-](.{0})(\d)"
regExM.Global = False


  For i = 2 To lr
     If regExM.Test(Range("J" & i).Value) Then
        Set matches = regExM.Execute(Range("J" & i).Value)
        For Each Match In matches
            level = matches(0).SubMatches(1) + 3
           Range("J" & i).Offset(0, 1).Value = level
        Next
     ElseIf regEx.Test(Range("J" & i).Value) Then
        Set matches = regEx.Execute(Range("J" & i).Value)
        For Each Match In matches
            level = matches(0).SubMatches(1)
            Range("J" & i).Offset(0, 1).Value = level
        Next
     End If
    Next i

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

- sous2817