CODEWITHSUNDEEP
vbaexcel
Wabonano
Wabonano

Reputation: 128

Ignoring empty cells in a chart VBA

I created a macro that results in a set of two columns and 0 to x rows.(x=number of rows in raw data). I'm plotting a chart with the next line of code.

Set MyChart = Sheet3.Shapes.AddChart(xlColumnClustered).Chart    
MyChart.SetSourceData Source:=Sheet2.Range("$A$1:$A$10,$b$1:$b$10")

This will plot just the Top 10 data .The problem is that there are cases where I have less than 10 rows. So, is there a way to make VBA to ignore empty pair of cells under the last data cell?

Update1: I need to use a colum chart and I'm deleting any past chart in the sheet and adding a new one Thanks.

Upvotes: 2

Views: 3935

Answers (3)

Jon Peltier
Jon Peltier

Reputation: 6063

This is an old question, but the accepted answer didn't really answer the question; the chart type was wrong and the discussion of #N/A is not relevant to the required chart type. The other answer was better but didn't limit the chart source data to 10 rows or 2 columns.

This code will insert the chart with the appropriate data range:

Sub CreateChart()
  Dim MyRange As Range
  Set MyRange = Sheet2.Range("A1").CurrentRegion.Resize(, 2)
  If MyRange.Rows.Count > 10 Then
    Set MyRange = MyRange.Resize(10)
  End If
  
  Dim MyChart As Chart
  Set MyChart = Sheet3.Shapes.AddChart2(, xlColumnClustered).Chart
  MyChart.SetSourceData Source:=MyRange
End Sub

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

Sub test()
        Dim MyChart As Chart
        Dim Ws As Worksheet

        Set Ws = Sheet3
        With Ws
            If .ChartObjects.Count > 0 Then
                .ChartObjects.Delete
            End If

            Set MyChart = .Shapes.AddChart(xlColumnClustered).Chart
            MyChart.SetSourceData Source:=Sheet2.Range("a1").CurrentRegion
        End With
    End Sub

Upvotes: 0

hnk
hnk

Reputation: 2214

This isn't a VBA programming question per-se. You'll need to use an XY-scatter kind of a chart, where you have X-axis and Y-axis specified on the spreadsheet.

Then you can set the chart settings to 'Ignore Hidden and Blank Cells'

Then your same code can be used to update the chart to any range even with extra cells.

If your formulas give out useful values only for the first 10 numbers and 'blank' afterwards, make sure you replace the 'blank' with an #NA using the function

=IFERROR(YourCell x 1, NA())

While NA() looks ugly on the spreadsheet, it's excellent for charts as the charts simply ignore this and then you can set the chart to plot

  1. Blanks or
  2. Interpolated values

in its place (depending on your settings)

For any other kind of chart, it's not possible to do this unless you track the changes on the spreadsheet with a WorkSheet Change event.

And the code to re-size your chart category and value data should be called from this event handler in the Worksheet (not a module)

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Code to handle the change, stuffing the chart category and value data goes here
End Sub

In the worst case that you can't change the range size, you can declare the range as dynamic in the spreadsheet and refer it by its name.

In this MSFT knowledgebase articule, you'll get information on dynamic defined ranges. http://support.microsoft.com/kb/830287

Upvotes: 1

Related Questions