VBA: mit Array Zeilen ausblenden

Bild

Betrifft: VBA: mit Array Zeilen ausblenden
von: Christoph
Geschrieben am: 23.09.2003 16:16:10

Hallo zusammen,
ich "bastel" grade an einem Filter. Zeilen, die nicht dem Kriterium in Cells(3, j) entsprechen, werden ausgeblendet.
Sicher, mit dem Autofilter geht das ganze auch, aber so wie ich diese Funktionalität einsetzten will, komme ich mit dem Autofilter nicht parat. Der untenstehende Code funktioniert fehlerfrei, aber etwas langsam. Daher stelle ich mir vor, statt für 20 Spalte jeweils 2000 Zellen abzufragen, hier Arrays einzusezten. Was Datenfelder betrifft, stecke ich allerdings noch in den Kinderschuhen.
hat jemand einen Ansatz für dieses Problem?
ich bin für jede Hilfe dankbar.
Grüße - und macht weiter so!
Christoph


Sub test_filter()
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
Rows("4:2000").EntireRow.Hidden = False
 For j = 2 To 20
    If Cells(3, j).Value <> "" Then
       For i = 4 To 2000
          If Cells(i, j).Value <> Cells(3, j).Value Then
             Cells(i, j).EntireRow.Hidden = True
          End If
       Next i
    End If
 Next j
Application.ScreenUpdating = True
End Sub

Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: ChrisL
Geschrieben am: 23.09.2003 17:45:00

Hi Christoph

Glaube nicht, dass es in diesem Fall mit Array viel schneller geht. Aber vielleicht ist folgendes ein Ansatz...

So wie es jetzt steht, fängt es bei Spalte B an... Falls B3 <> "" dann wird jede Zeile einzeln durchlaufen und mit Zelle B3 verglichen, als nächstes mit Spalte C usw.

Einige Zeilen sind aber schon ausgeblendet wenn du mit C, D etc. fortfährst. Schneller geht es vermutlich wenn du Zeile um Zeile prüfst. Wenn eine Bedingung z.B. bereits in Spalte B nicht erfüllt ist und die Zeile deswegen ausgeblendet wird, kannst du direkt mit der nächsten Zeile fortfahren (Exit For) und Spalte C, D, E etc. müssen nicht mehr überprüft werden.

Gruss
Chris


Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: Christoph
Geschrieben am: 23.09.2003 18:58:17

Hi Chris,
Danke für den Tipp. Klauseln dieser Art habe ich zuvor auch schon eingebaut. Dies funktioniert auch solange immer mehr Filter gesetzt werden. Wenn jedoch in einzelnen Spalten der Filter wieder gelöscht wir, so muß für die ganze Tabelle der Filter spaltenweise neu berechnet werden. und das dauert eben. Ich hatte hier nur den "nackten" Code gepostet, um diesen nicht mit allzuvielen Klauseln zu übersichtlicht werden zu lassen.
Ich hoffe natürlich weiter auf einen Lösungsansatz mit Arrays
Gruß
Christoph


Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: ChrisL
Geschrieben am: 23.09.2003 21:38:31

Hi Christoph

Vielleicht hast du recht mit dem Array muss deine 2. Nachricht nochmals genau lesen.

Hatte aber eben noch eine m.E. zündende Idee, obwohl noch nicht ganz fertig gedacht. Vielleicht gibt es sowas wie eine schlaue Formel, die sich per Application.WorksheetFunciton.irgendwas auf VBA übertragen liesse (evtl. CountIf). Die Formel sollte in einem Schlag die ganze Zeile mit allen Titeln vergleichen, findet eine Übereinstimmung nicht statt, kann die Zeile ausgeblendet werden. Damit müsstest du einfach alle Zeilen einmal mit der Formel durchlaufen und Schluss, du sparst dir damit etwa 19 Durchgänge. Nur wie die Formel aussehen müsste, soweit bin ich noch nicht :-)

bis dann
Chris


Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: ChrisL
Geschrieben am: 24.09.2003 11:50:40

Hi Christoph

Also... dein Gedanke mit dem Array bringt m.E. deshalb nichts, weil du nicht verschiedene Zeilen mittels Array gleichzeitig ein-/ausblenden kannst.

z.B.
Rows(Array(1, 3, 10)).EntireRow.Hidden = False
...hat bei mir nicht funktioniert.

Somit müsstest du also dein Array zum Abschluss trotzdem nochmals in einer Schlaufe durchlaufen und die Zeilen einzeln ein/ausblenden.

Die Idee mit der Formel würde zwar einiges bringen, nur fällt mir die Formel nicht ein. Bin nicht so der Profi auf dem Gebiet.

Anbei aber trotzdem ein neuer Vorschlag. Einerseits habe ich wie im ersten Beitrag geschrieben von Spalten dann Zeilen in Zeilen dann Spalten Durchlauf geändert. Ferner da ich davon ausgehe, dass du in der Regel mehr Zeilen ausblendest als einblendest den Code quasi in ein 'Negativ' geändert.

Schliesslich, habe ich dir trotzdem noch ein Array eingebaut, welches die relevanten Spalten enthält. Spalten die keinen Filter enthalten sollten ja nicht in jeder Zeile neu durchlaufen werden, deshalb werden nur die Spalten geprüft, die auch massgebend sind.

Gruss
Chris


Option Explicit


