VBA Dynamischen Bereich sortieren ohne Blatt zu aktivieren

[sheady]

Hallo zusammen,

ich hab ein Problem mit folgendem Code, der immer ab dem Sortieren Probleme macht (Objekt unterstützt diese Eigenschaft oder Methode nicht). Hab schon hin und her gefummelt, aber finde den Fehler nicht.

With Application.Worksheets("Spielerliste")
Dim rng As Range
Dim anzahl As Integer
anzahl = WorksheetFunction.CountA(ThisWorkbook.Worksheets("Spielerliste").Range("N1:N2500")) - WorksheetFunction.CountBlank(ThisWorkbook.Worksheets("Spielerliste").Range("N1:N2500"))

    Set rng = Range(.Cells(1, "M"), .Cells(anzahl, "N"))

.rng.Sort Key1:=Worksheets("Spielerliste").Range("M1"), Order1:=xlAscending, Key2:=Worksheets("Spielerliste").Range("N1") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal
End With

Kann mir jemand sagen, woran es liegt?


Vielen Dank!

[schauan]

Hallöchen,

Du schreibst am Anfang

With Application.Worksheets("Spielerliste")

Bei allem, was dann dort vor dem End With passiert, brauchst Du das Blatt nicht mehr anzugeben, z.B:
nicht
ThisWorkbook.Worksheets("Spielerliste").Range("N1:N2500")
sondern
.Range("N1:N2500")

und musst aber konsequent darauf achten, dass der Punkt vor dem Bereich steht. Fehlt min. 1x, z.B.
nicht
Set rng = Range(.Cells(1, "M"), .Cells(anzahl, "N"))
sondern
Set rng = .Range(.Cells(1, "M"), .Cells(anzahl, "N"))

Wenn Du in verschiedenen Mappen unterwegs bist, solltest Du ThisWorkbook schon beim With verwenden, wenn der zu verwendende Bereich nur dort zutrifft.

[sheady]

Hey, danke für die Hinweise! Aber auch wenn ich das ganze bereinige, bleibt der Fehler erhalten.

With Application.Worksheets("Spielerliste")
Dim rng As Range
Dim anzahl As Integer
anzahl = WorksheetFunction.CountA(.Range("N1:N2500")) - WorksheetFunction.CountBlank(.Range("N1:N2500"))

    Set rng = .Range(.Cells(1, "M"), .Cells(anzahl, "N"))

.rng.Sort Key1:=.Range("M1"), Order1:=xlAscending, Key2:=.Range("N1") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal
End With

Der Fehler liegt wie gesagt irgendwo hier:

.rng.Sort Key1:=.Range("M1"), Order1:=xlAscending, Key2:=.Range("N1") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
    :=xlSortNormal

[hddiesel]

Hallo sheady,

dann versuche es einmal so:
anz=letzte belegte Zeile in N
Wenn letzte belegte Zeile in M größer ist, dann ist anz letzte belegte Zeile in M

Code:

Sub Tabelle_sortieren()
   Dim rng     As Range
   Dim anz     As Integer
   
   With Application.ThisWorkbook.Worksheets("Spielerliste")

       anz = .Cells(Rows.CountLarge, "N").End(xlUp).Row
       If anz < .Cells(Rows.CountLarge, "M").End(xlUp).Row Then anz = .Cells(Rows.CountLarge, "M").End(xlUp).Row

       Set rng = .Range(.Cells(1, "M"), .Cells(anz, "N"))
   
       rng.Sort Key1:=.Range("M1"), Order1:=xlAscending, Key2:=.Range("N1") _
           , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
           False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
           :=xlSortNormal
           
   End With

   Set rng = Nothing

End Sub

Was möchtest du eigentlich, mit der folgenden Zeile erreichen, bei genügend Leerzellen, geht anzahl ins Minus.

Code:

anzahl = WorksheetFunction.CountA(.Range("N1:N2500")) - WorksheetFunction.CountBlank(.Range("N1:N2500"))

[schauan]

Hallöchen,

beim With bitte

With ThisWorkbook.Worksheets("Spielerliste")

ohne Application

Ansonsten noch ein anderer Hinweis. Ich schrieb zwar von den Punkten vor den Bereichen, aber wenn Du einen mit Set definierst, kommt anschließend beim definierten keiner mehr davor.

Also nicht
.rng.sort
sondern
rng.sort