Hilfe bei Makro mit dynamischen Bereich erstellen

[florian2508]

Hallo zusammen,

ich bin ziemlich neu im Bereich der Makro Erstellung und würde daher gerne eure Hilfe in Anspruch nehmen.
Folgende Ausgangssituation:
Ich habe eine excel Datei mit zwei Tabellenblättern

- Auswertung ("Basis Datei, die sich jährlich in Bezug auf die Zeilen verändert: sprich in einem Jahr mal 1000 im nächsten Jahr vielleicht 1.100)
- Hochrechnung (das Tabellenblatt Hochrechnung wird derzeit mittels Makro erzeugt). Im Tabellenblatt Hochrechnung werden verschiedene Kennzahlen aus dem Tabellenblatt Auswertung berechnet.

Mein Makro funktioniert auch tadellos für die Berechnung der Kennzahlen Beispielformel =Anzahl(Tabelle1[VKZ]). Allerdings muss ich es jedes Jahr entsprechend der Zeilen Datensätze des Tabellenblattes Auswertung manuell anpassen.
Kann mir bitte jemand helfen um das Tabellenblatt Auswertung dynamisch zu gestalten?

Das bisherige Makro wurde aufgezeichnet und liegt im Anhang bei.

Für eine individuelle Rückmeldung/ Hilfe wäre ich sehr dankbar, da ich irgendwie über google und youtube bisher leider nicht weitergekommen bin.
Vielen Dank vorab.
LG,
Florian

[BoskoBiati]

Hi,

man sieht, dass das Makro aufgezeichnet wurde. Mangels Datei ist es allerdings nicht lauffähig, somit läßt es sich auch nicht vernünftig bearbeiten, aber deutlich verkürzen:

Code:

Range("A1:AN1078").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$AN$1078"), , xlYes).Name _
        = "Tabelle1"
    Range("Tabelle1[#All]").Select
   
    ActiveWindow.ScrollRow = 1054
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "Hochrechnung"
    Range("A1") = "Bestandserhebung Hochrechnung"
    Range("A2") = "Anzahl aktueller Mitgliedsvereine"
    Range("A3") = "Anzahl bereits gemeldeter Vereine"
    Range("A4") = "prozentualer Anteil bereits gemeldeter Vereine"
    Range("A5") = "bisherige Mitgliedermeldung"
    Range("A6") = "Abweichung zum Vorjahr in absoluter Zahl"
    Range("A7") = "Abweichung zum Vorjahr in Prozent"
    Columns("A:A").EntireColumn.AutoFit
    Range("A1").Font.Bold = True
    Columns("B:B").ColumnWidth = 40.86
    Range("B2").FormulaR1C1 = "=COUNT(Tabelle1[VKZ])"
    Range("B3").FormulaR1C1 = "=COUNT(Tabelle1[G Ges.])"
    Range("B4").FormulaR1C1 = "=R[-1]C/R[-2]C"
    Range("B4").Style = "Percent"
    Range("B5").FormulaR1C1 = "=SUM(Tabelle1[G Ges.])"
    Range("B6").FormulaR1C1 = "=SUM(Tabelle1[Abweichung zum Vorjahr (in Zahlen)])"
    Range("B7").Formula2R1C1 = "=R[-1]C/SUM(ABS(R[-2]C:R[-1]C))"
    Range("B7").Style = "Percent"
    Range("B7").NumberFormat = "0.0%"
    Range("B2:B3").NumberFormat = "#,##0"
    Range("B5:B6").NumberFormat = "#,##0"
   
    Rows("7:7").RowHeight = 27
    Rows("2:7").RowHeight = 21.75
   
    Sheets("Hochrechnung").Columns("B:B").ColumnWidth = 30
    Range("A2:B7").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

[florian2508]

Danke für die Antwort.

Leider ist es nur nach wie vor so, dass im Makro automatisch bis Zeile 1078 gezählt wird... das Makro soll aber nur bis zum letzten Datensatz zählen.
Das ganze wird auch noch etwas erschwert, dass unter den Datensätzen (ab Zeile 1078) noch zusätzliche Informationen stehen, die aber in den Formel 

Ich habe mal eine Beispielsdatei mit fiktiven Zahlen angehängt. Vielleicht wird es dann deutlicher... dort funktioniert das Makro gut.
Sind es aber mehr oder weniger relevante Zeilen in der Tabelle Auswertung, so werden leider trotzdem 1078 berücksichtigt in den Berechnungen.

Vielen Dank schon mal für die Bemühungen.

[schauan]

Hallöchen,

Da es sich um eine Tabelle/Liste handelt kannst Du auch mit den Eigenschaften des ListObjects arbeiten, z.B.:

Sub test()
MsgBox ActiveSheet.ListObjects("Tabelle1").DataBodyRange.Rows.Count
End Sub

[Gast 123]

Hallo

wenn das Makro tatsächlich nur Zeilen mit Werten zählen soll muss man mit LastZell  suchen arbeiten!   s. Demo Code.
Hier wird ab A1 nach unten die letzte gefüllte Zeile gesucht. Im Range Bereich muss man dann die Zahl "1078" durch  lz1 ersetzen.  s. unten
Ich hoffe das hilft weiter dieses Problem zu lösen ....

mfg Gast 123

Code:

Sub test2()
lz1 = Range("A1").End(xlDown).Row   'LastZell in Spalte
MsgBox lz1  '** nur zur Kontrolle
    Range("A1:AN" & lz1).Select
End Sub

[florian2508]

Danke für die Tipps.

Ich werde sie Ende der Woche testen.