help for 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