Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Graph Macro range question

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 Whe=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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 120
Default Graph Macro range question

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 Whe=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








"ElkySS" wrote in message
...
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 Whe=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.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Graph Macro range question

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 Whe=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.

"Mark Ivey" wrote:

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 Whe=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








"ElkySS" wrote in message
...
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 Whe=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.




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 120
Default Graph Macro range question

Glad to help out...


"ElkySS" wrote in message
...
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 Whe=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.

"Mark Ivey" wrote:

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 Whe=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








"ElkySS" wrote in message
...
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 Whe=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.






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 16
Default Graph Macro range question

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 Whe=xlLocationAsObject, Name:="Data"

z.entropic

"Mark Ivey" wrote:

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 Whe=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








"ElkySS" wrote in message
...
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 Whe=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.






  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default Graph Macro range question

On Aug 17, 11:06 am, z.entropic
wrote:
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 Whe=xlLocationAsObject, Name:="Data"

z.entropic



"Mark Ivey" wrote:
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 Whe=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


"ElkySS" wrote in message
...
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 Whe=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.- Hide quoted text -


- 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 Whe=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 Whe=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

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default Graph Macro range question

On Aug 5, 7:46 am, ElkySS wrote:
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 Whe=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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default Graph Macro range question

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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default Graph Macro range question

On Aug 17, 12:36 am, "
wrote:
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

  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default Graph Macro range question

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




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,073
Default Graph Macro range question

On Aug 18, 5:36 am, "
wrote:
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
graph range update from macro rick_mc Excel Programming 0 March 28th 07 04:10 PM
Trying to define a range for a graph dynamically within a macro jb Excel Programming 3 March 21st 07 08:36 PM
Macro for Graph - problem with Range Tanya Excel Programming 2 March 3rd 07 09:37 PM
Graph macro question Edwin Merced Excel Programming 4 December 5th 03 06:06 PM


All times are GMT +1. The time now is 09:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"