Sub test_filter()
Dim i As Integer
Dim j As Byte
Dim AnzSpalten As Byte, Arr As Variant
Application.ScreenUpdating = False
AnzSpalten = 21 - Application.WorksheetFunction.CountBlank(Range("B3:V3"))
ReDim Arr(AnzSpalten)
For j = 2 To 20
    If Cells(3, j) <> "" Then
    i = i + 1
    Arr(i) = j
    End If
Next j
Rows("4:2000").EntireRow.Hidden = True
 
For i = 4 To 2000
    For j = 1 To AnzSpalten
        If Cells(i, Arr(j)) <> Cells(3, Arr(j)) Then Exit For
        Rows(i).EntireRow.Hidden = False
    Next j
Next i
Application.ScreenUpdating = True
End Sub



Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: Christoph
Geschrieben am: 24.09.2003 15:06:25

Hallo Chris,
Vielen Dank für deinen Vorschlag.
Es ist allerdings noch ein Fehler drin. Es wird dabei nicht die Schnittmenge aus mehreren Filterkriterien (Bsp: Spalte 4 und 16) angezeigt, sondern alle Zeilen, die dem "größten" Kriterium entsprechen. (Also wenn durch das Kriterium in Spalte 4 z.B. 20 Zeilen eingeblendet werden und hiervon 5 Zeilen auch dem Kriterium in Spalte 16 entsprechen, dann bleiben alle 20 Zeilen sichtbar.) Dies ist bedingt durch den Durchlauf in:
For j = 1 To AnzSpalten
If Cells(i, Arr(j)) <> Cells(3, Arr(j)) Then Exit For
Rows(i).EntireRow.Hidden = False
Next j
da, jene Zeilen die dem ersten Kriterium entsprechen, eingeblendet werden. sollten diese dem zweiten Krit nicht entsprechen, dann blendet sie keiner mehr aus.

Schlußendlich komme ich beim Versuch, dies zu korrigieren, doch wieder darauf zurück, alle Zeilen einzublenden und die falschen auszublenden. In der Summe arbeitet diese Sub dann leider auch nicht schneller als zuvor.

Trotzdem - herzlichen Dank für deinen Einsatz
gruß
Christoph


Bild


Betrifft: AW: VBA: mit Array Zeilen ausblenden
von: ChrisL
Geschrieben am: 24.09.2003 16:59:34

Hi Christoph

Danke für die Rückmeldung. Hast recht, gab tatsächlich noch ein Fehler. Nachstehendes Makro ist m.E. das Optimum das du rausholen kannst.

Option Explicit


Sub test_filter()
Dim i As Integer
Dim j As Byte
Dim AnzSpalten As Byte, Arr As Variant
Dim OK As Boolean
Application.ScreenUpdating = False
AnzSpalten = 21 - Application.WorksheetFunction.CountBlank(Range("B3:V3"))
ReDim Arr(AnzSpalten)
For j = 2 To 20
    If Cells(3, j) <> "" Then
    i = i + 1
    Arr(i) = j
    End If
Next j
Rows("4:2000").EntireRow.Hidden = True
 
For i = 4 To 2000
    OK = True
    For j = 1 To AnzSpalten
        If Cells(i, Arr(j)) <> Cells(3, Arr(j)) Then
        OK = False
        Exit For
        End If
    Next j
    If OK = True Then Rows(i).EntireRow.Hidden = False
Next i
Application.ScreenUpdating = True
End Sub



Gruss
Chris


Bild


Betrifft: super - vielen Dank
von: Christoph
Geschrieben am: 24.09.2003 18:24:34

Hi Chris,
die Lösung liegt manchmal so nah...auf die Boolean-Variable hätte ich ja eigentlich selber kommen müssen - auf dieses Schema hab ich schon öfters zugegriffen.
Aber an dem "If Cells(i, Arr(j)) <> Cells(3, Arr(j)) Then" hab ich mir heute mittag die Zähne ausgebissen, da ich nicht auf den Trick gekommen bin, wenn zuvor Arr(i) = j ist, hier Arr(j) einzutragen.
Wie schon gesagt, ich steck bei Array's noch in den Kinderschuhen.

Vielen Dank, Chris
gerne wieder
Gruß
Christoph


Bild


Betrifft: Frage noch offen
von: Christoph
Geschrieben am: 23.09.2003 20:28:07

Hallo zusammen,
ich habe mir den Vorschlag von ChrisL nochmal zu Herzen genommen, aber ich bin mir eigentlich sicher, dass das noch nicht der Weisheit letzter Schluß sein kann. (Wobei das den Einsatz von ChrisL in keinster Weise schmälern soll - jedem, der sich mit meinen Problemen auseinandersetzt, bin ich dankbar)
Den Gedanken, das oben beschriebene mit Hilfe von Arrays zu lösen, habe ich noch nicht verworfen.
Die Idee:
Definiere einen Array so groß wie der relevante Bereich: (Range("B4:T2000"); suche in den Einträgen, welche spaltenweise dem Wert in Cells(3, j) mit j = 2 To 20 entspricht und übergebe die Zeilennummer an ein zweites Array. (dabei muss das zweite Array um eins größer werden)
Wenn du fertig bist, blende alle Zeilen aus und blende jene, derern Zeilennummer im zweiten Array stehen wieder ein.
fertig!

Die Frage ist nur....wie schreibt man das in VBA???
mir der Hoffnung auf weitere Hilfe
Gruß
Christoph


Bild


Betrifft: Frage ist beantwortet (siehe oben) o.T
von: Christoph
Geschrieben am: 24.09.2003 18:27:51




 Bild

Beiträge aus den Excel-Beispielen zum Thema " VBA: mit Array Zeilen ausblenden"