Untested:

..SeriesCollection.XValues _
= "Sheets("DataTable").Range("a2:A" & LastRoww).Address(external:=true)

..SeriesCollection.Values = "=DataTable!R2C6:R" & lastrow & "C6"
..SeriesCollection.Values = "=DataTable!R2C7:R" & lastrow & "C7"

You used lastroww (with 2 w's). Was that a typo or on purpose?

CLamar wrote:

This should probably be posted under a different question just in case
someone needs to use it, but anyway the problem i am having is that i am
trying to do a scatterplot, but i dont want to hardcode the the range because
it is going to change. Below is what i get for that section when i record
the macro, but i need to modify it to work for any size range, that is why i
asked how do you find the lastrow

With ActiveChart
.SeriesCollection.XValues = "=Sheets("DataTable").Range("a2:A" &
LastRoww).Cells
.SeriesCollection.Values = "=DataTable!R2C6:R21C6"
.SeriesCollection.Values = "=DataTable!R2C7:R21C7"
.HasTitle = True
.ChartType = xlXYScatterLines
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dist"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "VRH1"
End With
"

"Dave Peterson" wrote:

What column (letter or number) do you need to use?

Say its Z (26):

lastrow = .cells(.rows.count,"Z").end(xlup).row
or
lastrow = .cells(.rows.count,26).end(xlup).row

Then you can use:

dim myRng as range
with activesheet
set myrng = .range("Z2:Z" & lastrow)
'or
set myrng = .Range("Z2",.cells(lastrow,"Z"))
end with

But I think I'm missing the real point.

Am I?

CLamar wrote:

Okay I understand that, but how am i supposed to find the last cell in a
column so that i can use it in a series to create a plot without hardcoding
it.

thanks

"Dave Peterson" wrote:

R1C1 is used with formulas in the worksheet--not to refer to addresses in code.

But you could use .cells() and refer to rows and columns:

lastrow = .cells(.rows.count,1).end(xlup).row

The 1 represents column A (the first column).


..cells(x,y)
x = row
y = column




CLamar wrote:

How would you locate the last cell in a column using the R1C1 method?

"Dave Peterson" wrote:

You didn't copy all John's code into your module.

John has an "on error resume next" line that you dropped.

CLamar wrote:

Once again thanks for the quick response, but now its giving me a runtime
error 457 "this key is already associated with an element of this collection"

"Dave Peterson" wrote:

Typo in that last section...

Dim LastRow as long
dim myCell as range
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
for each mycell in .range("a2:A" & lastrow).cells
....

(I removed that final closing paren.)

CLamar wrote:

Thanks for the quick response. Now my question is how do i find the end of a
Range without hardcoding it. For example I have:
For Each Cell In Range("A2:","") I am trying to put in a null value to
locate the end of the column, but its not working

"Dave Peterson" wrote:

Take a look at John Walkenbach's routine to fill a listbox with unique values:
http://j-walk.com/ss/excel/tips/tip47.htm



CLamar wrote:

I have a Listbox in Excel and I am trying to loop through and delete the
duplicate entries. How can I do this using VBA

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson