Adresse einer Zelle als Bereichsgrenze für Sortierung

[maine-coon]

Die Tabelle geht von A4 bis V800. Die Sortierung mache ich hiermit:

Code:

Sub Sortieren_Distanz_1()
    With Worksheets("Kundendistanz").Range("A4:V800")  'Sortieren nach Distanz_1
         .Sort Key1:=.Range("V4:V800"), Order1:=xlAscending, Key2:=.Range("U4:U800") _
         , Order2:=xlAscending, Key3:=.Range("B4:B800"), Order3:=xlAscending, Header:= _
         xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal, DataOption3 _
         :=xlSortNormal
    End With
End Sub

Es wird die gesamte Tabelle nach Spalte V aufsteigend sortiert. Das funktioniert gut.
Anschließend wird die Tabelle in meine Listbox eingelesen.

Code:

Private Sub Daten_Einlesen() 'Einlesen in die Listbox
    With Worksheets("Kundendistanz")
             ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(800, 22)).Address
    End With
end Sub

Jetzt mein Anliegen.
In Spalte V gibt es nur die Einträge 1, oder 2. Das kann sich immer wieder ändern. Aber es gibt nur die 1, oder 2.
Ich würde gern die Zeile, in der die letzte 1 steht, bevor die 2 beginnt, nehmen und als Bereich zum Einlesen in die Listbox nehmen.
Also alle Zeilen, die in Spalte V eine 2 haben sollen nicht in die Listbox eingelesen werden.

Ich habe versucht die 800 im

Code:

ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(800, 22)).Address

mit einer Variablen zu belegen. Leider vergeblich. Ich krieg es nicht gebacken.

Wie finde ich die Adresse der Zeile, die die letzte 1 hat und kann diese Adresse als Bereichsgrenze zum Einlesen in die Listbox verwenden?

[Steffl]

Hallo Achim,

da du eine sortierte Liste hast und auch die 2 vorhanden ist, sucht doch nach dieser und dann eine Zeile weniger (alternativ: Benutze die Find-Methode und passe die Suchrichtung an).

Code:

Private Sub Daten_Einlesen() 'Einlesen in die Listbox
   Dim lngC As Long
   With Worksheets("Kundendistanz")
            lngC = WorksheetFunction.Match(2, .Cells(4, 22).Resize(796), 0)
            ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(lngC - 1, 22)).Address
   End With
End Sub

Ergänzung: Mit der Find-Methode

Code:

Private Sub Daten_Einlesen() 'Einlesen in die Listbox
    Dim rngTreffer As Range
    
    With Worksheets("Kundendistanz")
             Set rngTreffer = .Cells(4, 22).Resize(796).Find(1, LookIn:=xlValues, lookat:=xlWhole, XlSearchDirection:=xlPrevious)
             ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(rngTreffer.Row, 22)).Address
    End With
End Sub

[maine-coon]

Hallo Stefan,

erst einmal vielen Dank für deine Bemühungen mein Problem zu lösen.
Ich habe die FIND Methode erfolgreich eingebaut.
Aber ich kapiere sie nicht.

Da steht Resize(796).... Die 796 ist keine Variable, sondern eine feste Zahl.
Wie kann denn hier der Bereich verändert werden?
Was macht denn dieses Risize?

Schönen Abend noch...

[Steffl]

Hallo Achim,

Du hast Recht: An dieser Position und mit einer festen Zahl ist das Resize nicht geeignet, insbesondere wenn mal dein Bereich jenseits der 800 Zeilen liegt. Ändere es mal so ab.

Code:

Private Sub Daten_Einlesen() 'Einlesen in die Listbox
    Dim rngTreffer As Range
    
    With Worksheets("Kundendistanz")
             Set rngTreffer = .Columns(22).Find(1, LookIn:=xlValues, lookat:=xlWhole, XlSearchDirection:=xlPrevious)
             ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(4, 22).Resize(rngTreffer.Row - 3)).Address
    End With
End Sub

Und für die Frage was macht das Resize zitiere ich mal die OH zu dieser Eigenschaft.

Ändert die Größe des angegebenen Bereichs. Gibt ein Range-Objekt zurück, das den geänderten Bereich darstellt.

Jetzt klar?

[maine-coon]

Nein, nicht klar.
Fehler 448. Benanntes Argument nicht gefunden.
Dann habe ich die 1 mal in "1" gesetzt... Gleicher Fehler....
22 Spalten, aber nur Zeile 4... Range(.Cells(4, 1), .Cells(4, 22)
Dann habe ich mal Range(.Cells(4, 1), .Cells(796, 22) genommen....
Geht auch nicht....

Ich werde eine andere Lösung verwenden...
In einem anderen Programm arbeite ich mit dem Autofilter.
Ich gebe die Werte vor, schreibe die Treffer in eine neue Tabelle und lese sie dann in die Listbox ein.

[BoskoBiati]

Hi,

warum nicht das:

Code:

lngC = WorksheetFunction.Match(2, .columns(22), 0)-1

[Steffl]

Hallo,

Code:

Private Sub Daten_Einlesen() 'Einlesen in die Listbox
    Dim rngTreffer As Range
    
    With Worksheets("Kundendistanz")
             Set rngTreffer = .Columns(22).Find(1, LookIn:=xlValues, lookat:=xlWhole, XlSearchDirection:=xlPrevious)
             ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(rngTreffer.Row - 3, 22)).Address
    End With
End Sub

[maine-coon]

Diese Variante funktioniert. Aber warum der Bereich von 796 auf derzeit 153 geht, ist mir schleiferhaft.
Wo wird hier gezählt?

Code:

With Worksheets("Kundendistanz")
            lngC = WorksheetFunction.Match(1, .Cells(4, 22).Resize(796), 0)
            ListBox1.RowSource = "Kundendistanz!" & .Range(.Cells(4, 1), .Cells(lngC - 1, 22)).Address
   End With

Na egal, es funktioniert gut.
Vielen Dank für die Hilfe.
Schönen Abend noch.

[Steffl]

Hallo Achim,

man kann nur vermuten, dass sich in dieser Zeile die erste 1 befindet.

[maine-coon]

Ja, so war es.
Ich habe es jetzt gelöst.
Vielen Dank für die gesamte Hilfe dazu.