Graph Macro range question

[Guest]

Is there a way to select a range of cells ONLY if they have data in them
while creating a graph macro. Here is the code I have as of right now:

Sub Try1()
Sheets("Raw Data").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("C252:C256"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="AHT Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Where is says .Range("C252:C256") works this week, but the problem starts in
future weeks. Each week when I add data to my sheet it will add a new number
to the end of that range (I.E. C256 next week) If I just set my range to
C252,C352 then the usable portion of my grid is smashed all the way to the
left with 90 (or so) blank (0, null, what ever you want to call it) plots on
the line graph. This makes it unreadable. What I would like to do is make
my range change as data is added. This macro is tied to a button in excel so
that it can be ran only if need be and will not need to be there all of the
time. Might sound trivial but for as many graphs as I want to make it really
will help out with file size.

 

[Mark Ivey]

See if this will work...


Sub try2()
Range("C252").Select ' The upper left most cell with data in it
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet, _
Name:="AHT Graph"
ActiveChart.HasLegend = False
ActiveChart.HasTitle = False
ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = False
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False
ActiveChart.ChartType = xlColumnClustered
End Sub

 

[Guest]

That is great!!!!!
Thank you so much. I had to edit it just a bit to fit my scheet, WOW...
that worked right off hand. Here is what I ended up with:

Sub Create_AHT_Graph()

Sheets("Raw Data").Select
Range("C252").Select
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Manager A AHT Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Thank you once again.

 

[Ken Johnson]

Is there a way to select a range of cells ONLY if they have data in them
while creating a graph macro. Here is the code I have as of right now:

Sub Try1()
Sheets("Raw Data").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("C252:C256"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="AHT Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Where is says .Range("C252:C256") works this week, but the problem starts in
future weeks. Each week when I add data to my sheet it will add a new number
to the end of that range (I.E. C256 next week) If I just set my range to
C252,C352 then the usable portion of my grid is smashed all the way to the
left with 90 (or so) blank (0, null, what ever you want to call it) plots on
the line graph. This makes it unreadable. What I would like to do is make
my range change as data is added. This macro is tied to a button in excel so
that it can be ran only if need be and will not need to be there all of the
time. Might sound trivial but for as many graphs as I want to make it really
will help out with file size.

If you use a dynamic named range for your chart data you won't need to
use a macro, so you won't have to worry about clicking any buttons.

Assuming that your chart data is on Sheet1, is a continuous sequence
of Column C cells with data (ie no inbetween blank cells) and is the
last data in column C, ie no data below it in column C, you could use
the following formula in the "Refers to:" box on the "Define Name"
dialog...

=OFFSET(Sheet1!$C$252,0,0,COUNTA(Sheet1!$C:$C)-COUNTA(Sheet1!$C$1:$C
$251),1)

The steps in full are...

1. Go Insert|Name|Define... to bring up the Define Name dialog
2. Type a new name in the "Names in workbook:" box at the top eg
Cht1Data
3. Type the offset formula above into the "Refers to:" box at the
bottom of the dialog
4. Click Add then OK
5. You can check that you have been successful by then going Edit|
Goto... to bring up the "Go To" dialog, then type the name you used
into the "Reference:" box (Cht1Data in my case. NB not case sensitive,
so cht1data will also work) then click OK. The resulting selected
range should be the range of cells containing data in column C
starting at C252.
6. Select the chart then go Chart|Source Data... to bring up the
"Source Data" dialog|Click the "Series" tab, then in the "Values:" box
type =Sheet1!Cht1Data (following my example) then click OK.

Ken Johnson

 

[Mark Ivey]

Glad to help out...

That is great!!!!!
Thank you so much. I had to edit it just a bit to fit my scheet, WOW...
that worked right off hand. Here is what I ended up with:

Sub Create_AHT_Graph()

Sheets("Raw Data").Select
Range("C252").Select
Range(Selection, Selection.End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Manager A AHT Graph"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

Thank you once again.

 

[lovepeaceofmind]

Hi, Ken:

This is very interesting and is exactly what I am looking for, too. Is
there any way to set the offset formula to be all numbers in this
column EXCEPT THE LAST NUMBER?

Thanks,

Jorge

 

[Ken Johnson]

Hi, Ken:

This is very interesting and is exactly what I am looking for, too. Is
there any way to set the offset formula to be all numbers in this
column EXCEPT THE LAST NUMBER?

Thanks,

Jorge

Hi Jorge,

If the numbers to be graphed start in row 2 (heading in row 1) and if
they are a continuous sequence of numbers down the column (say column
A on Sheet1), then you could use...

=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A)-1)

The COUNT(Sheet1!$A:$A)-1 is the argument of the OFFSET function that
includes all the numbers except the last number.

Ken Johnson

 

[Guest]

What if I wanted to generate a time-series chart that would include all the
data contained in a variable number of continguous columns with variable
length? I've been struggling with this problem without much success for some
time...

The snippets of code shown below don't work, since only one column is always
used to define the max number of rows to be plotted (starting from cell B15;
row B contains column titles)--but I need a maximum range of x calculated for
ALL the columns...

Sheets("Data").Select
' Range("B15").Select
' Range(Selection, Selection.End(xlDown)).Select
' Range(Selection, Selection.End(xlToRight)).Select
lastCol = ActiveSheet.Range("B15").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("B15", ActiveSheet.Cells(lastRow, lastCol)).Select
' Range("B15", ActiveSheet.Range("B15").End(xlToRight).End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"

z.entropic

 

[lovepeaceofmind]

Hi, Ken:

Thanks for the answer. This means if I have the following example on
Sheet1:

ColumnA
Row1
Row2
Row3 5
Row4 6
Row5 7
Row6 10
Row7
Row8


Then the formula should be the following if I want to include 5,6, and
7 in my range (I DO NOT want to include the last number 10 in my
range):

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Am I doing correct based on your post?

Thanks,

Jorge

 

[Ken Johnson]

Hi, Ken:

Thanks for the answer. This means if I have the following example on
Sheet1:

ColumnA
Row1
Row2
Row3 5
Row4 6
Row5 7
Row6 10
Row7
Row8

Then the formula should be the following if I want to include 5,6, and
7 in my range (I DO NOT want to include the last number 10 in my
range):

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Am I doing correct based on your post?

Thanks,

Jorge

Hi Jorge,

That's right.

The OFFSET function takes 5 arguments.
The first one is reference, which in this case is Sheet1!$A$3.
The second one is rows, which in this case is zero because we want to
start from the row indicated in the reference argument ($A$3 indicates
row 3).
The third one is column, which in this case is also zero because we
want to start from the column indicated in the reference ($A$3
indicates column A).
The fourth argument is height, which in this case is COUNT($A:$A)-1
that returns 4-1=3, resulting in a height of 3 rows.
The fifth argument is width, which in this case is 1 because we are
only wanting values from one column (column A) to be included in the
named range. This argument can be left out, it has a default value
equal to the width of the reference argument, Sheet1!$A$3 is 1 column
wide. The same is true of the height argument when it is left out.

Don't forget, you can check that your formula is working by going Edit|
GoTo...then type the name of the dynamic named range in the Reference:
box then click OK. In your example this should result in rows 3 to 5
of column A being selected.

Ken Johnson

 

[Ken Johnson]

Hi Jorge,

That's right.

The OFFSET function takes 5 arguments.
The first one is reference, which in this case is Sheet1!$A$3.
The second one is rows, which in this case is zero because we want to
start from the row indicated in the reference argument ($A$3 indicates
row 3).
The third one is column, which in this case is also zero because we
want to start from the column indicated in the reference ($A$3
indicates column A).
The fourth argument is height, which in this case is COUNT($A:$A)-1
that returns 4-1=3, resulting in a height of 3 rows.
The fifth argument is width, which in this case is 1 because we are
only wanting values from one column (column A) to be included in the
named range. This argument can be left out, it has a default value
equal to the width of the reference argument, Sheet1!$A$3 is 1 column
wide. The same is true of the height argument when it is left out.

Don't forget, you can check that your formula is working by going Edit|
GoTo...then type the name of the dynamic named range in the Reference:
box then click OK. In your example this should result in rows 3 to 5
of column A being selected.

Ken Johnson

Hi Jorge,

I should have mentioned it's probably better to use COUNT instead of
COUNTA.

Ken Johnson

 

[Ken Johnson]

What if I wanted to generate a time-series chart that would include all the
data contained in a variable number of continguous columns with variable
length? I've been struggling with this problem without much success for some
time...

The snippets of code shown below don't work, since only one column is always
used to define the max number of rows to be plotted (starting from cell B15;
row B contains column titles)--but I need a maximum range of x calculated for
ALL the columns...

Sheets("Data").Select
' Range("B15").Select
' Range(Selection, Selection.End(xlDown)).Select
' Range(Selection, Selection.End(xlToRight)).Select
lastCol = ActiveSheet.Range("B15").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("B15", ActiveSheet.Cells(lastRow, lastCol)).Select
' Range("B15", ActiveSheet.Range("B15").End(xlToRight).End(xlDown)).Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"

z.entropic







- Show quoted text -

Hi z.entropic,

I had trouble figuring out whether you are plotting by columns or by
rows.

If you are plotting by columns then try...

Public Sub chart_range_by_cols()
Dim lngLastRowMax As Long
Dim lngLastCol As Long
Dim I As Long
Dim rngChartRange As Range
Dim coChart As ChartObject
'make lngLastCol the column number of the right-most
'column of data contiguous with B15
lngLastCol = Sheets("Data").Range("B15").End(xlToRight).Column
'Loop through the chart data columns to determine
'the column whose bottom-most row has the
'greatest row number and make
'lngLastRowMax equal that greatest row number
For I = 2 To lngLastCol
With Worksheets("Data").Cells( _
Worksheets("Data").Rows.Count, I).End(xlUp)
If .Row > lngLastRowMax Then
lngLastRowMax = .Row
End If
End With
Next

With Sheets("Data")
Set rngChartRange = .Range(.Cells(15, 2), _
.Cells(lngLastRowMax, lngLastCol))
End With
Set coChart = Sheets("Data").ChartObjects.Add(0, 0, 600, 300)
With coChart.Chart
.ChartType = xlLineMarkers
.Location Where:=xlLocationAsObject, _
Name:="Data"
.SetSourceData Source:=rngChartRange, _
PlotBy:=xlColumns
End With
End Sub

If you are plotting by rows then try...

Public Sub chart_range_by_rows()
Dim lngLastColMax As Long
Dim lngLastRow As Long
Dim I As Long
Dim rngChartRange As Range
Dim coChart As ChartObject
'make lngLastCol the column number of the right-most
'column of data contiguous with B15
lngLastRow = Sheets("Data").Range("B15").End(xlDown).Row
'Loop through the chart data columns to determine
'the column whose bottom-most row has the
'greatest row number and make
'lngLastRowMax equal that greatest row number
For I = 16 To lngLastRow
With Worksheets("Data").Cells( _
I, Worksheets("Data").Columns.Count).End(xlToLeft)
If .Column > lngLastColMax Then
lngLastColMax = .Column
End If
End With
Next

With Sheets("Data")
Set rngChartRange = .Range(.Cells(15, 2), _
.Cells(lngLastRow, lngLastColMax))
End With
Set coChart = Sheets("Data").ChartObjects.Add(0, 0, 600, 300)
With coChart.Chart
.ChartType = xlLineMarkers
.Location Where:=xlLocationAsObject, _
Name:="Data"
.SetSourceData Source:=rngChartRange, _
PlotBy:=xlRows
End With
End Sub

The codes loop through the columns (or rows) to find the maximum to
use for the whole charting range.

Ken Johnson

 

[lovepeaceofmind]

Ken:

Thanks for the clarification. I tried and your method is working.

What's the difference between "COUNT" and "COUNTA"? For my specific
question, how to use "COUNT"?

Thanks again for your help!

Jorge

 

[Ken Johnson]

Ken:

Thanks for the clarification. I tried and your method is working.

What's the difference between "COUNT" and "COUNTA"? For my specific
question, how to use "COUNT"?

Thanks again for your help!

Jorge

Hi Jorge,

COUNT only counts cells with numbers.
COUNTA counts cells with numbers, text or formulas. It counts cells
with an apostrophe ' even though the cell looks empty. It counts cells
with ="" that also look empty.
Often, the cells I'm wanting to be included in a named range for
graphing have their values determined by an IF formula. As the formula
goes down the column a condition is eventually satisfied and the
formula starts returning the empty string, "". I'm not wanting those
empty string cells included in the named range, so the OFFSET formula
that defines the named range uses the COUNT function to count the
cells with numbers. COUNTA does not work since it also counts the
cells with the empty string.

Ken Johnson