CODEWITHSUNDEEP
vbaexcel
Crouzilles
Crouzilles

Reputation: 813

How to stop cell scrolling in excel vba

I have this function which updates cells depending on the value of the cell on their left. Unfortunately, I had to add the last line of code before the end of the function so the sheet would scroll back up as when I click the button to run the macro, each cell is selected which makes it scroll down to the last row of data (600 rows).

If you could tell me what to do to avoid this, it would be very much appreciated.

The code is:

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

Thank you

Upvotes: 3

Views: 6134

Answers (2)

sous2817
sous2817

Reputation: 3960

I don't think there's a need to activate the cells. Just loop through the range and do what you need with the values. This should be faster and avoid moving the sheet around while it runs.

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

Upvotes: 3

user2140173
user2140173

Reputation:

You can turn off screen updating while the macro is running.

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

Upvotes: 5

Related Questions