Problem ofsetting a selection

[richanor]

Hi all

I have a piece of code which will find a data range within a worksheet and make me a bar chart of it:

Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Offset(-1, -1).Activate
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.Location Where:=xlLocationAsObject, Name:="pivot cash"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With

(this is not the entire code)...

My problem is that once I have selected the "block" of data which I want to be the source data for my chart, I want to reduce the data range by 1 column and 1 row (to cut off the totals). The code I have patched together above just moves the selection rather than reduces it.

Any help ammending this would be most appreciated.

Rich

 

[Boller]

Replace the first 3 lines with :-

Range(Selection, Selection.End(xlToRight)(1, 0)).Select
Range(Selection, Selection.End(xlDown)(0)).Select

 

[richanor]

Thanks a lot - that worked a treat. Do you know if it's possible to specify the size / location of the chart excel is creating? If so could you point me in the direction of any info regarding this.

Thanks again

Rich