VBA Zeilen ausblenden, wenn die Formel kein Ergebnis liefert

[NobX]

Hallo liebe Excel Gemeinde,

komme einfach nicht weiter bei einer vermutlich ganz einfach zu lösenden Aufgabe:

In Tabelle 3 im Modul 6 steht dieser Code (es sollen in Tabelle3 (="Ermittlung") die Zeilen 1 bis 600 dann ausgeblendet werden, wenn die darin enthaltenen Formeln das Ergebnis ("") liefern oder dann eingeblendet werden, wenn die Zellen durch ein Formelergebnis mit einem Wert gefüllt werden):

Private Sub Worksheet_Calculate()

Dim i As Long

With Tabelle3

  For i = 1 To 600
    If Sheets("Ermittlung").Cells(i, 1) = "" Then
    Rows(i).Hidden = True
    End If
  Next
For i = 1 To 600
    If Sheets("Ermittlung").Cells(i, 1) <> "" Then
    Rows(i).Hidden = False
    End If
    Next
   
End With
   
End Sub


Der Code funktioniert nur, wenn ich ihn explizit aufrufe und starte. Er soll jedoch automatisch diese Änderung (das Ein- und Ausblenden der Zeilen) durchführen, wenn eine Zelle neu berechnet wird. Was kann ich tun?

Danke im Voraus und Grüße

NobX

[Werner.M]

Hallo,

das ist ein Event-Makro und gehört ins Codemodul des Tabellenblattes, auf dem es sich auswirken soll und nicht in ein allgemeines Modul.

Code:

Private Sub Worksheet_Calculate()
Dim i As Long

For i = 1 To 600
    Rows(i).Hidden = IIf(Cells(i, 1).Value = "", True, False)
Next i
   
End Sub

Dir ist klar, dass das Makro bei jeder Formelberechnung irgendwo auf dem Tabellenblatt ausgelöst wird und nicht nur, wenn sich das Formelergebnis in A1 bis A600 ändert?

Gruß Werner

[NobX]

Hallo Werner,

vielen Dank für Deine Arbeit.
Habe es auprobiert: es funktioniert für die erste Eingabe, bei der zweiten Eingabe hängt sich Excel auf. Es werden nicht die Zellen ausgeblendet und die hinterlegten Formeln sind alle gelöscht. Dies ist kein Problem, da ich das eh nur mit einer Testversion versucht habe. Da muss ich einen ganz anderen Ansatz finden. - Danke dennoch
Grüße
NobX

[Kuwer]

Hallo,

kannst es ja mal damit testen:

Private Sub Worksheet_Calculate()

  Dim i As Long

  Application.ScreenUpdating = False

  For i = 1 To 600

      Rows(i).Hidden = Cells(i, 1).Value = ""

  Next i

  Application.ScreenUpdating = True

End Sub

Gruß Uwe

[NobX]

Hallo Uwe,

danke Für Deinen Code. Nach Ausführung kommt immer die Meldung: "Fehler beim Kompilieren: Nach End Sub, End Function oder End Property können immer nur Kommentare stehen". Ich habe versucht noch nach der Deklaration der Objektvariablen "i" den Zusatz: With Tabelle3 und vor End Sub natürlich End With einzubauen. Dann kam die Meldung zwar nicht, - Excel hat sich dafür aber aufgehangen.
Ich weiss auch nicht mehr, was ich noch machen soll. Offensichtlich besteht der Knackpunkt beim Sortieren der Daten darin, dass zwischen den Zeilen mit Inhalt eben meistens Leerzeilen sind, in denen Formeln nur dann Ergebnisse liefern, wenn eine Auswahl auf einem anderen Tabellenblatt getroffen wurde, - wenn keine Auswahl getroffen wurde, sind die Ergebnisse "". Wenn es etwas bringt, kann ich die Ergebnisse alternativ als "0" ausgeben lassen oder mit einem beliebigen andern Ergebnis wie z. B. NV oder andere Excel - Fehlermeldungen.

Vielleicht fällt Euch noch etwas ein.

Grüße

NobX

[LuckyJoe]

Hi,

lade doch mal einen anonymisierte Version deiner Datei hoch, dann ist es für alle Helfer einfacher, zu prüfen, woran das liegt.

[NobX]

Hallo zusammen,

ich konnte es mit Eurer Hilfe nun selbst lösen. Danke. Die Datei kann ich leider nicht hochladen, da hier zu viele vertrauliche Daten der Firma draufstehen, - wenn ich die alle lösche, wisst Ihr nicht mehr was ich wollte. - Leider.
Mit folgender Lösung klappt es nun, dass sich die Zeilen bei jeder Änderung einer Formel auf diesem Blatt selbst aus- oder einblenden, wenn die Formel ein Ergebnis bringt:

Code:

Private Sub Zeilen_ein_ausblenden_Change()

    Dim Zeile As Integer
    Dim i As Integer
 
    On Error Resume Next
 
    For Zeile = 1 To 600
     
        With Tabelle3
       
        .Rows(Zeile).Cells.EntireRow.Hidden = False
       
    End With
       
    Next Zeile
 
    Application.ScreenUpdating = False

    With Tabelle3
   
        For i = 1 To 600
     
        If Cells(i, 1).Value = "" Then
        Rows(i).Hidden = True
   
    End If

    Next i
   
    Application.ScreenUpdating = True

    End With

    Range("A1").Select
End Sub

Danke nochmals

Grüße
NobX