Clever-Excel-Forum

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

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

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

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

J.K.H.

HKindler

J.K.H.

NobX