VBA - Autofilter, nur gefilterte Zeilen auslesen

[Michael O.]

Hallo Forum,

ich habe eine umfangreiche Tabelle mit Aufträgen, die über alle Spalten mit Autofilter gefiltert werden kann.
In der ersten Spalte steht die Auftrags-Nr..
In einem 2. Tabellenblatt möchte der Benutzer nun ausschließlich die aktuell gefilterten Aufträge angeboten bekommen.

Dazu habe ich folgendes eingerichtet:

• 1. Tabellenblatt mit Aufträgen:
  Spalte 1 mit Auftrags-Nr. = Name "data"
  Spalte 30 = Name "sel"
  
• 2. Tabellenblatt
  Zelle mit Datenüberprüfung, Zulassen: Liste, Leere Zellen ignorieren, Zellendropdown, Quelle = sel
  

Um nun den benannten Bereich "sel" mit den gefilterten Auftrags-Nrn zu füllen, gibt es folgendes VBA-Script.

Private Sub Worksheet_sel()

Dim obj_range As Range
Dim var_line As Long    'Zeilenzähler
Dim Sh As Worksheet
Set Sh = ActiveSheet

With Sh
    'Erste Zeile der Tabelle in Zeile 6
    var_line = 5
    
    'Benannten Zielbereich löschen
    .Range("sel").ClearContents
    
    'Tabelle lesen, benannter Bereich "data" enthält die eingegebe Auftrags-Nr.
    For Each obj_range In .Range("data")
        If obj_range.EntireRow.Hidden = False Then
            var_line = var_line + 1
             .Cells(var_line, 30) = .Cells(var_line, 1)   'Anm. 1
        End If
    Next
    
    'Fertig, alle eingeblendeten Zeilen bearbeitet
'    MsgBox ("Anzahl Zeilen gefiltert = " & var_line - 5)   'Anm. 2
    
    'Benannten Bereich dimensionieren
    ActiveWorkbook.Names("sel").RefersTo = .Range(.Cells(6, 30), .Cells(var_line, 30))    'Anm. 3
End With
End Sub

Das Ergebnis entspricht nicht den Erwartungen:

1. Im 2. Tabellenblatt wird in der Zelle mit der Datenüberprüfung nicht die gefilterte Auswahl angezeigt.
   Mache ich da einen Fehler bei der Dimensionierung des Bereiches (Anm. 3)?
   
   
2. Bei einigen wenigen gefilterten Zeilen wird die Auftrags-Nr. nicht in die Spalte 30 (sel) übertragen, stattdessen ist die Zelle leer (Anm. 1).
   
3. Die Anzahl der gefilterten Zeilen wird vom Script jedoch korrekt ermittelt (Anm. 2).
   

Die Basis des Scripts habe ich vor einiger Zeit bei einer Internet-Recherche gefunden und auf meine Bedürfnisse angepasst. Und ich bin recht sicher, dass es funktioniert hat, bevor der Kunde die Datei übernommen hat...

Für Ideen zur Fehlerbehebung und allgemein erhellende Hinweise bedanke ich mich schon mal im Voraus!

Viele Grüße

PS: Falls es hilfreich ist, die Original-Excel-Datei anzuhängen, kann ich das gern tun.

[Michael O.]

Zu Anm. 3 möchte ich noch ein Bildschirmfoto anfügen.
Der benannte Bereich wird schon so reduziert, dass er der Filterung entspricht. Nur den Inhalt verstehe ich nicht...

[snb]

Geht doch einfach so:

Code:

Sub M_snb()
    with sheets(1).cells(1).currentregion
       .autofilter 1, "abcde"
       .offset(1).copy sheets(2).cells(1)
       .autofilter
    end with
End Sub

Im Sheets(2) werden nur die gefillterte Daten gezeigt.

[Fennek]

Eine andere Variante:

Sub fennek()
Sheets(2).range("a1").CurrentRegion.clear
Sheets(1).range("a1").CurrentRegion.SpecialCells(xlCellTypeVisible). Copy
Sheets(2).range("a1").PasteSpecial (xlPasteValues)
End sub

Die gefilterte Tabelle ist in sheet1 und beginnt in a1.

(Per Hand übertragen, da besteht immer die Gefahr von Tippfehlern)

[Michael O.]

Die Aufgabenstellung ist schon speziell so, dass die Zelle (DropDwenListe) im 2. Tabellenblatt auf den benannten Bereich "sel" mit den gefilterten Ordnungsbgriffen zugreifen soll.
Es reicht nicht, einfach die gefilterten Ordnungsbegriffe in die 2. Tabelle zu kopieren.

[snb]

Code:

Sub M_snb()
    with sheets(1).cells(1).currentregion
       .autofilter 1, "abcde"
       .offset(1).copy sheets(2).cells(1)
       .autofilter
    end with
    sheets(2).cells(1).currentregion.columns(1).name="sel"
End Sub

[Kuwer]

Hallo Michael,

'.Cells(var_line, 30) = .Cells(var_line, 1)  '<-- falsch
.Cells(var_line, 30).Value = obj_range.Value '<-- richtig

Gruß Uwe

[Michael O.]

Hallo Uwe,

große Klasse und ganz herzlichen Dank!
Die DDLB in der zweiten Tabelle zeigt jetzt korrekt und vollständig die gefilterten Datensätze an!

Wenn ich mir aber den Inhalt der Spalte 30 im 1. Tabellenblatt anschaue, stehen dort völlig andere Werte bzw. oft steht auch gar nichts. Das verwirrt mich doch einigermaßen... Es könnte mir ja egal sein, denn die gewünschte Funktion ist ja gerettet! Aber verstehen würde ich es schon gern...

Hast Du da vielleicht noch einen Tipp für mich?

Vielen Dank im Voraus.

Viele Grüße
Michael

[Kuwer]

Hallo Michael,

es ist auch recht ungewöhnlich, dass die Liste für die Datenüberprüfung direkt in einer Spalte (bei Dir die 30 bzw. "AD") der gefilterten Liste erzeugt wird. Genau das macht ja Dein Code. Üblich wäre, einen unabhängigen Tabellenblattbereich oder ein anderes Tabellenblatt dafür zu nehmen.

Gruß Uwe

[Michael O.]

Hallo Uwe,

ach daran liegt es... Dann werde ich da mal drangehen.

Wäre es dann nicht auch sinnvoll, den Code aus der gefilterten Tabelle heraus in das 2. Tabellenblatt zu verlagern, ausgelöst durch einen Button? Die gefilterte Tabelle reagiert schon recht träge, wenn der Code bei jeder Filterung durchlaufen wird...

Besten Dank für die Anregungen!

Viele Grüße
Michael Obser