CODEWITHSUNDEEP
vbaexcel
Zenaphor
Zenaphor

Reputation: 801

Excel vba Charting, editting the range

Sub StoragevsQuota()
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("E1:G32")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Storage Charts"
    ActiveChart.Parent.Name = "Used Space vs Disk Quota"
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveChart.ChartTitle.Text = "Used Space vs Disk Quota"
End Sub

within excel vba this produces a graph for me at the click of a button from the range E1:G32. But that is a static range

I want to keep roughly the same syntax but for it to loop through column E for example all the way down until no more data so it would be 32 then extend that range to G32

its the length of the columns that can vary for graphs that I will produce not how many columns, could anyone help me set it up so that like i said above reads down a column until no data input then extend the range in the case above across 3 columns?

Thanks Z

Continuation

Sub WeeklySuccessOrFailure() 
'On Error Resume Next
Dim lastRow As Long
With Sheets("Data")
lastRow = .range("AA" & Rows.Count).End(xlUp).Row
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data").range("AA1:AA & lastRow, AD1:&      lastRow, AE1:AE & lastRow")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Job Charts"
ActiveChart.Parent.Name = "Total Weekly Success or Failure"
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.ChartTitle.Text = "Total Weekly Success Or Failure Of Jobs"
End With
End Sub

Upvotes: 1

Views: 30399

Answers (4)

nes
nes

Reputation: 101

The working example for me:

Note: Grafik2 is an array which holds the row numbers such as 14, 22, 30. My Excel table is something like this:

Row Number, Name(Column A), Value (Column D)

...

14, Name1, 85

...

22, Name2, 75

...

30, Name3, 82

Dizisi = ""
For i = 1 To GrafikSay
    Dizisi = Dizisi + "A" + Trim(Str(Grafik2(i))) + ":D" + Trim(Str(Grafik2(i)))
    If i <> GrafikSay Then
        Dizisi = Dizisi + "; " 'in some countries this should be comma ", "
    End If
Next i
oBook.Charts.Add
oXL.activechart.SetSourceData Source:=oSheet.range(Dizisi)

Upvotes: 0

Jon Peltier
Jon Peltier

Reputation: 6063

If the data is a nice rectangle, surrounded by blank rows and columns, you can make it very easy. Select a cell in the data range, then run the code, which finds the entire region of data containing the active cell:

Sub MakeChartFromIndicatedRange1()
  Dim rng As Range
  Set rng = ActiveCell.CurrentRegion
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub

Or if the user is going to select the entire range, this code uses the selected region:

Sub MakeChartFromIndicatedRange2()
  Dim rng As Range
  Set rng = Selection
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub

Or totally mimic Excel's built-in range definer, which uses the selected range if it's more than one cell, or the whole region the active cell is contained in if only one cell is selected.:

Sub MakeChartFromIndicatedRange()
  Dim rng As Range
  If Selection.Cells.Count = 1 Then
    Set rng = ActiveCell.CurrentRegion
  Else
    Set rng = Selection
  End If
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub

Upvotes: 0

j boschiero
j boschiero

Reputation: 510

Personally i prefer the start and end cells of my ranges to be dynamic, so my code would be something like:

ActiveChart.SetSourceData Source:=.Range(.cells(1,5),.cells(7,lastRow))

But really only because i prefer the cell notation for specifying ranges. Siddharth's answer is pretty much spot on.

For multiple, non-contiguous ranges, i would create a Range variable and then use Union to assign whatever ranges i want to it.

So let's say i wanted columns AA, AD and AE, as in Siddharth's example, i would do the following:

Sub WeeklySuccessOrFailure() 
'On Error Resume Next
Dim lastRow As Long
Dim myRange as Range
With Sheets("Data")
lastRow = .range("AA" & Rows.Count).End(xlUp).Row
Charts.Add
Set myRange = Union(.range(.cells(1,27),cells(lastRow,27)),_
.range(.cells(1,30),.cells(lastRow,30)),_
.range(.cells(1,31),.cells(lastRow,31)))
ActiveChart.SetSourceData Source:= myRange
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Job Charts"
ActiveChart.Parent.Name = "Total Weekly Success or Failure"
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.ChartTitle.Text = "Total Weekly Success Or Failure Of Jobs"
End With
End Sub

I did not test the above code but i have used the myRange and Union method before.

I would say that it is somewhat clunkier than Siddharth's suggestion, but i find it easier to use for automating purposes. If you aren't ever going to automate the part of your macro that finds the columns to graph, i.e. it's always going to be the same columns, then Siddharth's method is easier and probably more intuitive.

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149305

Is this what you are trying? (UNTESTED)

Sub StoragevsQuota()
    Dim lastRow As Long

    With Sheets("Data")
        lastRow = .Range("G" & Rows.Count).End(xlup).Row

        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=.Range("E1:G" & lastRow)
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Storage Charts"
        ActiveChart.Parent.Name = "Used Space vs Disk Quota"
        ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
        ActiveChart.ChartTitle.Text = "Used Space vs Disk Quota"
    End With
End Sub

FOLLOWUP

@siddarth just to grab your attention any chance you could help me out with a problem I posted under comment below where boschiero commented, thanks – Zenaphor 8 secs ago

Is this what you are trying? (AGAIN UNTESTED)

ActiveChart.SetSourceData Source:=Sheets("Data").Range("AA1:AA" & lastRow & _
",AD1:AD" & lastRow & ",AE1:AE" & lastRow)

Upvotes: 2

Related Questions