Add Chart to constantly changing stats table

[Pancakes1032]

Hello,

Right now I have a code that will create a new sheet and table and add all of the collected data into the table. I want to also had a chart at the bottom of the table. I used the record macro to get the basics of the coding to add the table, the problem is the data is constantly changing. The columns and rows expand and extract depending on the amount of data collected for the day.

How do I code it so that the data collected expands the chart as well here:

ActiveChart.SetSourceData Source:=Range("B8:AG8")

and here:

ActiveChart.FullSeriesCollection(1).XValues = "='Master log'!$B$2:$AG$2"

. The ranges need to be able to expand or reduce depending on the table data.

I hope that makes sense. Thanks!

[Yongle]

SUGGESTION No1
Assuming that your data always begins at cell B8, to set your source data range, you could try something like this. It is likely you need to modify the chart name from Chart2 (see below)

Sub DynamicChartRange()
    Dim DataLastRow As Long, DataLastColumn As Long
    Dim MyDataRange As Range
    Dim DataLastCell As String
'calculate last data rows and columms
    DataLastRow = Range("B8").End(xlDown).Row
    DataLastColumn = Range("B8").End(xlToRight).Column
    DataLastCell = Cells(DataLastRow, DataLastColumn).Address(0, 0)
'set chart data range
    Set MyDataRange = Range("B8:" & DataLastCell)
MsgBox "The data range for the chart is set to:" & vbNewLine & MyDataRange.Address(0, 0)
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SetSourceData Source:=Range("B8:" & DataLastCell)
End Sub

How it works
The code assumes there are no blank cells below B8 until the last row of data, also no blank cells to right of B8 until last column of data range
VBA uses this assumption to arrive at the address of the last cell in range.
MyDataRange is set from B8 to the last cell
My chart was Chart2 . To identify a chart record a macro, activate the chart and then look at the vba from that macro

You could use a similar approach to make the Xvalues dynamic.

[Yongle]

SUGGESTION No2

You used the word "TABLE" above. This suggestion depends on what you mean by the word "Table".

If you create a table using the Insert Tab > Table,
and create a chart using the data from the table, the chart will stretch and contract automatically with the data.
You can add rows and columns and the table automatically includes them.
(This works with Excel 2010 onwards, I am not sure if it was a feature before that)