zrýchlenie procedúry

[tsibee]

Zdarvím,

prosím niekto by mi prepísal n.u. procedúru, aby bola rýchlejšia?

For Each cell In Worksheets("hárok2").Range("F11:P41")
If cell.Value = 0 Then
cell.Value = " "
End If
Next cell

Vopred vďaka :)

[elninoslov]

Napr. :

Kód: Vybrat vše

Sub ClearZero()
Dim RNG As Range, D(), R As Long, S As Integer, x As Integer, y As Long
  
  With wsData.Range("F11:P41")
    R = .Rows.Count: S = .Columns.Count
    ReDim D(1 To R, 1 To S)
    If R * S = 1 Then D(1, 1) = .Value2 Else D = .Value2
    
    For y = 1 To R
      For x = 1 To S
        If Not IsEmpty(D(y, x)) And D(y, x) = 0 Then
          If RNG Is Nothing Then Set RNG = .Cells(y, x) Else Set RNG = Union(RNG, .Cells(y, x))
        End If
      Next x
    Next y
    
  End With
  
  If Not RNG Is Nothing Then RNG.ClearContents
End Sub

alebo

Kód: Vybrat vše

Sub ClearZero2()
Dim RNG As Range, Bunka As Range
  
  For Each Bunka In wsData.Range("F11:P41")
     If Not IsEmpty(Bunka) And Bunka.Value2 = 0 Then
       If RNG Is Nothing Then Set RNG = Bunka Else Set RNG = Union(RNG, Bunka)
     End If
  Next Bunka
  
  If Not RNG Is Nothing Then RNG.ClearContents
End Sub

Ale 0 sa dajú skryť napr aj formátom. Záleží na tom, čo tam máte. Napr. :

Kód: Vybrat vše

General;General;

alebo Podmienené formátovanie :
Vzorec:

Kód: Vybrat vše

=F11=0

Formát:

Kód: Vybrat vše

;;;
alebo
General;General;

PS: "wsData" je CodeName listu.

[tsibee]

ahh Jasne General :).
Vďaka.