VBA Makro: Ausgeblendete Zeilen überspringen

mis0r · 12. November 2019 um 02:51

Hallo Leute!
Ich habe mir ein VBA Makro programmiert. Es geht um ein Spinnendiagramm das mit dem Makro automatisch den Bereich zugewisen bekommt, weil öfters Zeilen hinzu oder wegkommen.

Mein Problem ist, dass das Makro nicht funktioniert, wenn eine Zeile ausgeblendet ist…

Hier der Makro Code:

[code]
For i = 4 To 200

Zeile = 4

=> Falsch! 'If Tabelle1.Range(„B3:B200“).Hidden = True Then
Zeile = Zeile + 1

Reinhard_fb8cb7 · 12. November 2019 um 02:51

Kann mir das jemand so erweitern, dass er nur die
eingeblendeten Zeilen erneuert. Also so, dass man den
Autofilter ohne bedenken benutzen kann.

Hallo Michael,

der Html-Tag code geht hier nicht, nimm pre

Sub tt()
Dim Zeile As Long, Zelle As Range
on Error resume next
With Tabelle1
 For Each Zelle In .Range("B3:B200").SpecialCells(xlCellTypeVisible)
 Zeile = Zelle.Row
 '
 ' dein Diagrammcode
 '
 Next Zelle
End With
End Sub

Gruß
Reinhard

mis0r · 12. November 2019 um 02:51

Ich gebe euch mal mein gesamt programmiertes makro!

Also das makro hat den Zweck den Diagramm Bereich zu aktualisieren.

zuerst überschreibt es die formeln in meinem data sheet, dann löscht er den gesamten bereich und dann setzt er den Diagrammbereich von zeile 4 bis 200 neu auf!

Das funktioniert auch wunderbar, außer man verwendet den Autofilter => also wenn Zeilen ausgeblendet sind steckt meine for - schleife.

Makro:

'Überschreibt die Formeln der Zielwerte
 Dim oBlatt As Worksheet

 Set oBlatt = Tabelle1

 With oBlatt
 .Range("BC3").FormulaLocal = "=AV4/1000" 'Heat\_Flux
 .Range("BD3").FormulaLocal = "=AW4" 'HC\_FLux
 .Range("BE3").FormulaLocal = "=AX4" 'NOx\_Flux
 .Range("BF3").FormulaLocal = "=AY4\*1000" 'SM\_VAL2
 .Range("BG3").FormulaLocal = "=AZ4" 'VPI
 End With

 Set oBlatt = Nothing



' Löscht Diagrammbereichs2 bis auf einen Wert(Zielwert)
 Set cht = Diagramm2

 With cht

 For i = .SeriesCollection.Count To 1 Step -1
 If i \> 1 Then .SeriesCollection(i).Delete
 Next i

 End With

 Set cht = Nothing




'Erstellt neuen Diagrammbereich2
For i = 4 To 200

 Diagramm2.ChartArea.Select

 Diagramm2.SeriesCollection.NewSeries

 Diagramm2.SeriesCollection(i - 2).Name = "=Data!$B$" & CStr(i)

 Diagramm2.SeriesCollection(i - 2).Values = "=Data!$BC$" & CStr(i) & ":blush:BG$" & CStr(i)

 Diagramm2.SeriesCollection(i - 2).XValues = "=Data!$BC$2:blush:BG$2"


Next i

Kann mir da jemand weiterhelfen, dass das ding auch mit AUtofilter funktioniert

mis0r · 12. November 2019 um 02:51

Hallo Reiner!

Vielen Dank für deine erste ANtwort ich hänge schon seit 2 Tagen an diesem Problem!

Dein Makro funktioniert soweit

Nur das Problem ist, dass sich mein PC fast aufhängt wenn er das Makro ausführt und, dass nun in meiner Diagrammlegende Datenreihe 14, Datenreihe 19 auch zu finden ist.

Ich brauch es leider, dass z.b nur die 4 Reiehn die der AUtofilter anzeigt im Diagrammbereich zu finden sind.

Kann man das auch machen?

ich hab mal den gesamten text reingehängt

