Loop through Rows, Create Chart for each row with values from specific columns until Blank row

Question

I want to start in row 6 on a worksheet and go down each row to create PieCharts for each Row with the values from the Cells in the C,E,G & I Column of each row until it hits a blank row.

So far I have something like this to produce the first Chart, but having lots of trouble trying to loop this process (currently attempting do use a Do-Until-Loop which compares current row# with the value from the last row# that isnt empty)

Do

'ValueRange sets the Range of Cells needed to fill each Chart with data
ValueRange = ThisWorkbook.Worksheets("Testplan Überblick").Range(Sheets("Testplan Überblick").Cells(rownumber, 3), Sheets("Testplan Überblick").Cells(rownumber, 5), Sheets("Testplan Überblick").Cells(rownumber, 7), Sheets("Testplan Überblick").Cells(rownumber, 9))


Set Graph = Sheets("Testplan Überblick").ChartObjects.Add(Left:=180, Width:=270, Top:=7, Height:=210)
With Graph
.Chart.SetSourceData Source:=ValueRange
.Chart.ChartType = xlPie
.Chart.HasTitle = True
.Chart.SetElement (msoElementChartTitleAboveChart)
.Chart.ChartTitle.Text = Sheets("Testplan Überblick").Cells(rownumber, 1).Value
.Chart.FullSeriesCollection(1).XValues = TitelrangeBuild
End With

rownumber = rownumber + 1

Loop Until rownumber >= LastFoundRow   'LastFoundRow gives a Long-Value of the last filled Row#

Im guessing I would need a For-Each row approach but Im unable to make anything work, I can only ever create 1 chart

Cheers and thanks for your help!

EDIT: The line "ValueRange = ..." sadly throws an Error (Wrong Number of Arguments) that I cant seem to fix

Answer 1

I would count where the last row with values is, something like that ThisWorkbook.Worksheets("Testplan Überblick").Cells(1048576, 3).End(xlUp).Row and then loop from 6 to this value.

For simplyfication I assigned your worksheet to ws that makes reading the code easier.

Sub loop_till_end()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Testplan Überblick")
Dim i As Integer
Dim valuerange As Range
Dim graph As ChartObject
For i = 6 To ws.Cells(10489, 3).End(xlUp).Row
    Set valuerange = Union(ws.Cells(i, 3), ws.Cells(i, 5), ws.Cells(i, 7), ws.Cells(i, 9))
    Set graph = ws.ChartObjects.Add(Left:=(180 + (i - 6) * 270), Width:=270, Top:=7, Height:=210)
    With graph
        .Chart.SetSourceData Source:=valuerange
        .Chart.ChartType = xlPie
        .Chart.HasTitle = True
        .Chart.SetElement (msoElementChartTitleAboveChart)
        .Chart.ChartTitle.Text = ws.Cells(i, 1).Value
    End With
Next
End Sub

It will also not stack the graphs on top of each other but make them side by side. Left:=180 + (i - 6) * 180

The counter i is your rownumber.