Excel VBA: For Each zelle in Range

Salu zusammen,

folgendes erklärt sich in seiner Absicht wohl von selbst, läuft aber so nicht. Was ist der Grund?

Option Explicit
Sub BestimmteZeilenAusblenden()
Dim KramBereich As Range
Dim KramInhalte As Variant

KramBereich = Range("Kram")

For Each KramInhalte In KramBereich
 If KramInhalte = "gelb" Then KramInhalte.EntireRow.Hidden = True
Next KramInhalte
End Sub

Falls so nicht klar: in einer Spalte stehen diverse Farben, immer wenn „gelb“ darin steht, soll die Zeile nach Durchlaufen des Makros ausgeblendet werden. Das Problem ist, daß sich der Abschnitt, in dem diese Farben notiert sind, verschiebt, weil darüber immer mal wieder Zeilen eingefügt und gelöscht werden. Daher möchte ich mit einem Bereichsnamen arbeiten (Range(„KramBereich“)).

Vielen Dank & bestliche Grüße
-Rob.

folgendes erklärt sich in seiner Absicht wohl von selbst,
läuft aber so nicht. Was ist der Grund?

Hallo Rob,

beschreib bitte genauer was da nicht läuft und wie sich das äuerst, Debugger in welcher zeile, Fehlermeldung, sonstige Infos.

meinst du so

Sub BestimmteZeilenAusblenden()
Dim KramBereich As Range, KramInhalte As Range
Set KramBereich = Range("Kram")
For Each KramInhalte In KramBereich
 If KramInhalte.Value = "gelb" Then KramInhalte.EntireRow.Hidden = True
Next KramInhalte
End Sub

vielleicht hier nicht nötig aber sicherheitshalber gewöhn dir bitte an bei sowas so vorzugehen:

Sub BestimmteZeilenAusblenden()
Dim KramBereich As Range, KramInhalte As Range
Set KramBereich = Range("Kram")
**KramBereich.EntireRow.Hidden = False**
For Each KramInhalte In KramBereich
 If KramInhalte.Value = "gelb" Then KramInhalte.EntireRow.Hidden = True
Next KramInhalte
End Sub

Gruß
Reinhard

Läuft! Danke :smile:
Salu o Excel-Champion :smile:

beschreib bitte genauer was da nicht läuft und wie sich das
äuerst, Debugger in welcher zeile, Fehlermeldung, sonstige
Infos.

Ich hatte zuvor Probleme mit den Variablentypen - was immer ich gewählt hatte (beide Variant, Option Explicit weggelassen) immer kam „Unverträglichkeite der Typen“…

vielleicht hier nicht nötig aber sicherheitshalber gewöhn dir
bitte an bei sowas so vorzugehen:

Mit „so“ meinst Du das „Set“, richtig?

Sub BestimmteZeilenAusblenden()
Dim KramBereich As Range, KramInhalte As Range
Set KramBereich = Range(„Kram“)
KramBereich.EntireRow.Hidden = False
For Each KramInhalte In KramBereich
If KramInhalte.Value = „gelb“ Then
KramInhalte.EntireRow.Hidden = True
Next KramInhalte
End Sub

So klappts. Vielen Dank. Allerdings ist das Script unfaßbar langsam: Bei den zwanzig Zellen die in meinem Fall durchlaufen werden, dauert die Chose auf meinem Rechner (P4, 2 x 3 GHz, 2 GB RAM) trotz Application.ScreenUpdating = False ca. 25 Sekunden. Ich weiß ja, daß VBA keine sehr effiziente Sprache ist, aber das ist doch wohl übertrieben. Woran kann das liegen?

Nochmals allerbesten Dank, Sternchen & viele Grüße :o)
-Rob.

Hallo Rob,

Ich hatte zuvor Probleme mit den Variablentypen - was immer
ich gewählt hatte (beide Variant, Option Explicit weggelassen)
immer kam „Unverträglichkeite der Typen“…

Option Explicit lasse ich nie weg.
Wenn schon lass das „As Integer“ o.ä. ERSTMAL weg.
Übrigens, As Variant hinschreiben oder nix ist identisch.

Wenn du dann den Code hinbekommst so daß er funktioniert kannste z.B. mit
msgbox typename(Variable)
herausfinden als was du die Variable hättest deklarieren sollen und dies dann nachholen.

Das klappt bei komplizierteren Variablentypen nicht immer 1:1.
Das bedeutet dann mußt du es so machen:
Dim xyz as Object
obwohl bei
msgbox typename(Variable)
etwas „Genaueres“ als Object herauskam.