'Überschreibt die Zielwerte
 Dim oBlatt As Worksheet

 Set oBlatt = Tabelle1

 With oBlatt
 .Range("BC3").FormulaLocal = "=AV4/1000" 'Heat\_Flux
 .Range("BD3").FormulaLocal = "=AW4" 'HC\_FLux
 .Range("BE3").FormulaLocal = "=AX4" 'NOx\_Flux
 .Range("BF3").FormulaLocal = "=AY4\*1000" 'SM\_VAL2
 .Range("BG3").FormulaLocal = "=AZ4" 'VPI
 End With

 Set oBlatt = Nothing



' Löschen des Diagrammbereichs1 bis auf einen(Zielwert)
 Set cht = Diagramm1

 With cht

 For i = .SeriesCollection.Count To 1 Step -1
 If i \> 1 Then .SeriesCollection(i).Delete
 Next i

 End With

 Set cht = Nothing




'Erstellt neuen Diagrammbereich1

Dim Zeile As Long, Zelle As Range

On Error Resume Next
With Tabelle1
 For Each Zelle In .Range("B3:B200").SpecialCells(xlCellTypeVisible)
 Zeile = Zelle.Row

For i = 4 To 200

 Diagramm1.ChartArea.Select

 Diagramm1.SeriesCollection.NewSeries

 Diagramm1.SeriesCollection(i - 2).Name = "=Data!$B$" & CStr(i)

 Diagramm1.SeriesCollection(i - 2).Values = "=Data!$BI$" & CStr(i) & ":blush:BM$" & CStr(i)

 Diagramm1.SeriesCollection(i - 2).XValues = "=Data!$BI$2:blush:BM$2"

Next i
 Next Zelle
End With

Thomas_Ramel · 12. November 2019 um 02:51

Grüezi mis0r

Warum bearbeitest Du dauernd den Quellbereich des Diagramms?

Lass diesen über den kompletten Datenbereich laufen und blende dann mit dem Autofilter alle Daten aus, die Du nicht darstellen lassen willst.

Das Diagramm passt sich stets an die im Autofilter gewählten Daten an (wenn nicht ist eine Einstellung verbogen, die aber gerichtet werden kann).

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

mis0r · 12. November 2019 um 02:51

Hallo Thomas!

DU hast recht bei den BalkenDiagrammen funktioniert das auch hervorragend. ABer bei den Spinnendiagrammen müsste ich 200 Zeilen per Hand einlesen…

und mit dem Makro kann ich das per knopfdruck und könnte es auch auf 500 erweitern.

Weil der spinnendiagrammbereich wird in einer Reihe gewählt also in Reihe 4: BB4 und BI4 bis BM3

und das müsste ich jetzt für 200+ Zeilen jede einelne reihe machen

desswegen das blöde Makro^^

Reinhard_fb8cb7 · 12. November 2019 um 02:51

Nur das Problem ist, dass sich mein PC fast aufhängt wenn er
das Makro ausführt und, dass nun in meiner Diagrammlegende
Datenreihe 14, Datenreihe 19 auch zu finden ist.

Ich brauch es leider, dass z.b nur die 4 Reiehn die der
AUtofilter anzeigt im Diagrammbereich zu finden sind.

Hallo Michael,

ungetestet, wenn es nicht klappt dann lade mal die mappe hoch mit file-upload, s. FAQ:2606

Option Explicit

Sub tt1()
'Überschreibt die Zielwerte
Dim Zeile As Long, Zelle As Range, I As Integer, oBlatt As Worksheet, cht
On Error GoTo hell
Set oBlatt = Tabelle1
With oBlatt
 .Range("BC3").FormulaLocal = "=AV4/1000" 'Heat\_Flux
 .Range("BD3").FormulaLocal = "=AW4" 'HC\_FLux
 .Range("BE3").FormulaLocal = "=AX4" 'NOx\_Flux
 .Range("BF3").FormulaLocal = "=AY4\*1000" 'SM\_VAL2
 .Range("BG3").FormulaLocal = "=AZ4" 'VPI
 ' Löschen des Diagrammbereichs1 bis auf einen(Zielwert)
 Set cht = ActiveSheet.Shapes(1) 'Diagramm1
 With cht
 For I = .SeriesCollection.Count To 1 Step -1
 If I \> 1 Then .SeriesCollection(I).Delete
 Next I
 End With
 'Erstellt neuen Diagrammbereich1
 On Error Resume Next
 For Each Zelle In .Range("B3:B200").SpecialCells(xlCellTypeVisible)
 Zeile = Zelle.Row
 I = I + 1
 cht.ChartArea.Select
 cht.SeriesCollection.NewSeries
 cht.SeriesCollection(I + 1).Name = "=Data!$B$" & CStr(Zeile)
 cht.SeriesCollection(I + 1).Values = "=Data!$BI$" & CStr(Zeile) & ":blush:BM$" & CStr(Zeile)
 cht.SeriesCollection(I + 1).XValues = "=Data!$BI$2:blush:BM$2"
 Next Zelle
 On Error GoTo hell
