Histogram with unknown column length data

[scolty1985]

Good afternoon all, I was hoping someone would be kind enough to help me with this wee problem.

Im trying to write a VBA program which will automatically create a histogram for me. I already have an example for plotting an XY scatter which im trying to modify but thus far with no success.

Issues:

1) Im not sure what the syntax is for creating a histogram

2) Im not sure how to use the existing example i have so it references an undisclosed number of rows in a column.

The existing code i have, written by someone here i believe, is as follows:

With Worksheets("Sheet3").ChartObject.Add _
    (Left:=200, Width:=400, Top:=250, Height:=225)
    .Chart.SetSourceData Source:=Worksheets("DataSheet").Range("H2:H6")
    .Chart.ChartType = xlXYScatterLines

I usually use the following:

LC = Range("A" & Rows.Count).End(xlUp).Row

To store the number of rows in a column. I dont know how i can use that in a range though, ie

...Range("H2: H(LC)").ch....

If someone could also comment on the correct syntax for a historgram so i can replace the line

Chart.ChartType = xlXYScatterLines

I would appreciate it.

Rgds

Scolty

 

[scolty1985]

Ok im now just stuck on the data range for the chart, any ideas how i can automatically get excel to calculate the number of entries in a column and then assign it into the Range section??

Source:=Worksheets("DataSheet").[COLOR="Red"][B]Range("H2:H6")[/B][/COLOR]
    .Chart.ChartType = xlColumnClustered

so H6 is altered automatically??

Thanks in advance

Rgds

Scolty

 

[VoG]

Try

LR = Worksheets("DataSheet").Range("H" & Rows.Count).End(xlUp).Row
Source:=Worksheets("DataSheet").Range("H2:H" & LR)

 

[scolty1985]

works like a charm, thanks very much

 

[Rasm]

This code in link below creates a histogram - it also allow you to display the Histogram using two different .CharType properties

http://www.excelfox.com/forum/showthread.php?14-Making-a-Histogram-Chart-Using-Arrays

 

[scolty1985]

Thanks again VoG and Rasm. I have one finaly queery, how do i state the name for the chart?

I tried adding in the following lines within the WITH block but none seemed to work.

ActiveChart.Name = "Positive Distribution"

and

Chart.Name = "Positive Distribution"

any ideas??

Thanks in advance.

 

[VoG]

As you have just added the chart it should be the last one on the sheet so try

ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Name = "Positive Distribution"

 

[scolty1985]

i tried putting it inside and outside the WITH block and nothing happened.

With Worksheets("Sheet3").ChartObjects.Add _
    (Left:=910, Width:=400, Top:=250, Height:=225)
    .Chart.SetSourceData Source:=Worksheets("Sheet3").Range("G2:G" & LS)
    .Chart.ChartType = xlColumnClustered
    ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).Name = "Positive Distribution"
End With

 

[VoG]

Try taking that out and add after End With

Worksheets("Sheet3").ChartObjects(Worksheets("Sheet3").ChartObjects.Count).Name = "Positive Distribution"

 

[scolty1985]

Still no difference