EN VI

Excel - How to optimize this sub in vba?

I got a sub that works well except it takes times to run and at the end, my excel shuts down.

Sub testing_rows()
    
    Dim ws As Worksheet
    Dim nbTxt As Integer
    Dim I As Integer
    
    Set ws = Worksheets("Sheet4")
    
    gp_lastrow = Range("A" & Rows.count).End(xlUp).Row
    lastRow = Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
    
    For I = 2 To 100
        my_char = "-"
        nbTxt = (Len(Range("A" & I)) - Len(Replace(Range("A" & I), my_char, ""))) / Len(my_char)
        Range("BB" & I) = nbTxt
    Next I
End Sub

Where gp_lastrow = 28.000 lines and lastrow = 18.000 How can I optimize this sub which is only a small part of what I want to do... on almost 30.000 lines.

Thx

Solution:

Reading/writing cells always take some time. Several changes to improve efficiency:

Load data into array
Write output to sheet all at once

Microsoft documentation:

Split function

UBound function

Sub testing_rows()
    Dim ws As Worksheet, LastRow As Long
    Dim rngData As Range, i As Integer, arrA, arrBB
    Const START_ROW = 2 ' the first data row (skip header)
    Const IN_COL = "A"
    Const OUT_COL = "BB"
    
    With Worksheets("Sheet1")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        ' load data on Col A
        Set rngData = .Range(.Cells(START_ROW, IN_COL), .Cells(LastRow, IN_COL))
        arrA = rngData.Value
        ' output range on Col BB
        Set rngData = .Range(.Cells(START_ROW, OUT_COL), .Cells(LastRow, OUT_COL))
        arrBB = arrA
        For i = LBound(arrA) To UBound(arrA)
            arrBB(i, 1) = UBound(Split(arrA(i, 1), "-"))
        Next
        rngData.Value = arrBB
    End With
End Sub