Office: (Office 2013) Dynamischer Zeilenbereich über VBA

Hallo zusammen,

ich arbeite gerade an einer Preisliste und möchte diese etwas "dynamisch" gestalten.
Es gibt ca. 30 verschiedene Produkte nach denen ich filtern möchte (ausblenden in der Preisliste), da nicht alle Kunden dieselben Produkte bekommen.

Den ersten Filter habe ich soweit fertig und er funktioniert auch wie gewünscht. Problem ist nun, dass sich bei 30 Typen immer mal etwas ändert (sprich: Zeilen werden eingefügt wegen neuen Produkten, Änderungen, etc.).

Wie bekomme ich es nun hin, dass das VB Skript erkennt wenn Zeilen davor, danach oder dazwischen eingefügt werden?

Der Filter-Code sieht folgendermaßen aus:

Code:

Private Sub CheckBox1_Click()

Dim sht As Worksheet
Dim varProdukt1 As Range

Set sht = Worksheets("Preisliste")
Set varProdukt1 = sht.Rows("12:40")

If CheckBox1 = True Then
varProdukt1.Hidden = True
Else
If CheckBox1 = False Then
varProdukt1.Hidden = False
End If
End If

End Sub

Wie ihr seht ist der auszublendende Bereich die Zeilen 12 - 40 in der Tabelle "Preisliste". Dieser Bereich müsste abgefragt werden ob eben Zeilen davor, danach oder dazwischen eingefügt wurden. Spalten gibt es (voraussichtlich) keine neuen, lediglich Zeilen.

Vielen Dank schon mal für Eure Hilfe!

 

Hallo Groot,

