Range automatisch bestimmen

[J.K.H.]

Hallo zusammen,

ich hoffe ich bekomme hier Hilfe von euch.
Habe ein Makro mit dem Rekorder aufgezeichnet, komme aber an meine bescheidenen Grenzen.
In dem Code sind Bereiche von Hand definiert, dieser Bereich ändert sich aber oft.
Ich möchte den Code gerne so angepasst haben, dass er automatisch erkennt wie groß der Bereich zum Autofill, oder Filtern ist.

Vielen Dank vorab, wenn sich jemand den Code mal ansieht und evtl. eine Verbesserung parat hat.
Beste Grüße J.K.H.

Code:

Sub Makro1()
    Range("A1").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Box"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Columns("B:B").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Columns("B:U").EntireColumn.AutoFit
    Range("A1").Select
   
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
   
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IF(--RC[2]<200,1,IF(AND(--RC[2]>=200,--RC[2]<800),2,IF(AND(--RC[2]>=800,--RC[2]<900),3,4))),5)"
   
Selection.AutoFill Destination:=Range("A2:A8839")     ---> hier z.B. das automatisch 8839 erkannt wird
Range("A2:A8839").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Application.CutCopyMode = False
   
    Selection.AutoFilter
    Range("A1").Select
    ActiveSheet.Range("$A$1:$U$8839").AutoFilter Field:=1, Criteria1:="=1", _ ---> und hier    ---> hier z.B. das automatisch 8839 erkannt wird ---> hier z.B. das automatisch 8839 erkannt wird
        Operator:=xlOr, Criteria2:="=3"
    Cells.Select
    ActiveWorkbook.Worksheets("temp").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("temp").Sort
        .SetRange Range("A:V")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ActiveSheet.Range("$A$1:$U$8839").AutoFilter Field:=1, Criteria1:="=2", _  ---> und hier, usw.
        Operator:=xlOr, Criteria2:="=4"
    ActiveWorkbook.Worksheets("temp").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("C:C"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("H:H"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("temp").Sort.SortFields.Add _
        Key:=Range("J:J"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("temp").Sort
        .SetRange Range("A:V")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A:$U").AutoFilter Field:=1
   
    Range("A1").Select
    Selection.AutoFilter
   
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
   
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],Tabelle1!C:C[1],2,0)"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A8839")
    Range("A2:A8839").Select
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("A8839").Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
        Application.CutCopyMode = False
   
Range("A1").Select
    Selection.AutoFilter
    Selection.End(xlToLeft).Select
End Sub

[HKindler]

Hi,

tu dir selbst einen Gefallen und schmeiss die ganzen xxx.Select und Selection.yyy etc. raus. Siehe dazu Achtung Makrorekorder oder Wer selektiert denn da?

Um dir bei deiner eigentlichen Frage helfen zu können, sollte man wissen, wodurch A8839 bzw. U8839 gekennzeichnet sind. Sprich wieso willst du Zeile 8839 haben und nicht Zeile 12345?

Eventuell reicht dir ja auch schon ein Range("A1").CurrentRegion

[J.K.H.]

Hallo Helmut,

Danke für die Antwort.
Hoffe das ich am WE die Zeit finde mich da reinzudenken.
Bis Zeile 8839 wollte ich haben, weil im Moment in dem File soviel Zeilen gefüllt sind.
Aber das ändert sich ständig.

BG J.K.H.
8839

[NobX]

Hallo,

wenn die Ranges jeweils an die tatsächlich verwendeten Zeilen angepasst werden sollen, solltest Du mit Variablen arbeiten. Mit diesem/n bestimmst Du vor dem Sortierbefehl oder ganz am Anfang Deines Codes ggf. die erst und in jedem Fall die letzte benutzte Zeile. Diesen Range kannst Du in Deinem Code z. B. so für einen Bereich in Spalte B einbauen:

Code:

Dim aLng As Long 'erste Zeile
Dim eLng As Long 'letzte Zeile

'....Ermittlung oder Festlegung der Variablen aLng und eLng

Tabelle1.Range("B" & aLng & ":" & "B" & eLng).Autofilter oder .Sort usw. usw.

Grüße
Norbert