More Excel Analysis

[guitardave78]

Thanks for the last bit of help!!! With some mods it works just like i want.
Next thing i spent all day doing is trying to make the macro write a chart dynamic ranges.
This is what i have

Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Tasks&quot.Range("A3:A12,H3:H12&quot, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Analysis for " & myFolder
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Subject"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Accuracy"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
Sheets("Chart&quot.Select


where you see Range "("A3:A12,H3:H12&quot"
I need the A12 and H12 to be dynamic depending on the number of rows.
Thanks in advance

 

[Databaseguy]

Go to the first row programatically.

As long as your data in contigous (no breaks in the rows), this will work

Selection.End(xlDown).Select

Then, just grab the address or row and concoctenate your range. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[dsi]

Use the row numbers as variables:

Dim sRange As String
Dim iLastRow As Integer

sRange = "A3:A" & iLastRow & ",H3:H" & iLastRow

Range(sRange)

 

[guitardave78]

i'm getting application defined or object defined error??

Dim sRange As String
Dim iLastRow As Integer

sRange = "A3:A" & iLastRow & ",H3:H" & iLastRow


Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range(sRange), _
PlotBy:=xlColumns

 

[Databaseguy]

You've gotta set iLastrow to something....How about a meld of the two suggestions:

Private Sub SelectRange()
Dim sRange As String
Dim iLastRow

ActiveSheet.Cells(3, 1).Select
Selection.End(xlDown).Select
iLastRow = Selection.Row
sRange = "A3:A" & iLastRow & ",H3:H" & iLastRow

Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1&quot.Range(sRange), _
PlotBy:=xlColumns ETC Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[guitardave78]

I think that has worked DatabaseGuy. Cheers!!!!