In Excel 2007, how do I define a data range with a function to dynamically resize to match # of rows/columns?

Question

I have an excel template with a macro to pull in data from various csv's. This template needs to have charts defined to display the data. The number of data points (i.e. rows) varies greatly from data set to data set. Also, the number of columns can also vary. How do I define a chart to automatically refit to the data? The first column will be the time stamp (x-axis) and every additional column needs to be plotted as it's own series. In addition, the chart's data series should chart all data points with no blanks at the end. Can any Excel warriors out there help me with this? I'm using Excel 2007.

Thanks!

Answer 1

Set tbl = ActiveCell.CurrentRegion

tbl.Offset(1, 0).Resize( tbl.Rows.Count - 1, _

tbl.Columns.Count).Select

This code will select the block of whatever size less the header row, probably is what you need to define the data source of the chart, to make sure it works require examine of the whole code.

Answer 2

Good question! I'm going to answer this question as if it were two separate ones. 1.The match that has my vote as the worse match will be Big Show vs "Money Mayweather. Lets face it if this match wasn't scripted,and the Show did not job it, Mayweather would not be in it. Neither he nor those who are vested in him would take the chance that would he get hurt in anything but a bout sanctioned by the Boxing Association. So they would make that a stipulation in the contract. There can be only one outcome to this match Mayweather wins. No surprises in this match. Besides Mayweathers ego wouldn't allow him loose even a scripted match. 2. I chose to answer this one last because there's a possibilty of three different answers. A) If Shawn Michaels beats Ric Flair then Flair no longer wrestles in WWE. If that happens it will be talked about for generations. But most of all there will be a change in WWE. For better or worse is anybodys guess. But there will be a change.Yes if Flair loses and has to leave that will truly be the match everybody will remember. B) If Flair wins Then the Definening match will be the Money in the Bank match. All the contestants will not hesitate to put their bodies on the line in this kind of match. I would be hard put to predict a winner. The Money in the Bank match is probely the hardest match to script because of the high number of possible injuries to the participants. In the end it really won't matter who wins because the talk will be about who did what to whom.And this year the possible outcomes are endless. C) THe Undertakers streak coming to an end. Quite frankly I would have to say if that happened there would be a lot of fans that would be angry at WWE. Mainly because they don't want to see the streak end,but also because the don't think edge is worthy of that honor. Sorry Edge but I would be one of them. Again this too would change WWE big time.

Answer 3

This is a bit beyond me but I can help you get the info. The best website to get answers to questions like this is www.mrexcel.com. Enter their message board (you'll have to become a member but it is free) and ask your question. I've been amazed at how fast I've received an answer on this site.



Here's the link: http://www.mrexcel.com/forum/index.php