CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

Update Chart to include last row data using defined range

I have a question , similar to this VBA: Modify chart data range But I the answer was based on horizontal data set.

How can I make this named range update to include latest row on a vertical column set ?

This is what I got as my formula, B1 being the header, and Column B will have all my data.

=OFFSET(Sheet1!$B$1,,,1,COUNTA(Sheet1!$B2:$B))

Edit

I am using the following defined range now:

=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B2:$B1000),1)

but when I add it to my chart I get the following error:

enter image description here

Upvotes: 2

Views: 654

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

You're almost there - you basically need to swap height parameter with width: =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B1:$B1000),1)

Here's a range that excludes header in B1: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B2:$B1000),1)

Of course you're welcome to extend the B1:B1000 range. Hope it helps!

Upvotes: 1

Related Questions