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

 

[VoG]

I created a chart manually on sheet3 then ran this with another sheet selected and it renamed the chart

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

Excel 2010.

 

[scolty1985]

may the fact that im using excel 2003 have anything to do with it as i just created a chart, copied the test() u had in ur last post and nothing happened.

 

[VoG]

I doubt it but I will test using Excel 2000 tomorrow.

 

[scolty1985]

i must be doing something stupid then. Thanks for ur help anyway though. I will try n see where I have screwed up.

Rgds

Scolty

 

[VoG]

Working in Excel 2000. To verify that the name of the chart has changed, show the Drawing toolbar, click on the Select Objects icon (the northwest facing arrow), click on the chart, note the name in the address bar.

 

[scolty1985]

Aye ok, i will open it up after work hrs and have a look and see if i can get it working again but i tried a little longer last night n i still had a blank chart, with just "series 1" on the right hand side.

Thanks again for ur help though