End With
hell:
If Err.Number 0 Then MsgBox Err.Number & vbCr & Err.Description
Set oBlatt = Nothing
Set cht = Nothing
End Sub

Gruß
Reinhard

mis0r · 12. November 2019 um 02:51

Hallo Reinhard!

Ich darf das file leider nicht veröffentlichen. Ich habe dir eine mail geschickt.

DANKE

Thomas_Ramel · 12. November 2019 um 02:52

Grüezi Mis0r

Ich darf das file leider nicht veröffentlichen.

Das sollst Du ja auch nicht, sondern einen identisch aufgebaute Mappe mit Dummy-Daten drin.

Ich habe dir eine mail geschickt.

…und damit verbaust Du dir die Mithilfe anderer User hier - schade auch für die künftigen Mitleser, die nun nix mehr von einer gefundenen Lösung haben…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Reinhard_fb8cb7 · 12. November 2019 um 02:52

Ich darf das file leider nicht veröffentlichen. Ich habe dir
eine mail geschickt.

Hallo Michael,

eine Mappe zu anonymisieren geht doch fix.
Alles was nicht fürs Diagramm gebraucht wird fliegt raus.
Sind in einer Spalte verräzterische Namen/Bezeichnungen, schreibst in die oberste Namenszelle
Namen1
dann ziehste die rechte untere Ecke einfach nacu unten.

brauchste einen namen vermach wegen Autofilter, korrigiere das mhalt manuell.
Und wir brauchen keine Hunderete datenzeilen, 30 o.ä. reichen.

Zahlen, was ja ohne bezug nix sagt, aber bitte, die kannste auch mit
=ganzzahl(Zufallszahl()*100)
anonymisieren.

Gruß
Reinhard

mis0r · 12. November 2019 um 02:52

ich habe in die mail geschrieben, dass ich ihm das excel file schicke

und dass wir danach den lösungscode veröffentlichen!!!

Thomas_Ramel · 12. November 2019 um 02:52

Grüezi mis0r

DU hast recht bei den BalkenDiagrammen funktioniert das auch
hervorragend. ABer bei den Spinnendiagrammen müsste ich 200
Zeilen per Hand einlesen…

Das kann ich so nicht wirklich nachvollziehen - warum solltest Du 200 Datenriehen manuell einfügen müssen…?

Weil der spinnendiagrammbereich wird in einer Reihe gewählt
also in Reihe 4: BB4 und BI4 bis BM3

und das müsste ich jetzt für 200+ Zeilen jede einelne reihe
machen

Quatsch!

Markiere alle deine Daten und erstelle das Netzdiagramm.
Im zweiten Schritt gibst Du an, dass die Datenreihen in Zeilen zu suchen sind und du erhältst ein Netzdiagramm mit 200 Datenreihen.

Anschliessend kannst Du wie gewohnt über den Autofilter Datenreihen (also ganze Zeilen) ausblenden und diese werden aus dem Diagramm entfernt.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Thomas_Ramel · 12. November 2019 um 02:52

Grüezi Michael

ich habe in die mail geschrieben, dass ich ihm das excel file
schicke

und dass wir danach den lösungscode veröffentlichen!!!

mis0r · 12. November 2019 um 02:52

Hallo Leute!

nun aber ich habs stark vereinfacht. hoffe es geht trotzdem und euch ist klar um was es geht. wenn nicht einfach fragen

[MOD] Link auf eigenen Wunsch entfernt

mis0r · 12. November 2019 um 02:52

bzw. am ende dieses makros habe ich noch ein makro welches mir die leeren zeilen x bis 200 ausbledet, damit der diagrammbereich sinnvoll dargestellt wird…

also damit keine leeren datenreihen angezeigt werden