statt dem fixen Bereich kannst Du die letzte Zeile ermitteln (https://www.excel-inside.de/vba-loe...zte-spalte-und-letzte-zelle-per-vba-ermitteln) und damit den bereich festlegen.

Gruß,
Lutz

 

Hallo Lutz,

vielen Dank für die schnelle Antwort. Diese Möglichkeit kannte ich noch gar nicht.
Allerdings weiß ich nicht wie das jetzt genau aussehen soll, da ich ja eine Tabelle habe, mit Überschriften natürlich, aber alles untereinander.
Deshalb müsste ich ja dann quasi von der letzten Zeile zurückrechnen. Dieser Bereich wäre ja fest und daher nicht dynamisch.

Also bspw. von der letzten Zeile - 100 Zeilen, aber wenn etwas dazwischen eingefügt wird funktioniert es ja nicht mehr.
Oder habe ich hier einen Denkfehler?

 

Hallo Groot,

dann lade doch mal ne Beispieldatei inkl. Code hoch.

Gruß,
Lutz

 

Hallo Lutz,

anbei die Datei.

In der Tabelle "Preisliste" werden die einzelnen Produkte dargestellt mit den Preisen und Artikelbeschreibungen.
In der Tabelle "Filter" können die Produkte ausgeblendet werden (Ich habe bis jetzt nur "Produkt 1" programmiert, die anderen Checkboxen sind einfach nur da. Über die Radio Buttons kann der Preis errechnet werden
In der Tabelle "EK" stehen die Einkaufspreise sowie diverse Faktoren zur VK Berechnung sowie feste Aufpreise für bestimmte Varianten, die bilde ich in den Formeln ab (siehe z.B. in "Preisliste" C9).

Nun möchte ich dieses Ausblenden dynamisch gestalten falls neue Produkte / Änderungen hinzukommen.
Aufgrund deines Tipps habe ich nochmal Google bemüht und ich könnte mir vorstellen, dass es über "Find" Funktion läuft, indem ich in Spalte B z.B. nach "Produkt 1" suche. Wenn das gefunden wird bräuchte ich jetzt noch eine Funktion, die mir von da an weiter bis zur nächsten Zeile mit Hintergrundfarbe sucht, diese Zeile wird dann ausgelesen und nach Abzug einer Zeile, habe ich die endgültige Zeile... hoffe das das geht. :-)

Den Anhang Preisliste_Beispiel.xlsm betrachten

 

Hallo Groot,

aber warum machst Du das nicht einfach über einen Filter?
Mir geht der Sinn des ganzen nicht so richtig ein...

Gruß,
Lutz

 

Guten Morgen,

vielleicht denke ich zu kompliziert, allerdings bin ich anscheinend zu blöd den Filter zu erstellen, da mir die Überschriften nicht bei den Filtern angezeigt werden und wenn ich versuche einen Textfilter hinzuzufügen mit der entsprechenden Überschrift passiert nichts... auch weiß ich nicht wie ich den Bereich festlege...

 

Hallo,

ok, fangen wir gaaanz vorne an. Was genau ist Dein Ziel?
Möchtest Du Zeilen ausblenden (wofür)?
Möchtest Du bestimmte Daten suchen und in einer anderen Tabelle damit weiterrechnen?

Ich habe dein Ziel scheinbar noch nicht richtig begriffen.

Solltest Du einfach einen Filter über alles setzen wollen, in dem Du manuell ein- und ausblendest, einfach Bereich markieren und Filter setzen.

Gruß,
Lutz

 

Hallo,
das Ziel ist eine "dynamische" Preisliste zu erstellen.
Zeilen werden ausgeblendet, da nicht alle Kunden alle Produkte erhalten sollen bzw. dürfen, sprich für jeden Kunden kann eine individuelle Preisliste erstellt werden, mit eben ein paar Klicks in Checkboxen.
In der Tabelle EK wird der Preis berechnet und in die Tabelle Preisliste eingefügt. Es gibt auch verschiedene Preisstufen (A + B), je nach Kunde.
Später möchte ich noch eine Schaltfläche hinzufügen, die mir die Datei exportiert, mit den entsprechenden Filtereinstellungen, aber eben ohne die EKs. Aber das kommt erst später.

Da sich wie gesagt die Produkte, etc. auch mal ändern, und ich die Filter nicht jedes mal händisch anpassen möchten (den Zeilenbereich), möchte ich diesen Bereich eben dynamisch gestalten. Im obigen Beispiel kann ich denke ich nach der Überschrift (z.B. "Produkt 1" suchen, über die Find Funktion, da der Produktname einmalig ist in dieser Spalte), jetzt müsste ich noch wissen wie ich das Ende des Bereichs herausfinde. Die Überschriften sind ja entsprechend rot markiert. Ich bräuchte also dann eine Funktion, die mir aber der Zeile, wo der Produktname gefunden wurde weitersucht bis zur nächsten Überschrift, dann hätte ich meinen dynamischen Bereich.

Ich hoffe dass das halbwegs verständlich ist. Evtl. zu umständlich, aber es müssen noch andere Mitarbeiter außer mir diese Liste "bedienen" und es darf eben auf keinen Fall mal die Tabelle EK mit rausgehen..., deshalb der ganze Aufwand mit der Automatisierung...

 

Hallo Groot,

meinst Du das:

Code:

Set sht = Worksheets("Preisliste")

Zeile1 = sht.Cells.Find(What:="Produkt 1").Row
Zeile2 = sht.Cells.Find(What:="Produkt 2").Row - 1

Set varP1 = sht.Rows(Zeile1 & ":" & Zeile2)

 

Hallo Lutz,

ja, genau so suche ich das! Vielen Dank!

Nur müsste jetzt die "Zeile 2" noch dynamisch sein, da es ja sein kann, dass ein anderes, neues Produkt, in Zukunft dazwischen eingefügt wird.
Ich bräuchte also etwas, das mir nach der Formatierung sucht, da die Überschriften ja wie gesagt in roter Farbe sind... ich weiß allerdings nicht ob es das gibt.

 

Hallo Groot,

ändere den Code so

Code:

Zeile2 = sht.Cells.Find(What:="Preis", After:=Cells(Zeile1 + 1, 1)).Row - 1

Gruß,
Lutz

 

Ja, natürlich. Wie blöd.
Hätte ich auch selbst draufkommen können, ist ja immer gleich in der Zeile.
Sorry, habe den Wald vor lauter Bäumen nicht gesehen. Vielen Dank!