vielleicht hier nicht nötig aber sicherheitshalber gewöhn dir
bitte an bei sowas so vorzugehen:

Mit „so“ meinst Du das „Set“, richtig?

Nein, damit meine ich das:

KramBereich.EntireRow.Hidden = False

Die Logik dahinter ist diese, du willst alle zellen gelb färben in den das Wort „Huhu“ steht.
Würdest du einfach nur Code schreiben der nur das macht so lauert ein Problem im Hintergrund.
Du läßt den Code laufen, in C5 steht „Huhu“, ergo wird die Zelle gefärbt, alles in Ordnung.

Nun „arbeitest“ du aber in der tabelle und in C5 steht nicht mehr Huhu sondern was anderes und wieder startest du den Code.
Was passiert? Nicht nur Zellen in denen Huhu steht sind gelb sondern auch Zellen in denen Huhu FRÜHER mal stand.
Ergo, im Code erstmal alle Zellen entfärben, dann wieder gezielt färben.
Und Färben oder Zeilen ausblenden ist für diese Problematik identisch.
Also ertsmal alle wieder ewinblenden dann gezielt ausblenden.

So klappts. Vielen Dank. Allerdings ist das Script unfaßbar
langsam: Bei den zwanzig Zellen die in meinem Fall durchlaufen
werden, dauert die Chose auf meinem Rechner (P4, 2 x 3 GHz, 2
GB RAM) trotz Application.ScreenUpdating = False ca. 25
Sekunden. Ich weiß ja, daß VBA keine sehr effiziente Sprache
ist, aber das ist doch wohl übertrieben. Woran kann das
liegen?

20 Zellen und 25 sek? Hä, wie watt?
Zeig mal die Mappe.

Gruß
Reinhard

MMM (Mit Muster-Mappe)
Salu Reinhard,

Ich hatte zuvor Probleme mit den Variablentypen - was immer
ich gewählt hatte (beide Variant, Option Explicit weggelassen)
immer kam „Unverträglichkeite der Typen“…

Option Explicit lasse ich nie weg.

In der finalen Datei: ich auch nicht. War ja nur zum testen.

Wenn schon lass das „As Integer“ o.ä. ERSTMAL weg.
Übrigens, As Variant hinschreiben oder nix ist identisch.

Ah, gut zu wissen. Danke …

Wenn du dann den Code hinbekommst so daß er funktioniert
kannste z.B. mit
msgbox typename(Variable)
herausfinden als was du die Variable hättest deklarieren
sollen und dies dann nachholen.

Sehr gut zu wissen. Vielen Dank …

Das klappt bei komplizierteren Variablentypen nicht immer 1:1.
Das bedeutet dann mußt du es so machen:
Dim xyz as Object
obwohl bei
msgbox typename(Variable)
etwas „Genaueres“ als Object herauskam.

Gut.

vielleicht hier nicht nötig aber sicherheitshalber gewöhn dir
bitte an bei sowas so vorzugehen:

Mit „so“ meinst Du das „Set“, richtig?

Nein, damit meine ich das:

KramBereich.EntireRow.Hidden = False

Die Logik dahinter ist diese, du willst alle zellen gelb
färben in den das Wort „Huhu“ steht.
Würdest du einfach nur Code schreiben der nur das macht so
lauert ein Problem im Hintergrund.
Du läßt den Code laufen, in C5 steht „Huhu“, ergo wird die
Zelle gefärbt, alles in Ordnung.

Nun „arbeitest“ du aber in der tabelle und in C5 steht nicht
mehr Huhu sondern was anderes und wieder startest du den Code.
Was passiert? Nicht nur Zellen in denen Huhu steht sind gelb
sondern auch Zellen in denen Huhu FRÜHER mal stand.
Ergo, im Code erstmal alle Zellen entfärben, dann wieder
gezielt färben.
Und Färben oder Zeilen ausblenden ist für diese Problematik
identisch.
Also ertsmal alle wieder ewinblenden dann gezielt ausblenden.

Achso, das ist in diesem Fall aber aus vielen Gründen nicht nötig. Als Grundregel aber ist der Tipp natürlich richtig.

So klappts. Vielen Dank. Allerdings ist das Script unfaßbar
langsam: Bei den zwanzig Zellen die in meinem Fall durchlaufen
werden, dauert die Chose auf meinem Rechner (P4, 2 x 3 GHz, 2
GB RAM) trotz Application.ScreenUpdating = False ca. 25
Sekunden. Ich weiß ja, daß VBA keine sehr effiziente Sprache
ist, aber das ist doch wohl übertrieben. Woran kann das
liegen?