Reinhard_fb8cb7 · 12. November 2019 um 02:52

Hallo Michael,

ich habe in die mail geschrieben, dass ich ihm das excel file
schicke

korrekt. Habe ich darauf geantwortet daß du das tun kannst/solltest?
Nö. Ich habe dir nicht per Mail sondern hier geantwortet und dir versucht zu helfen deine mappe zu anonymisieren sodaß daß du sie unbesorgt hier hochladen kannst.

und dass wir danach den lösungscode veröffentlichen!!!

Reinhard_fb8cb7 · 12. November 2019 um 02:52

Hallo Michael,

einfach fragen? Okay, da biste bei mir richtig )

Sind für die Beantwortung deiner Anfrage alle vier Tabellenblätter und die drei Diagrammblätter notwendig, wenn nicht, warum sind sie da?

Dein Code bezieht sich auf das Blatt „data“.
Was sehe ich da, eine Überschriftszeile mit 96 Zellen die das Autofilter Symbol haben.

Toll, woher soll ich wissen wo du was filterst?

Und wenn es der bereich in diesem Auszug sein sollte:

Tabellenblatt: [Mappe1]!Tabelle1
 │ B │ C │ D │ E │ F │ G │ H │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 4 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 5 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 6 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 7 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 8 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
 9 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
10 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
11 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
12 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
13 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
14 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
15 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
16 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
17 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
18 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
19 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
20 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┼──────┼──────┼─────┼───┼───────┼────────┼──────┤
21 │ TEST │ test │ 1,5 │ 4 │ Meier │ MÜLLER │ test │
───┴──────┴──────┴─────┴───┴───────┴────────┴──────┘
Zahlenformate der Zellen im gewählten Bereich:
B4:B21,C4:C21,E4:E21,F4:F21,G4:G21,H4:H21
haben das Zahlenformat: Standard
D4:smiley:21
haben das Zahlenformat: 0,0

Tabellendarstellung erreicht mit dem Code in FAQ:2363

wie soll ich da filtern? Ich hatte dir geschrieben wie man das macht damit man nach Unterschieden filtern kann. Warum nicht umgesetzt?

Versuche das mal nicht von deiner Anfrage aus zu sehen sondern von der Helferseite.
Je einfacher du uns das machst und umso klarer deine Fragen sind umso schneller/besser kann dir geholfen werden.

Du brauchst vielleicht 3 Minuten um deine beispielmappe zu erstellen, so what.
10 Hilfsbereite brauchen wenn sie das was du das geschickst gebrauchsfähig zu machen auch je 3 min, das sind 30 min.

Also 27 min völlig für die katz, die sinnlos verpulvert wurden.
Das könnte dir ja kurz gedacht schnuppe sein, aber weitergedacht bedeutet das genauso klar, wenn du helferzeit sinnlos verpulverst so schlägt das auf dich zurück.

nämlich dann wenn du irgendwo anfragst und die helfer haben keine Zeit für dich weil sie grad ihre wertvolle zeit für so’n Scheiß verplempern.

Gruß
Reinhard

mis0r · 12. November 2019 um 02:53

Hallo Reinhard!

Tut mir leid,wenn du das in den falschen hals reinbekommen hast.

Ich habe nichts anderes vor mir wie du. Filtern tun wir mit dem Bereich A4:A200 wenn wir 1 in die zellen reinschreiben wollen wir genau diese angezeigt bekommen.

oder was meinst du? bzw. könntest du cheops und bosch aus deinem alten beitrag rausnehmen? da ich die vergessenhabe zu löschen

vielen dank

Thomas_Ramel · 12. November 2019 um 02:53

Grüezi mi0r

Ich habe nichts anderes vor mir wie du. Filtern tun wir mit
dem Bereich A4:A200 wenn wir 1 in die zellen reinschreiben
wollen wir genau diese angezeigt bekommen.

Ich sage erneut: Autofilter , so wie ich es hier beschrieben habe:

/t/vba-makro-ausgeblendete-zeilen-ueberspringen/6572…

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

mis0r · 12. November 2019 um 02:53

hast du dir die spinnendiagramm bereich angesehen vielleicht verstehe cih dich nícht ganz aber wenn ich den bereich bei einem einfach hinunter ziehe nimmt er die daten nicht