juiste aantal kolommen voor een grafiek automatisch selecteren

[printshop]

Hoi Excel experts!

Zie bijlage.

in de invoertabel worden door de gebruiker gegevens ingevoerd. De laatste kolom bepaalt hoeveel van de kolommen in de UITVOERtabel er worden gebruikt. Dit heb ik gedaan met formules in de uitvoertabel. Het maximum hiervoor is 50, maar op dit moment staat er 9 ingevuld, waardoor de uitvoertabel niet helemaal gevuld is.

Onder de uitvoertabel staan twee grafieken, een groene en een rode om aan te geven welke goed en welke ongewenst is. Groen is dus gewenst. dergelijke grafiek dient er te worden gemaakt aan de hand van de uitvoertabel. Wat mij niet lukt, is om deze grafiek altijd te baseren op de juiste hoeveelheid kolommen uit de uitvoertabel. Want wanneer iemand in de invoertabel aangeeft dat er 9 jaren zichtbaar moeten zijn, dan moet ik handmatig de juiste kolommen in de uitvoertabel selecteren en dat wil ik dus niet. Als een gebruiker aangeeft dat er 50 jaren zichtbaar moeten zijn, dan moet Excel dus automatisch de hele uitvoertabel als bron voor de grafiek gebruiken.

Ik dacht dat ik dan een macro kon opnemen waarbij ik cel B12 zou selecteren om vervolgens met CTRL+SHIFT+PIJLRECHTS alle niet lege cellen zou kunnen selecteren, maar om een of andere reden selecteert hij dan ook alle cellen met "" als output. Ik dacht dat het probleem lag bij het feit dat de cel weliswaar leeg lijkt, maar toch een formule bevat. Dus om dat te omzeilen heb ik in de macro opgenomen dat hij de hele uitvoertabel kopieert, een nieuwe tabel plakt (alleen de waarden) waardoor een lege cel ook echt leeg is. Doe ik vervolgens hetzelfde trucje met CTRL+SHIFT+PIJLRECHTS dan selecteert hij alsnog alle cellen ook.

Misschien is het op te lossen met een instelling binnen excel, misschien is het iets anders, wat het ook is... ik heb geen idee.

Wie kan mij helpen altijd automatisch de juiste grafiek te maken?

Bonusvraag: als het je lukt om op een mooie manier de jaartallen in de grafiek te zetten (eventueel ingekort van 2023 naar '23) dan ben je helemaal een held(in)

Alvast bedankt! :thumb:

Groet, Roel

 

[snb]

Moet jij de investering niet afschrijven op levensduur ?
Ergo: reken je niet rijk.

Excel kent hiervoor al enige tijd dynamische tabellen (VBA: listobjects)

 

[printshop]

...

klopt in principe, maar is niet altijd wat de gebruiker zo wil tonen in dit geval. Ik wil het dus aan de gebruiker overlaten wat hij wil laten zien in de grafiek. Zo kunnen zonnepanelen die zichzelf in 9 jaar terugverdienen, wel 20 jaar meegaan. De gebruiker kan dat dan in een grafiek op deze manier willen laten zien.

 

[AHulpje]

Plaats deze eens achter Blad1:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$4" Then
        ActiveSheet.ChartObjects("Grafiek 1").Activate
        ActiveChart.SetSourceData Source:=Range("$A$12").Resize(, Range("G4").Value + 2)
    End If
End Sub

En met jaartallen:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$4" Then
        With ChartObjects("Grafiek 1").Chart
            .SetSourceData Source:=Range("$A$12").Resize(, Range("G4").Value + 2)
            .FullSeriesCollection(1).XValues = Range("$B$11").Resize(, Range("G4").Value + 2)
        End With
    End If
End Sub