20 Zellen und 25 sek? Hä, wie watt?
Zeig mal die Mappe.

Da, guck: http://www.file-upload.net/download-3564326/test.xls…

Issoch ächt was zu lang, ne!?

Abendliche Grüße :o)
-Rob.

Grüezi Rob

Falls so nicht klar: in einer Spalte stehen diverse Farben,
immer wenn „gelb“ darin steht, soll die Zeile nach Durchlaufen
des Makros ausgeblendet werden. Das Problem ist, daß sich der
Abschnitt, in dem diese Farben notiert sind, verschiebt, weil
darüber immer mal wieder Zeilen eingefügt und gelöscht werden.
Daher möchte ich mit einem Bereichsnamen arbeiten
(Range(„KramBereich“)).

Nur eine kleine Zwischenfrage:

Warum machst Du das nicht mit dem Autofilter und der Bedingung ‚Gelb‘?

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -

Grüezi Rob

So klappts. Vielen Dank. Allerdings ist das Script unfaßbar
langsam: Bei den zwanzig Zellen die in meinem Fall durchlaufen
werden, dauert die Chose auf meinem Rechner (P4, 2 x 3 GHz, 2
GB RAM) trotz Application.ScreenUpdating = False ca. 25
Sekunden. Ich weiß ja, daß VBA keine sehr effiziente Sprache
ist, aber das ist doch wohl übertrieben. Woran kann das
liegen?

Hmmm, auch bei diesem nicht immer zuverlässig arbeitenden Code sollte das doch keine 20 Sekunden in Anspruch nehmen - hast Du vielleicht viele Formeln drin, die sich auf ausgeblendete Zeilen beziehen und dann jeweils neu berechnet werden? Oder gibt es Matrixformeln in der Mappe in exzessivem Stil?

Generell solltest Du beim Ausblenden von hinten nach vorne durch die Zeilen laufen, um zu verhindern, dass nach dem hochrutschen von Zeilen diese nicht einfach ‚vergessen‘ werden - aber da fällt mir ein, dass das nicht beim Ausblenden sondern beim Löschen von Zeilen zutrifft.

Anyway - da ich den Code schon geschrieben habe will ich ihn hier dennoch wiedergeben:

Sub GelbAusblenden()
Dim lngI As Long

 Application.ScreenUpdating = False

 With Range("Kramfarbe")
 For lngI = .Rows.Count To 1 Step -1
 .Rows(lngI).EntireRow.Hidden = (.Rows(lngI).Value = "gelb")
 Next lngI
 End With

 Application.ScreenUpdating = True

End Sub

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -

Hallo Rob,

Option Explicit lasse ich nie weg.

In der finalen Datei: ich auch nicht. War ja nur zum testen.

mag sein, so wie es aussieht tippst du das manuell ein!?
Denn in den anderen Modulen steht es nicht drinne.

Stlle in Extras–Otionen ein:
[X] Variablendeklaration erforderlich

20 Zellen und 25 sek? Hä, wie watt?
Zeig mal die Mappe.

http://www.file-upload.net/download-3564326/test.xls…
Issoch ächt was zu lang, ne!?

Hallo Rob,

kann ich nicht nachvollziehen, hier in der Tabelle siehst du „meine“ Werte wenn ich das nachfolgende Makro „test“ laufen liess.
In sind die Zeilenanzahlen in B die zeit in Sekunden die der dir vorliegende Code brauchte.
Wenn ich das für 65536 zeilen hochrechnen lasse komme ich auf etwas über 10 sek. und deine Kiste ist sicher schneller :smile:

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[test.xls]!Tabelle2
 │ A │ B │
──┼──────┼──────┤
1 │ 200 │ 0,05 │
──┼──────┼──────┤
2 │ 600 │ 0,13 │
──┼──────┼──────┤
3 │ 1000 │ 0,16 │
──┼──────┼──────┤
4 │ 1400 │ 0,22 │
──┼──────┼──────┤
5 │ 1800 │ 0,28 │
──┼──────┼──────┤
6 │ 2200 │ 0,33 │
──┼──────┼──────┤
7 │ 2600 │ 0,39 │
──┼──────┼──────┤
8 │ 3000 │ 0,45 │
──┴──────┴──────┘

Festgelegte Namen:
Kramfarbe : =Tabelle1!$H$2:blush:H$3800

