Snelheidsverhoging proces

[Plotinus]

Ik heb de volgende code:

 LastRow = Range("C" & Rows.Count).End(xlUp).Row
    If ActiveCell.Column <> 2 Then
        Application.ScreenUpdating = False
        Application.Calculate
        For i = 9 To LastRow
            If Range("V" & i) = 0 And (Range("S" & i) <> " " Or Range("T" & i) <> " " Or Range("U" & i) <> " ") Then Range("S" & i).Resize(, 3) = " "
        Next i
    End If

Wat er in de For-next-lus gebeurt is vrij simpel: als in een bepaalde rij de kolom V=0 (resultaat van en Function) en één van de drie velden in de kolomen S, T en U in dezelfde rij is leeg, dan moet de inhoud in S, T en U in dezelfde rij worden gewist. Dit gebeurt aan de hand van een event: SelectionChange, dus heel vaak. Het is bedoeld om onvolledige of inconsequent ingevulde velden te legen; kolom B mag daarbij buiten beschouwing worden gelaten. Echter, omdat het aantal rijen in de paar duizend kan lopen, treedt er hierdoor ergerlijke vetraging op. Daarnaast ziet er eigenlijk ook niet uit, vind ik.

Vraag is nu, is er een compactere schrijfwijze die ook sneller wordt uitgevoerd?

 

[edmoor]

De grote vertragende factor hier is de Application.Calculate. Het is lastig te zeggen zo of dit wel nodig is bij een wijziging van 1 van de cellen in de kolommen S, T, U, V. Het is afhankelijk van de functies waar je het over hebt. Volgens mij kan ook het bereik veel kleiner, je doet het nu bij een wijziging in alle kolommen behalve kolom B. Daarnaast zou ik geen spaties gebruiken maar leeg. Dus niet " " maar "". Een spatie maakt een veld niet leeg maar zet er een spatie in. Dan zie je niks maar lijkt het alleen maar leeg.

Wat betreft het uiterlijk van je code zou je het zo kunnen kunnen schrijven:

    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    If ActiveCell.Column <> 2 Then
        Application.ScreenUpdating = False
        Application.Calculate
        For i = 9 To LastRow
            If Range("V" & i) = 0 And _
               (Range("S" & i) <> " " Or _
                Range("T" & i) <> " " Or _
                Range("U" & i) <> " " ) Then
                Range("S" & i).Resize(, 3) = " "
            End If
        Next i
        Application.ScreenUpdating = True
    End If

 

[snb]

lees het gebied in in een array,
schrijf het resultaat van de vergelijking in een array
schrijf de resultaat array in 1 keer naar het werkblad.

Plaats immer een voorbeeldbestand om het helpers gemakkelijker te maken.

 

[Plotinus]

Dank Edmoor; calculate is eigenlijk inderdaad hier niet nodig en dat scheelt weer.

Hierbij een uitgekleed voorbeeldje wat ik bedoel als reactie op wat snb schrijft. Het is een aanpassing van zijn eerder aangedragen prachtige oplossing van een andere vraag over geschakelde comboboxen van mij. Omdat het bereik in plaats van een paar honderd een paar duizend records kan bevatten heb ik dat voorbeeld ook zo verder uitgebreid. De vertraging die nu optreedt is goed te zien. De originele sheet bevat nog veel meer kolommen, waardoor de vertraging nog toeneemt.

De bedoeling is dus dat, als na een selectie via de comboboxen, er vervolgens handmatig in één van de kolommen A, B, of C iets wordt gewijzigd de betreffende cellen in die kolommen worden gewist, omdat er dan een ongeldige situatie ontstaat (zie Toetsing_vulling, aangesproken via Worksheet_SelectionChange). Hoe dat slim (en snel) via een Array te kunnen realiseren is mij helaas niet duidelijk - Arrays zijn nog niet echt mijn ding.

Overigens kan worden voorkomen dat er handmatig wijzigingen worden uitgevoerd door het blad met deze kolommen te beveiligen, maar dan moet ik de optie openlaten dat de cellen in deze kolommen wel geselecteerd kunnen worden. Dat betekent dat dan ook alle andere cellen geselecteerd kunen worden, wat ik wil voorkomen. De sheet is dus wel in de werkelijke situatie beveeiligd met enkel genoemde kolommen niet geblokkeerd.

 

[snb]

Had je hieraan al gedacht ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [A12:A50].Interior.ColorIndex = 0
    ComboBox1_1.Visible = False
    ComboBox1_2.Visible = False
    ComboBox1_3.Visible = False
    ComboBox1_2.ListIndex = -1
    ComboBox1_3.ListIndex = -1
    
    If Not Intersect(Target, [A12:A50]) Is Nothing Then
        Target.Interior.ColorIndex = 3
        ComboBox1_1.Visible = True
        ComboBox1_2.Visible = True
        ComboBox1_3.Visible = True
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, [A12:A50]) Is Nothing Then 
      application.enableevents=false
      Toetsing_vulling
      application.enableevents=true
    end if
End Sub

 

[Plotinus]

Dit werkt perfect snb; aanzienlijke prestatieverbetering!

Ik heb in dit verband nog wel één vraagje: Jij schrijft

 If Not Intersect(Target, [A12:A50]) Is Nothing Then

Een Rangebereik met Blokhaken. Ik wilde dat graag afhankelijk maken van de omvang van mijn tabel. Dat lukte me enkel, na diverse varianten geprobeerd te hebben, door het als volgt te schrijven:

 LastRow = Range("C" & Rows.Count).End(xlUp).Row
    If Not Intersect(Target, Range("A9:A" & LastRow)) Is Nothing Then

Waarom is dat? Is er toch een manier om binnen de blokhaken een variabel bereik mee te geven. Het is maar een detail natuurlijk, maar ik vind dat zeer merkwaardig. En uitgebreid zoeken op internet levert mij ook geen duidelijkheid - waarschijnlijk door de onjuiste vraagstelling.

 

[snb]

If Not Intersect(Target, cells(9,1).currentregion.columns(1)) Is Nothing Then

 

[Plotinus]

dat is een hele mooie oplossing; hartelijk dan snb