If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help macro for formatting pie chart
Hi I've got a macro for formatting pie charts in a report that I produce. The idea is that any count under 3 is not shown in the pie chart. The macro seems to work for most tables, but doesn't work when there are blank cells at the end of a table or the final column has a blank cell - it puts 0% labels on the pie chart. Can anyone help please (my vba skills are very rusty....) - I've copied the code below.... Thanks! Sub CalloutsChartData1() ' declare variables Dim intCount As Integer Dim intlength As Integer Dim ChartValue As String Dim strStart As String Dim strEnd As String ' initialise count intCount = 1 ' initialise variable within if statement Do Until intCount = 3 If intCount = 1 Then ChartValue = strDataType + "Title" ElseIf intCount = 2 Then ChartValue = strDataType End If ' select the sheet Worksheets(strSheetName).Select ' select the range Range(ChartValue).Select ' name the first cell in range ActiveWorkbook.Names.Add Name:=(strDataType + "Start"), RefersToR1C1:=ActiveCell ' move right to last cell with data Selection.End(xlToRight).Select ' name the last cell ActiveWorkbook.Names.Add Name:=(strDataType + "End"), RefersToR1C1:=ActiveCell strStart = strDataType + "Start" strEnd = strDataType + "End" ' select the range between first cell selected and last cell selected Range(strStart + ":" + strEnd).Select ' name the selected range ActiveWorkbook.Names.Add Name:=(ChartValue + "Data"), RefersToR1C1:=Selection intCount = intCount + 1 Loop ' clear highlighted ranges Range("endprogramrange").Clear End Sub -- monkeygirl ------------------------------------------------------------------------ monkeygirl's Profile: http://www.excelforum.com/member.php...fo&userid=2451 View this thread: http://www.excelforum.com/showthread...hreadid=518166 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Date range on reports | Mark_Milly | Setting Up & Running Reports | 10 | January 19th, 2006 03:49 PM |
pasting chart shows chart area, not the chart | Pirjo | Powerpoint | 3 | October 13th, 2005 04:31 AM |
Macro to change Chart Range when inserting a column | Mark | Charts and Charting | 1 | September 13th, 2005 01:12 PM |
Selecting Charts in a Macro | Herman Merman | Charts and Charting | 1 | August 18th, 2004 12:25 AM |
macro to change chart on excel data field | [email protected] | Charts and Charting | 3 | June 11th, 2004 05:16 PM |