Zahlenformate der Zellen im gewählten Bereich:
A1:A8
haben das Zahlenformat: Standard
B1:B8
haben das Zahlenformat: 0,00

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

Option Explicit

Sub GelbAusblenden(ByVal N As Long)
Dim KramRange As Range, KramIndex As Range
Application.ScreenUpdating = False
ActiveWorkbook.Names.Add Name:="Kramfarbe", RefersToR1C1:= \_
 "=Tabelle1!R2C8:R" & N & "C8"
Set KramRange = Range("Kramfarbe")
For Each KramIndex In KramRange
 If KramIndex.Value "gelb" Then KramIndex.EntireRow.Hidden = True
Next KramIndex
Application.ScreenUpdating = True
End Sub

Sub test()
Dim wks1 As Worksheet, N As Long, Zei As Long, T As Single
Set wks1 = Worksheets("Tabelle1")
Worksheets.Add after:=Worksheets(Worksheets.Count)
With ActiveSheet
 For N = 200 To 4000 Step 400
 wks1.Rows.Hidden = False
 T = Timer
 Zei = Zei + 1
 Call GelbAusblenden(N)
 .Cells(Zei, 1) = N
 .Cells(Zei, 2) = Timer - T
 .Cells(Zei, 3) = .Cells(Zei, 2) / .Cells(Zei, 1)
 Next N
 .Columns("B:B").NumberFormat = "0.00"
 .Cells(Zei + 1, 1) = 65536
 .Cells(1, 5) = "65536 Zeilen = " & \_
 Format(.Cells(Zei + 1, 1) \* Application.Average(.Range("C1:C" & Zei)), "0.00")
 .Range("A1").Select
 Charts.Add
 ActiveChart.ChartType = xlXYScatterLines
 ActiveChart.SetSourceData Source:=.Range("A1:B" & Zei + 1), PlotBy:=xlColumns
 ActiveChart.Location Where:=xlLocationAsObject, Name:=.Name
 .Range("A1").Select
End With
End Sub

20 Zellen und 25 sek? Hä, wie watt?
Zeig mal die Mappe.

Da, guck:
http://www.file-upload.net/download-3564326/test.xls…

Issoch ächt was zu lang, ne!?

Hallo Rob,

Viele Zellen/Zeilen „abklappern“ macht Vba müde :smile:
Excel-Funktionen sind da x mal schneller, probier das Nachfolgende mal:

Option Explicit

Sub Test2()
Dim T As Single
T = Timer
Call gelb2(65536)
MsgBox Format(Timer - T, "0.00") & " sek"
End Sub

