Bei Tabelle Spalten ausblenden

Option Explicit
'Diagramm Strom [EG] aktualisieren
Public LC As Long
Public Const TB As String = "TabStromEG"
Public blnStart As Boolean
Sub DiaStromEGBearbeiten()
Dim AnzahlJahre As Integer
Dim LR As Long
Dim lngAnzahl As Long
Dim lngLauf As Long
Dim lngZeile As Long
Dim blnAlle As Boolean
If blnStart Then
blnStart = False
Exit Sub
End If
lngAnzahl = Worksheets("Einstellungen").Range("B11")
'Anzahl Jahre überprüfen
With Sheets("Einstellungen")
AnzahlJahre = WorksheetFunction.CountIf(Sheets("TabStromEG").Columns(2), "Nein")
If AnzahlJahre = "0" Then
Exit Sub
End If
End With
With Sheets(TB)
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 1
If .AutoFilterMode Then .AutoFilterMode = False 'Autofilter ausschalten
.Cells(2, LC + 2).Resize(LR - 1, 1).FormulaR1C1 = "=IF(AND(RC6="""",RC7=""""),""X"","""")"
.Cells(1, LC + 2) = "#TMP#"
.Columns(LC + 2).AutoFilter Field:=1, Criteria1:="=" 'Nur Leere anzeigen
If lngAnzahl > .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count + 1 Then
lngZeile = 2
blnAlle = True
Else
For lngZeile = LR To 2 Step -1
If .Rows(lngZeile).RowHeight > 0 Then lngLauf = lngLauf + 1
If lngLauf = lngAnzahl Then Exit For
Next lngZeile
End If
Charts("DiaStromEG").PlotVisibleOnly = True 'Ausgeblendete Zeilen weglassen
If Charts("DiaStromEG").FullSeriesCollection.Count = 0 Then
Charts("DiaStromEG").SeriesCollection.NewSeries
Charts("DiaStromEG").SeriesCollection.NewSeries
End If
Charts("DiaStromEG").FullSeriesCollection(1).Values = Worksheets(TB).Range("$F$" & lngZeile & ":$F$" & LR) '.SpecialCells(xlCellTypeVisible)
Charts("DiaStromEG").FullSeriesCollection(1).XValues = Worksheets(TB).Range("$A$" & lngZeile & ":$A$" & LR) '.SpecialCells(xlCellTypeVisible)
Charts("DiaStromEG").FullSeriesCollection(2).Values = Worksheets(TB).Range("$G$" & lngZeile & ":$G$" & LR) '.SpecialCells(xlCellTypeVisible)
End With
With Charts("DiaStromEG")
'Diagramm formatieren
With .FullSeriesCollection(1)
.ApplyDataLabels
With .Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.6000000238
.Transparency = 0
.Solid
End With
With .DataLabels
.Position = xlLabelPositionInsideBase
With .Format
With .Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.6000000238
.Transparency = 0
.Solid
End With
With .TextFrame2.TextRange.Font
.BaselineOffset = 0
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
End With
With .Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Weight = 1.5
End With
End With
End With
End With
With .FullSeriesCollection(2)
.ApplyDataLabels
With .Format.Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.6000000238
.Transparency = 0
.Solid
End With
With .DataLabels
.Position = xlLabelPositionInsideBase
With .Format
With .Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent2
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0.6000000238
.Transparency = 0
.Solid
End With
With .TextFrame2.TextRange.Font
.BaselineOffset = 0
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
End With
With .Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 80)
.Transparency = 0
.Weight = 1.5
End With
End With
End With
End With
End With
Charts("DiaStromEG").Refresh
If blnAlle Then MsgBox "Die Anzahl der Datensätze ist größer" & vbLf & _
"als die Anzahl der gefilterten Daten." & vbLf & _
"Es werden alle gefilterten Daten angezeigt."
End Sub