Change Source Data in VBA

Hi,

Please be aware that I have also posted this in the XL Programming group as 
I am not sure which is most appropriate.

I have a chart set up (eg a simple bar chart) that I want to be able to 
update automatically using VBA. The source data will  vary in quantity and I 
have no way of knowing in advance how many data there will be.
Eg
I might currently have:
A1    B1     C1     D1
10    12     15     35

but when I want to refresh the chart I might have:
A1    B1     C1     D1    E1    F1
15    5       10     20    25    10

(don't worry about the actual values).

How do I tell Excel to re-size the source data area automatically (ie as 
part of a VBA macro) - the number of data may reduce as well as increase?

I'm reasonalby comfortable with VBA but by no means am I a programmer, so a 
brief explanation of how the solution works would be appreciated.

This is in Office 2003 if that makes a difference.

TIA

Dave

0
Utf
11/9/2009 2:24:02 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
1367 Views

Similar Articles

[PageSpeed] 15

Before tackling this process using VBA, would it be possible to solve with a 
dynamic range?
http://peltiertech.com/Excel/Charts/Dynamics.html

In your scenario, perhaps a range defined something like:
=OFFSET($A$1,0,0,COUNT(1:1),1)
named "MyRange"

In your chart, the data range is:
='My Book.xls'!MyRange



-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Risky Dave" wrote:

> Hi,
> 
> Please be aware that I have also posted this in the XL Programming group as 
> I am not sure which is most appropriate.
> 
> I have a chart set up (eg a simple bar chart) that I want to be able to 
> update automatically using VBA. The source data will  vary in quantity and I 
> have no way of knowing in advance how many data there will be.
> Eg
> I might currently have:
> A1    B1     C1     D1
> 10    12     15     35
> 
> but when I want to refresh the chart I might have:
> A1    B1     C1     D1    E1    F1
> 15    5       10     20    25    10
> 
> (don't worry about the actual values).
> 
> How do I tell Excel to re-size the source data area automatically (ie as 
> part of a VBA macro) - the number of data may reduce as well as increase?
> 
> I'm reasonalby comfortable with VBA but by no means am I a programmer, so a 
> brief explanation of how the solution works would be appreciated.
> 
> This is in Office 2003 if that makes a difference.
> 
> TIA
> 
> Dave
> 
0
Utf
11/9/2009 2:38:02 PM
Reply:

Similar Artilces:

Change SID
Hi, I am making an application in VC++ using MFC, where I have to change the disk SID after cloning. Any help will be highly appriciated. Thanks, Sudipta > I am making an application in VC++ using MFC, where I have to change > the disk SID > after cloning. > > Any help will be highly appriciated. http://www.microsoft.com/technet/sysinternals/Security/NewSid.mspx ...

How do I change bullet design?
Hi, Suddenly when I use bullet points in Publisher 2000 they are no longer dot's and have turned into clacker boards! I'm sure that this is not the correct term but they're the boards that are used to mark scenes when shooting a film. I've gone into Format > Indents & Lists > Bulleted Lists and then selected the type of bullet I would like but it's still a clacker board. I've tried using differnet popular fonts (Arial, Times New Roman, Etc...) but I still get the same problem. Any advice would be appreciated. Regards, Sonars UK Have you tried a...

Excel VBA #2
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C474D5.F0F10580 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Bonjour, Je croyais que chaque fois que l'on lan=E7ait une proc du type Private = Sub, toutes les variables =E9taient remises a z=E9ro. apparemment il n'en n'est rien=20 Quelqu'un peut-il me dire comment remettre =E0 z=E9ro toutes les = variables avec une instruction ou quelques lignes de code ? Merci ------=_NextPart_000_000A_01C474D5.F0F10580 Content-Type: text/html; charset="iso-...

Need to permanently changing a row source after running code
Hello! I have added code to a form object's Not In List event to add the item to the row source automatically. While I'm in the form, the item is still there to select, but when I close the form & re-enter, the new items that I just had code add, are not there anymore & it defaults back to my original row source entries. It is not a lookup to another table or query. Just a simple value list. Any help will be extremely appreciated... I'm stumped & desperately need to wrap up this project? Thanks to everyone! Melinda S wrote: > Hello! > I have added code...

how can i combine 2 columns in excel into 1 without losing data?
Assuming columns A and B In C1 enter =A1 & B1 Double-click on the fill handle(black lump at bottom right corner)of C1 to increment the formula down the column as far as you have data in column B. If you would like a space between the data........ =A1 & " " & B1 Gord Dibben Excel MVP On Mon, 9 May 2005 16:26:03 -0700, sturville <sturville@discussions.microsoft.com> wrote: Not much to go on here regarding cell contents and formatting, but I'm assuming that you have text in the cells and you just want to make on long text string. (If it was numerical...

OnContextMenu forwarding, pWnd changes
Hi, In a CListCtrlEx1 I use this code to forward this message to the parent if the click was not on the header: void CListCtrlEx1::OnContextMenu(CWnd* pWnd, CPoint point) { if (pWnd != GetHeaderCtrl()) { CWnd* parent = pWnd->GetParent(); if (parent) parent->SendMessage(WM_CONTEXTMENU, reinterpret_cast<WPARAM>(pWnd), MAKELPARAM(point.x, point.y)); return; } void CXBTClientDlg::OnContextMenu(CWnd* pWnd, CPoint point) { if (point.x == -1) GetCursorPos(&point); CMenu menu; if (pWnd == &m_peers) { In the first function, pWnd points to a CListCtrlEx1, ...

Data Encryption
Is there a way to anonymize say a column of employee numbers in Excel? I want it done in such a way that for each year of data the number is encrypted the same way so i can use the encrypted value to look up data for that employee without knowing who the person is? Thanks for any assistance you can provide. What's the end purpose of this hiding/encrypting of the employee numbers? Just to hide them from you, or to hide from other people viewing the reports/output later? In either case you could 'hide' them by building a separate sheet that has 2 columns: a series ...

Calculation from results of form fields in VBA
I have stumbled (literally) on a solution for calculating the result of a formfield using the results of 2 others: If aDocForm("SupInfBox").Result = "inf" Then aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result - aDocForm("ULWireSetup").Result) / 2) + aDocForm("ULWireSetup").Result End If and it works. However, when I change simply the + to a - If aDocForm("SupInfBox").Result = "sup" Then aDocForm("IsoSuggest").Result = ((aDocForm("LLWireSetup").Result + aDocForm(&qu...

Change Number to Text , Case error when cell is String and format is General
Hi All How to check the cell is text or number ? When Cell is string, Case Excel close and recoveing. Sub FormatNum2str() '~~ 2010/02/18 Dim iRows As Long Dim iCols As Long Dim ir, ic As Long Dim iNum As Long Dim strVal As String If Application.Selection Is Nothing Then MsgBox "No Open Worksheet", vbCritical Exit Sub End If iRows = Application.Selection.Rows.Count iCols = Application.Selection.Columns.Count For ic = 1 To iCols For ir = 1 To iRows With Application.Selection.Item(ir, ic) If .NumberFormatLocal = "Gene...

Changing Text Import Wizard Defaults
I am using Excel 2007 and would like to change the Text Import Wizard's default column data format from General to Text; that is, whenever I run the wizard and go to Step 3 of 3, I would like all columns to be pre-configured as Text instead of General. Can this be done? If yes, then how would I do it? TIA for any assistance. -- Cm~ I don't think you can do it. But if your data always has the same file name and layout, you could record a macro when you open the file and specify each field. If the fields vary (in width??), you can make make it a bit easier. Open the f...

Text Wrap from Copied Access Data
When I copy data from Access to Excel, Excel automatically wraps the text. Is there a fix for this, so when I copy the data it does not wrap the text? Thanks! ...

Opening/importing data from database takes forever after upgrading office!
Hello. I recently reinstalled a users computer and upgraded his office version from 2000 to xp. Now when he imports data from a database it takes forever. It can take several minutes and before the reinstallation the operation only took seconds. What could be wrong? I did the same on his coworkers computer with the same result so there must be something that can solve the problem. I just can't figure it out. The run windows 2000 sp4. Please help. :^) //Johan Anyone? It never hurts to empty the windows temp folder (with excel closed), but it may not help. You may want to take...

Dynamically Hiding Empty Series with VBA?
My application generates a chart using VBA that defines the various column ranges and uses Union to sum them for .SetSourceData Sometimes a aparticular series has no valid data - but always has a header. How can I prevent that series from showing up in the chart AND its header showing up in the legend? One idea was to hide the EntireColumn after the chart is built but I cannot seem to find a way of determining whether a column range is empty of numeric data since they all have a header. Any ideas how to tackle this? Thanks Bill If you want to check if a particular column has only a ...

Data Validation #40
How can I restrict entry to ceels between the times of 18:00 & 06:00, this must allow 22:00 etc This is so it covers the night shift in 24hr format Pete Select the cells Choose Data>Validation From the Allow dropdown, choose Custom In the Formula box, type a formula that refers to the active cell, e.g.: =OR(B5>=0.75,B5<=0.25) Click OK Pete wrote: > How can I restrict entry to ceels between the times of > 18:00 & 06:00, this must allow 22:00 etc > > This is so it covers the night shift in 24hr format > > Pete -- Debra Dalgleish Excel FAQ, Tips...

Double Clicking On Cell To Edit Data
Hello, I modify existing cells contents quite frequently. Using the mouse and double clicking seems to me to be extra work. Is there any way via the keyboard to get into a cell to edit its contents? TIA Jim Mac Millan Jim Cursor to, F2 Beege "Jim Mac Millan" <fly_boy_jim@yahoo.moc> wrote in message news:OOiJF%23cLGHA.604@TK2MSFTNGP14.phx.gbl... > Hello, > > I modify existing cells contents quite frequently. Using the mouse and > double clicking seems to me to be extra work. > > Is there any way via the keyboard to get into a cell to ...

Data validation listbox still not working after troubleshooting
Hi All, The list box in each cell in ranges I've named are not working--only the top cell or record in the list box can be used for some reason. The list box, when clicked, does not even show the top value--just a blank field. Does anyone know why this would happen? It does not appear to be associated with: XP, corrupt wkbook, frames or window split, etc. I've searched this site and cannot find an answer. This boggles my mind... Thanks, Arnold Hi How did you define your data validation list? Did you enter values directly, or did you refer to some cell range on same sheet, or...

Problems with Vanguard Fund Data
I believe I have set up my accounts correctly and download active money statements from Vanguard, but although I have a total of about 6 different funds, the transactions all get lumped into 2 or 3 of them. I have to manually move them to correct funds. Does anyone else have this problem with Vanguard funds, or a solution? I have the same problem...even when I have shown one of the accounts as closed.Also, the transactions don't add up to the account balance...really weird. Whats the point of having online updates that don't work. any suggestions?? "Jim" wrote: > ...

limiting chart source data with criteria?
I know for a series you choose: Values: =A2:A10 is there anyway to limit it further by saying something to the effect of Values: =A2:10 but not equal to 0, like Values =A2:A10 <> 0 (I know that doesnt work, already tried it, but its kinda what I want to accomplish) I can go in and manually ctrl click through to limit the data source, but thats a pain, I'd rather choose 1 big block and have some statement exclude 0 values. I could sort the data....but according to my boss thats not an option, because she wants the order to be the same quarter to quarter no matter what the values....

Change requested ship date on SOP and have it update on PO
We do mostly drop ship orders so we have a link to a PO on the SOP. I know when you first enter in a SOP the requested ship date will update the required date on the PO. But, if you change the ship date on the SOP after the link to the PO is created the required date on the PO won't update. Is there a way to get the PO to update automatically? Thanks Jennifer If the system doesn't automatically update - you could create an SQL trigger to update the POP tables. Example of what a trigger can do...........I had to create a SQL trigger for a client that wanted to change the re...

How do you change the right margin of a header in Excel?
Is there any way to change the right margin in Excel, or is it going to default no matter what? The margins of left and right headers cannot be changed. The only way to move them is to pad with spaces. i.e. enter your right header as custom then add several spaces to move it left. You cannot move it more to the right. Gord Dibben Excel MVP On Wed, 19 Jan 2005 14:19:05 -0800, ncford <ncford@discussions.microsoft.com> wrote: >Is there any way to change the right margin in Excel, or is it going to >default no matter what? Thanks for your help. However, I can't get it...

Fill Series only changing one input
I am not sure if this is possible, but I am hoping that it is. Right now, I am trying to create a formula (and fill down the column) that calculates the percentage of a total. My formula right now is as follows: =C2/(SUM('2010 Consultation'!B12+'2010_Raw Pivot'!C:C)) Column C in the sheet titled (2010_Raw Pivot) contains revenue. I need to know the percentage of revenue out of the total. However, the total is not just the summation of all of Column C. I need to take into account a cell that I have in another sheet (2010 Consultation! - Cell B12). When I use...

Filter changes upon reopening file!
Can anyone help with why my filter settings change upon opening a file?? The scenario is I save a file with particular filter criteria that display one row of about a 100. When I reopen it I see the 1 row briefly and then it reverts to a previous filter critera that displays around 10 of the 100!! I haven't been able to repeat the problem with a simple sheet but have now seen it multiple times ;-( The current offender has track changes turned on. TIA Barry Just a guess... Do you have macros in that workbook that could be helping out? If you close excel and reopen it in safe mode...

how do i control bubble size for each data series in excel 2007?
I'm trying to plot two bubble series with the same x and y values and then use transparency so I can see both bubbles. I need to use different bubble size scale factors since the data ranges of one series is 0 to 100 and the range of the other is 0 to 10,000. I suppose I could take the log of each to compress the range but I would rather be able to control the bubble size of each series independently. Thanks. Hi, Looks like a bug. In xl2003 you would have had to move 1 of the series to the secondary axis in order for the bubble scaling to have been different. In xl2007 it would...

How do I limit data entry columns?
I have 3 columns of data I want to record from a whole pile of bank statements: Date, Amount, Code So, it's a lot of data-entry. How do make the cursor cycle the 3 columns as I am inputing data so that I can just use the ENTER key after entering in these numbers? TIA Hi Mark, One way: Below your column headers, select a 3-column block of (say) 100 rows - change to suit, preferably slightly greater than your anticipated data entry requirement. Set cursor movement on enter to Down or Right (Tools | Edit | Direction) according to the desired data entry direction. --- Regar...

loop thru all forms to change properties
i want to add a command that will update header.backcolor... in (all) of the forms with in the current project...and am using the following code (below)... it loops thru the forms correctly, but does not change the color... Is there a caviat i'm missing? Private Sub Form_Open(Cancel As Integer) Dim frm As Object For Each frm In CurrentProject.AllForms If Mid(frm.Name, 1, 3) = "frm" Then FormHeader.BackColor = 11528154 txtHeaderTitle.ForeColor = 108 End If Next frm End Sub thanks in advance, mark You have to open each form in...