Thanks for your help, a macro was created for excel, but now the question has arisen of how to optimize the performance of this macro. For 130 records, it works for about 20 seconds, while restrictions are imposed on only about 10 columns. There should be more than a hundred columns and rows of several thousand. It will slow down. How can I make this optimization?

Private Sub Worksheet_Change(ByVal Target As Range) Dim objCell As Range If Target.Row = 1 Then Exit Sub If Not Application.Intersect(Range("A:CY"), Target) Is Nothing Then Application.EnableEvents = False For Each objCell In Target With objCell If Len(.Value) > 0 Then Select Case .Column Case 1, 2 If TypeName(.Value) = "String" Then .Value = Left(.Value, 100) Else .Value = .Value End If Case 3, 4 If TypeName(.Value) = "String" Then .Value = Left(.Value, 5) Else .Value = .Value End If Case 5, 6 If .Value <> 0 And .Value <> 1 Then .Value = "" Case 7, 8 .Value = Replace(.Value, ";", "") Case 9, 10 If Not IsNumeric(.Value) Then .ClearContents End Select End If End With Next objCell Application.EnableEvents = True End If End Sub

Thank.

  • Application.ScreenUpdating = False , Application.ScreenUpdating = True ? - Denis
  • 1. Wrong: for every sneeze to create a theme. rather, to produce questions on the same code === 2. You create threads on the forums. You get a solution, but don't unsubscribe and come up with other questions - no desire to help. - vikttur
  • In that forum I went out, I just can not enter. Sorry how many tried - unsuccessfully. I think I was banned, although vrodeby did not break anything. - Kamil Askerov
  • A comment from @vikttur applies to this forum. He has already answered 3 of your questions, you seem to be using his solutions, i.e. they helped you. But at the same time you did not mark any of the answers to the questions asked as correct. - Edward Izmalkov
  • Edward, at least your comment is correct, but paragraph 2 of the comment is about another resource. Show the usual respect, unsubscribing to the topic, is considered good form. - vikttur

1 answer 1

Disable / enable screen refresh:

 Application.ScreenUpdating = false ... Application.ScreenUpdating = True

If there are formulas on the sheet, especially if they are volatile (recalculated for any change on the sheet) - disable / enable autocalculations:

 Application.Calculation = xlManual ... Application.Calculation = xlAutomatic

If processing of large data ranges is assumed, in the code to use arrays, minimize access to the sheet - work with objects is slow.

It may be better to abandon the use of sheet events and launch a macro forcibly (using the button, keyboard shortcut ...).

For complex processing and if these processing for different ranges are different, it makes sense to process each range in a separate procedure, combining their call in one macro. This item is more for the convenience of working with the code in the future.

  • Thank. "I'll dig" - Kamil Askerov
  • Some remark: for some reason this macro on the second line works, but with subsequent changes it does not work as it should, although it seems like it should work correctly all the time (by evaluating code) - Kamil Askerov
  • What is there in your line and what ranges you change - is unknown. It is possible that the crash and events remained disabled. To guess? Here - about optimizing computations on large volumes, not about errors - vikttur