Jump to content


Photo

Using Macro to update source data in a chart

Chart Source data.

  • Please log in to reply
3 replies to this topic

#1 Frank.2day

Frank.2day

    Newbie

  • Members
  • Pip
  • 6 posts

Posted 04 July 2015 - 10:35 PM

I have a Range of cells. Each cell is a reference to another cell. example " =F1".  My range spans 1400 cells in a column. Cell F1 referrers to a cell in a different workbook. I am trying to select all the cells in the range which display a result. Using the below VBA I can select all cells in the range.

   Range("F2", Range ("F1") . End (xldown)) . Select

 

This will select all the cells in the range. I wish to only select only the active cells with results in them to use as my source data to update a scatter plot chart. Using a button to run my macro.

 

The problem is the references to other cells.  Excel looks at these as values, and reads them all instead of stopping at the last cell in the range.

 

Any help here would be greatful.

    



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 05 July 2015 - 11:00 AM

Hi Frank,

 

As I understand it, your macro is recording the equivalent of the <Ctrl>+<Down Arrow> shortcut key, which selects all cells below the active cell until an empty cell is found. The problem you're experiencing is that your cells contain formulas, so even if they appear blank they are not treated as blank by this command and are selected anyway.

 

I'm not aware of an easy way to make Excel ignore blank values when selecting cells, but you could solve this a different way by applying a filter to your data that will hide the empty cells.

Hidden cells are not charted by default, so they will not appear on your chart.

 

Filters are covered in Expert Skills Lesson 1-3: Apply a simple filter to a range.

 

I have attached an example worksheet showing how this could apply to your data:

 

Attached File  ChartBlanks.xlsx   14.42KB   127 downloads

ChartBlanks.png

 

If you want more control over how the hidden cells are handled, you can do so using the chart settings, as covered in the video lesson: Essential Skills Lesson 5-17: Deal with empty data points.

 

If you need to automate this, you could record a macro that reapplies the filter each time it runs. For more on recording macros, see the video lesson: Expert Skills Lesson 9-11 Record a macro with absolute references.

 

I hope this presents a solution, but please feel free to reply if we can offer any further assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Frank.2day

Frank.2day

    Newbie

  • Members
  • Pip
  • 6 posts

Posted 05 July 2015 - 06:17 PM

I have no blank cells. My range is a table as per your example. Again each cell in the Table has a reference to another range. " =F2". 1400 cells long in a column. So It interprets a value in each cell. So I think I need to change the VBA formula to look for text value so that it reads each cell as text. This way it will reads the Values as text, and will go to last entry. Range ("F2" , Range ("F1") . End (xldown)). Select  will select the proper range But it selects values by default. In VBA it is possible to change the Range property to Text. Just not sure where to place it. in the above formula. Have been searhing the net for a week to find how to do this. Only reference I have been able to find is in "VBA for Dummies 2010 Chapter 8 "Text Property" But it doesn't help with where the property goes in the formula. This might be some thing that should be put into the lessons also. LOL.

 

Frank



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 05 July 2015 - 09:52 PM

Hi Frank,

 

I'm not sure I understand what you're trying to do if there are no blank cells in your data. Why not simply create a chart based upon the data as is?

 

Perhaps you're trying to create a chart based upon only certain types of result (ie. text or numbers). If this is the case, you can still do this using a filter exactly as I suggested above.

 

I have attached another example that contains both numeric and non-numeric values and filters them to hide the non-numeric values in the resulting chart:

 

Attached File  ChartBlanks2.xlsx   14.48KB   87 downloads

ChartBlanks2.png

 

If this doesn't help, perhaps you could consider using Go To Special to select cells that result in numbers or text as required.

To do this, click Home > Editing > Find & Select > Go To Special, select the Formulas option and then tick the boxes as appropriate.

 

You can see a more detailed walkthrough of Go To Special in the video lesson: Essential Skills Lesson 2-8 AutoSelect a range of cells.

 

By using one of these two approaches while recording your macro, you should be able to achieve your goals without needing to modify any VBA code.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users