+ Reply to Thread
Results 1 to 4 of 4

Creating macro to continue reading data until empty cell is reached

  1. #1
    Registered User
    Join Date
    04-10-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Creating macro to continue reading data until empty cell is reached

    I have an excel spread sheet from which I must create graphs (XY scatter plots in this case)
    I wish to create a macro to create the graphs as the data changes weekly and graphs of each set of data must be created.
    Creating a macro to do this rather than having to manualy graph the data each week is not a problem.
    My issue is that the amount of data in each column changes weekly, Column A is always the same parameter as is column B etc etc, however the length of the columns vary, this week all columns contain 22 entrys, this could be 50 or any other number next week.
    Eg currently the macro will plot A1 to A22 against B1 to B22, if next weeks data sheet contains 50 values per column, the macro will plot A1 to A22 regardless and A23 to A50 will not appear on the graph.
    Is there a method of making the macro continue to read the data from the column and plot it until an empty cell is reached?
    Many thanks for any help on this matter.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating macro to continue reading data until empty cell is reached

    Maybe an easier way is to use dynamic named ranges and specify your graph source data in terms of these.

    Have a look at http://www.ozgrid.com/Excel/DynamicRanges.htm
    Martin

  3. #3
    Registered User
    Join Date
    04-10-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Creating macro to continue reading data until empty cell is reached

    Thanks for your help mrice, unfortunately my knowledge of excell is fairly basic, I followed the instructions in the link you provided but when I run the macro I still have the same problem, I'm sure its because of something I'm doing wrong but I don't know what it is. I've outlined my current method below, if you could spot where I'm going wrong or suggest an alternative method that would be a huge help.
    Open worksheet of raw data, 1st 4 rows contain text, rows 5 to 26 contain numerical data.
    Create 2 new sheets, name one graphs and one formatted data.
    create macro / start recording.
    insert scatter plot in "graphs" sheet, select data, plot A5:A26 on x axis against B5:B26 on y axis, format graph, axis lables etc
    Paste raw data column by column into "formatted data" sheet under user friendly headings as headings in raw data sheet are obtained from a machine and its unclear what they represent.
    stop recording and save.
    test macro by adding data to each column so they span from A5:A50 instead of A5:A26 as before, this is scenario I need macro to deal with when completed, varying amounts of data processed in the same manor.
    When I run the macro again with the additional data, the graph still only plots A5:A26 as before and the formatted data page contains only 1st 21 entries also.
    I have tried seting the dynamic ranges you suggested both before and within the macro, neither case made a difference.
    It is worth pointing out the data runs for 286 columns and is used to create 20 seperate graphs, I have only described A and B above for simplicity but I require the macro to work for the full volume of data.

    Also the dates in the raw data shet are in the format 04-04-12-13_41 is there a way to convert all these entries to a better format, 04/April/12 13:41 or similar, and the data is recorded at 5 minute intervals and must be graphed as such, I have been writing a column of increments of 5 by using A1=0, A2=A1+5 and dragging this down to whatever length is needed and using this as the x axis for all the plots, this suffers the same problem however of not increasing when the amount of data increases.

    Any help on any of these issues would be hugely appreciated!
    Thanks for taking the time to read this!

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Creating macro to continue reading data until empty cell is reached

    Can you post a copy of the workbook itself? - there are less likely to be problems than trying to deduce what it looks like from a description.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1