Sub gelb2(ByVal N As Long)
Dim Zei As Long
On Error Resume Next 'wg. Specialcells
With Worksheets("Tabelle1")
 .Range("BA2:BA" & N).FormulaLocal = "=WENN(H2=""gelb"";1;"""")"
 .Range("BA2:BA" & N).Value = .Range("BA2:BA" & N).Value
 .Range("BA2:BA" & N).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
 .Range("BA2:BA" & N).ClearContents
End With
End Sub

Gruß
Reinhard

Autofilter ist nicht so günstig
Servus Thomas,

Nur eine kleine Zwischenfrage:

Warum machst Du das nicht mit dem Autofilter und der Bedingung
‚Gelb‘?

Die Idee ist prinzipiell natürlich gut, aber in diesem Fall weniger elegant, da es sich nicht um ein einheitliches Blatt handelt. Die fraglichen Zeilen sind weiter unten, und da dieser Switch (weg mit gelb) nur sehr selten durchgeführt werden muß, wären die Dropdown-Pfeile nicht so chic.

Aber Danke Dir für den Tipp & viele Grüße :smile:
-Rob.

Salu Thomas,

Hmmm, auch bei diesem nicht immer zuverlässig arbeitenden Code
sollte das doch keine 20 Sekunden in Anspruch nehmen - hast Du
vielleicht viele Formeln drin, die sich auf ausgeblendete
Zeilen beziehen und dann jeweils neu berechnet werden? Oder
gibt es Matrixformeln in der Mappe in exzessivem Stil?

Nun, im fraglichen Bereich (also in den potenziell ausgeblendeten Zeilen und eine Zeile darüber oder darunter) sind eigentlich keine komplizierten Formeln, nur einige Summen (vertikal und horizontal) werden gebildet. Da sich deren Werte aber nicht in Abhängigkeit von der Zeilensichtbartkeit ändert, sollten sie sich nicht auf die Performance des fraglichen Scripts auswirken.

Generell solltest Du beim Ausblenden von hinten nach vorne
durch die Zeilen laufen, um zu verhindern, dass nach dem
hochrutschen von Zeilen diese nicht einfach ‚vergessen‘ werden

  • aber da fällt mir ein, dass das nicht beim Ausblenden
    sondern beim Löschen von Zeilen zutrifft.

Anyway - da ich den Code schon geschrieben habe will ich ihn
hier dennoch wiedergeben:

Sub GelbAusblenden()
Dim lngI As Long

Application.ScreenUpdating = False

With Range(„Kramfarbe“)
For lngI = .Rows.Count To 1 Step -1
.Rows(lngI).EntireRow.Hidden = (.Rows(lngI).Value = „gelb“)
Next lngI
End With

Application.ScreenUpdating = True

End Sub

Okay, wird für künftige Fälle vorgemerkt :wink:

Vielen Dank & nächtliche Grüße
-Rob.

Zwischenmeldung
Salu Reinhard,

vielen vielen Dank, daß Du Dir wieder soviel Mühe gemacht hast. Ich muß Dein Script erst mal testen und die Hintergründe verstehen. Ich melde mich in kürze mit den Ergebnissen zurück :smile: Wollte nur erstmal laut geben, damit Du nicht so lange warten mußt.

Nächtliche Grüße :o)
-Rob.

Salu Reinhard,

kann ich nicht nachvollziehen, hier in der Tabelle siehst du
„meine“ Werte wenn ich das nachfolgende Makro „test“ laufen
liess.
In sind die Zeilenanzahlen in B die zeit in Sekunden die der
dir vorliegende Code brauchte.
Wenn ich das für 65536 zeilen hochrechnen lasse komme ich auf
etwas über 10 sek. und deine Kiste ist sicher schneller :smile:

Ich muß zugeben, daß ich die Zeitermittlung nicht ganz verstanden habe, aber ich habe nach etwa fünf Minuten das Script abgebrochen. Die Werte der bis dahin angezeigten zwei Zeilen waren bei mir

Tabellenblatt
│ A │ B │ C │
──┼──────┼──────┼──────┤
1 │ 200 │ 38,9 │ 0,19 │
──┼──────┼──────┼──────┤
2 │ 600 │ 65,4 │ 0,10 │
──┼──────┼──────┼──────┤

Ich habe es mit festen Indizes für die Zelle versucht (For i = 2 to 21 …), auch nix. Das Apklappern erscheint mir alternativlos. Ich weiß nicht, wie ich mit einer Funktion die Zeilen ausblenden kann. Zudem habe ich mit Funktionen schlechte Erfahrungen gemacht (siehe /t/excel-vba-eigene-funktion-werte-aktualisieren/646…

Kurz: Ich habe es nicht verstanden :o( Gibst Du mir noch einen Stubbs in die richtige Richtung?

Viele Grüße :o)
-Rob.

Hallo Rob,

kann ich nicht nachvollziehen, hier in der Tabelle siehst du
„meine“ Werte wenn ich das nachfolgende Makro „test“ laufen
liess.
In sind die Zeilenanzahlen in B die zeit in Sekunden die der
dir vorliegende Code brauchte.

Ich muß zugeben, daß ich die Zeitermittlung nicht ganz
verstanden habe,

was genau, in welchem Code?

aber ich habe nach etwa fünf Minuten das
Script abgebrochen. Die Werte der bis dahin angezeigten zwei
Zeilen waren bei mir

Tabellenblatt
│ A │ B │ C │
──┼──────┼──────┼──────┤
1 │ 200 │ 38,9 │ 0,19 │
──┼──────┼──────┼──────┤
2 │ 600 │ 65,4 │ 0,10 │
──┼──────┼──────┼──────┤

Du siehst ja die Werte bei mir für 200 und 600 Durchläufe, bei dir dauert das sehr beträchtlich länger.
An den Rechnerfähigkeiten kann das m.E. nicht liegen.
Ich habe WinXP auf 512 MB Arbeitsspeicher und 1,7 GHz schnell.
Da bremst bei dir etwas anderes.

Das Abklappern erscheint mir alternativlos.

Nein, im anderen Posting zeigte ich doch eine sehr schnelle Alternative.

Zudem habe ich mit Funktionen
schlechte Erfahrungen gemacht (siehe
/t/excel-vba-eigene-funktion-werte-aktualisieren/646…

Ich sehe da daß du noch Informationen nachliefern wolltest und das nicht getan hast. Erst wenn die kommen kann dir Thomas oder ein anderer überhaupt helfen *glaub*

Gruß
Reinhard