Wenn in einer Zelle eine bestimmtes Wort steht, sollen die Zeilen darunter ausgeblendet bzw. eingeblendet werden. Geht das?
Hi,
in der Spalte B2 steht entweder: Leer, Auto1 und Auto2
Jetzt möchte ich folgendes:
Auto 1 -> ausblenden der Zeilen A5 bis A1500. Auto 2 -> ausblenden der Zeilen A1501 bis A3000 Leer -> ausblenden der Zeilen A5 bis A3000
Geht sowas? Könnt Ihr mir helfen?
2 Antworten
Ich komme nicht ganz mit.
A5 bis A3000 sind Zellen und keine Zeilen.
Soll nur die Zelle leer sein, oder die ganze Zeile?
dann: Soll die Zelle/Zeile nur leer anzeigen, oder wirklich ausgeblendet werden?!
Füge folgenden Code in deinem VBA Projekt ein.
Natürlich nicht als Modul, sondern in das Tabellenblatt, welches Betroffen ist. z.B.: Tabelle1
Getestet und für gut empfunden :-)
Je nach Wunsch würde ich persönlich die Zeilen als Variable deklarieren. Macht es im Falle einer Änderung leichter
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$B$2" Then
Rows("5:3000").EntireRow.Hidden = False
'Wenn "leer"
If Range("B2").Value = "leer" Then Rows("5:3000").EntireRow.Hidden = True
'Wenn "Auto1"
If Range("B2").Value = "Auto1" Then Rows("5:1500").EntireRow.Hidden = True
'Wenn "Auto2"
If Range("B2").Value = "Auto2" Then Rows("1501:3000").EntireRow.Hidden = True
End If
End Sub
Vielen Dank. Bei dir hat es so funktioniert? Komisch...
Erkennbar ist nur was bei Auto1, und auch da blendet er die gewünschten Zeilen aus, anstatt einzu blenden.
Bei den anderen zwei Parametern sehe ich keine Veränderungen. Leider.
Danke.
Range.EntireRow muss ich mir merken, damit scheint sich die "Hidden"-Eigenschaft auch für mehrere Zeilen gleichzeitig setzen zu lassen.
Ich hab wohl aus und einblenden verwechselt.
Schreib einfach true zu false um und umgekehrt ;)
Du musst Auto1, Auto2 oder leer schreiben. Über ein Dropdown wählen geht leider nicht.
Sobald du schreibst und enter drückst reagiert das Makro
du darfst das Makro gern anpassen
P.S.: Auto1, Auto2 oder leer sind 3 Worte die jeweils in Zelle B2 stehen sollen?! Korrekt?!
Jedenfalls in Excel 2000 kann man den Haken bei "Leere Zellen ignorieren" rausnehmen; dann kann man auch leere Einträge haben.
Oh man.. irgendwie funktioniert es garnix mehr :(
Nur per Makro.
Dim visible1 As Boolean, visible2 As Boolen, z as Long
visible1 = (Cells(2,2).Text = "Auto1") 'beachte, dass bei Cells(Zeile, Spalte) die Reihenfolge anders herum ist als in "C5"
visible2 = (Cells(2,2).Text = "
For z = 5 To 1500
Rows(z).Hidden = Not visible1
Next
For z = 1501 To 3000
Rows(z).Hidden = Not visible2
Next
Am besten in das Change-Ereignis des Worksheets (der Tabelle), und erst sicherstellen, dass der geänderte Range auch tatsächlich B2 umfasst.
Erstmal danke. Leider passt bei deinem Makro garnix. Schade.
Was heißt "es passt gar nix"? Das ist nur ein Codeabschnitt.
Hast du schon mal mit Makros gearbeitet?
Ja, die Maschine sagt mir bei der ersten Zeile "visible2 As Boolen" einen Fehler an, dass dieser nicht definiert wurde.
Tippfehler meinerseits. Ersetze "Boolen" durch "Boolean".
Tipp: angemeckertes Wort markieren (ohne Leerzeichen dahinter) und Strg+J für Ergänzungs-/Korrekturvorschläge
Man lernt nie aus. Das wusste ich auch noch nicht :P
DANKE
Okay, jetzt zeigt er mir keinen Fehler mehr an. Aber trotzdem funktioniert es nicht immer im Hintergrund. Das heißt, wenn ich ändern möchte, also den Eintrag in B2, passiert wieder gar nichts.
Hast du diese Codezeilen in
Private Sub Worksheet_Change(ByVal Target As Range)
..
End Sub
?
Ich vermute er nutzt ein Dropdown?!
da reagiert die Change-Eigenschaft glaub nicht.
@KaiserWilhelmXL: Kannst du uns mal ein Screenshot deiner Excel-Tabelle zeigen und auch zeigen wie du die Zelle B2 änderst?
JA per Dropdown. Daran kann ich auch nicht abrücken.
Hab mal ein DropDown (mit Liste) in B2 gesetzt.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Cell_B2) Is Nothing) Then
MsgBox ("B2 betroffen")
End If
End Sub
bringt die MsgBox wie es sein soll.
Wenn du das Makro in die Quelltextseite der Tabelle einfügst und die Daten der Zelle B2 der Tabelle als DropDown einschränkst, erscheint jedesmal ein "Dialog", wenn du den Inhalt von B2 über das DropDown änderst.
Da die Funktion, die diesen Dialog veranlasst, in VBA6 "MsgBox" heißt, habe ich den Dialog ebenso benannt.
Oh man :(
Was genau muss ich jetzt nun machen, dass es so funktioniert?
B2 -> Dropdown Leer(Nicht das Wort Leer, sondern wenn noch kein Dropdown ausgewählt wurde), Auto1 oder Auto2
Wenn Auto1-> Einblenden Zeilen 6:1000, Ausblenden 1001:1500
Wenn Auto2-> Einblenden Zeilen 1001:1500, Ausblenden 6:1000
Bitte, ich finde sonst nirgend eine Hilfe.
Ich behalte mal die Variablennamen "visible1" und "visible2" von oben bei.
visible1 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto1" Then visible1 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible1 = True ' wenn in B2 nichts drin steht, alle Zeilen einblenden
visible2 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto2" Then visible2 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible2 = True ' wenn in B2 nichts drin steht, alle Zeilen einblenden
Das Ein-/Ausblenden selbst geschieht wie oben (mit den For-Schleifen).
Mit nur zwei möglichen Einträgen (zuzüglich leer) könnte man vielleicht noch ein, zwei Zeilen Code sparen, aber 1. habe ich mir aus gutem Grund angewöhnt, von Anfang an auf leichte spätere Erweiterbarkeit zu achten und 2. wird der Code dadurch m. E. kein bisschen übersichtlicher.
Da passiert gar nichts? Fehlt da noch was. Muss ich mir jetzt was dazu denken?
Kannst du nicht bitte einmal den kompletten Code hier reinschreiben?
Ok ...
Prozedurrahmen:
Private Sub Worksheet_Change(ByVal Target As Range)
..
End SubAbfrage, ob B2 von der Änderung betroffen ist:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Cell_B2) Is Nothing) Then
MsgBox ("B2 betroffen")
End If
End Sub(Hier lassen wir die Zeile mit MsgBox gleich raus - die war nur drin, um nachzuweisen, dass das Ereignis "Change" ausgelöst wird. Auch ersetzen wir Cell_B2 durch den direkten Ausdruck.)
Dann brauchen wir noch die Ermittlung, welche Blöcke sichtbar sein sollen
visible1 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto1" Then visible1 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible1 = True ' wenn in B2 nichts drin steht, alle Zeilen einblenden
visible2 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto2" Then visible2 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible2 = Trueund das eigentliche Ein- und Ausblenden der Zeilen
For z = 5 To 1500
Rows(z).Hidden = Not visible1
Next
For z = 1501 To 3000
Rows(z).Hidden = Not visible2
Nextund natürlich die Variablendeklarationen
Dim visible1 As Boolean, visible2 As Boolen, z as LongZusammengefasst:
Private Sub Worksheet_Change(ByVal Target As Range)
'die Variablendeklarationen
Dim visible1 As Boolean, visible2 As Boolen, z as Long
'Abfrage, ob B2 von der Änderung betroffen ist:
If Not (Intersect(Target, Cells(2, 2)) Is Nothing) Then
'Dann brauchen wir noch die Ermittlung, welche Blöcke sichtbar sein sollen
visible1 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto1" Then visible1 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible1 = True ' wenn in B2 nichts drin steht, alle Zeilen einblenden
visible2 = False ' erst mal grundsätzlich ausblenden
If Cells(2, 2).Text = "Auto2" Then visible2 = True ' wenn in B2 ein Wert drin steht, die zugehörigen Zeilen einblenden
If Cells(2, 2).Text = "" Then visible2 = True ' wenn in B2 nichts drin steht, alle Zeilen einblenden
'und das eigentliche Ein- und Ausblenden der Zeilen
For z = 5 To 1500
Rows(z).Hidden = Not visible1
Next
For z = 1501 To 3000
Rows(z).Hidden = Not visible2
Next
End If
End Sub
(Eigentlich ist das Arbeit für mich und ich müsste dafür was berechnen und dürfte das demnach nicht auf dieser Plattform veröffentlichen.)
Das dies Aufwand bedeutet, ist mir bewusst. Jedoch ist genau das das Prinzip von diesem Forum. Wenn jeder danach gehen würde, würde diese Plattform hier keinen Sinn mehr machen.
Grundsätzlich funktioniert dein Code. Jedoch dauert es jedesmal minutenlang bis der VBA-Code umgesetzt wurde, es flackert währendessen usw. . Das ist leider so nicht zu gebrauchen. Jedoch willst du sicherlich nicht noch weiter Arbeit reinstecken. Danke trotzdem.
Für die Geschwindigkeit siehe den Code im Kommentar von Rambaldi1988 zu seiner Antwort.
Ersetze die For-Blöcke durch
Rows("5:1500").EntireRow.Hidden = visible1
Rows("1501:3000").EntireRow.Hidden = visible2
Oder / und du schreibst noch je eine Zeile vor und hinter die For-Blöcke bzw. diese beiden Zeilen:
Application.ScreenUpdating = False
For ...
...
Next
For ...
...
Next
Application.ScreenUpdating = True
Das sorgt dafür, dass VBA keine Zeit damit verschwendet, zwischendurch immer wieder den Fensterinhalt anzuzeigen.
Noch besser wäre es, da ein "On Error GoTo" oder "On Error Resume Next" reinzubringen, für den Fall, dass da was schiefgehen sollte:
On Error Resume Next
Application.ScreenUpdating = False
For ...
...
Next
For ...
...
Next
Application.ScreenUpdating = True
On Error GoTo 0
Danke. Jetzt klappt es wunderbar.
Vielen Dank für deine Mühe. Auch wenn du dir jetzt dafür nichts kaufen kannst.
Stimmt, du Recht. Mein Fehler.
Ich meinte eigentlich A5 bis AM3000. Das ist der Bereich meiner kompletten Tabelle.
Eigentlich ist es mir egal, ob dann dieser Bereich ausgeblendet wird, oder gleich die kompletten Zeilen.
Und diese sollen dann wirklich ausgeblendet bzw. dann wieder eingeblendet werden