M
monkeygirl
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
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