Deleting Duplicate items in a ListBox

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

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

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

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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

Which line?

You'll have to post more of the code.

CLamar wrote:

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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


--

Dave Peterson

First let me say, I do appreciate your assistance with this.
Here is my code below, I made some minor changes which is probably the cause
of the error. Here is the entire Sub:

Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
LastCellx = Sheets("DataTable").Cells(Rows.Count, "F").End(xlUp).Row
LastCelly = Sheets("DataTable").Cells(Rows.Count, "G").End(xlUp).Row
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Range("f2:F" & LastCellx)
.SeriesCollection(1).Values = Range("g2:G" & LastCelly)
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub




"Dave Peterson" wrote:

Which line?

You'll have to post more of the code.

CLamar wrote:

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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


--

Dave Peterson

Is this closer?

Option Explicit
Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

With Worksheets("datatable")
LastCellx = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCelly = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues _
= Worksheets("datatable").Range("f2:F" & LastCellx) _
.Address(external:=True)
.SeriesCollection(1).Values _
= Worksheets("datatable").Range("g2:G" & LastCelly).Value
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


CLamar wrote:

First let me say, I do appreciate your assistance with this.
Here is my code below, I made some minor changes which is probably the cause
of the error. Here is the entire Sub:

Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
LastCellx = Sheets("DataTable").Cells(Rows.Count, "F").End(xlUp).Row
LastCelly = Sheets("DataTable").Cells(Rows.Count, "G").End(xlUp).Row
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Range("f2:F" & LastCellx)
.SeriesCollection(1).Values = Range("g2:G" & LastCelly)
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


"Dave Peterson" wrote:

Which line?

You'll have to post more of the code.

CLamar wrote:

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

Hopefully this will be the last question on this topic. The code below works
fine when the range is G2 - G12, but when it changes to something longer for
example G2-G47 it gives me the error: " Unable to set the values property to
the series class". And the error doesnt apply to the XValues only the
..Values statement. I put an "**" where the error is occuring. I am assuming
it is having a problem with the SeriesCollection

Thanks

"Dave Peterson" wrote:

Is this closer?

Option Explicit
Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

With Worksheets("datatable")
LastCellx = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCelly = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues _
= Worksheets("datatable").Range("f2:F" & LastCellx) _
.Address(external:=True)
**.SeriesCollection(1).Values _
= Worksheets("datatable").Range("g2:G" & LastCelly).Value
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


CLamar wrote:

First let me say, I do appreciate your assistance with this.
Here is my code below, I made some minor changes which is probably the cause
of the error. Here is the entire Sub:

Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
LastCellx = Sheets("DataTable").Cells(Rows.Count, "F").End(xlUp).Row
LastCelly = Sheets("DataTable").Cells(Rows.Count, "G").End(xlUp).Row
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Range("f2:F" & LastCellx)
.SeriesCollection(1).Values = Range("g2:G" & LastCelly)
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


"Dave Peterson" wrote:

Which line?

You'll have to post more of the code.

CLamar wrote:

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson

This worked ok for me:

Option Explicit
Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

With Worksheets("datatable")
LastCellx = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCelly = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection.Add _
Source:=Worksheets("datatable").Range("f2:F" & LastCellx)
.SeriesCollection.Add _
Source:=Worksheets("datatable").Range("g2:G" & LastCelly)
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub

And I'm pretty much past the stuff I know about charts.

If you have more questions, you may want to start a new thread in the
microsoft.public.excel.charting newsgroup.

There are some pretty smart people who hang out there. And because it's
dedicated to charting, it'll be easier to get their attention.


CLamar wrote:

Hopefully this will be the last question on this topic. The code below works
fine when the range is G2 - G12, but when it changes to something longer for
example G2-G47 it gives me the error: " Unable to set the values property to
the series class". And the error doesnt apply to the XValues only the
.Values statement. I put an "**" where the error is occuring. I am assuming
it is having a problem with the SeriesCollection

Thanks

"Dave Peterson" wrote:

Is this closer?

Option Explicit
Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

With Worksheets("datatable")
LastCellx = .Cells(.Rows.Count, "F").End(xlUp).Row
LastCelly = .Cells(.Rows.Count, "G").End(xlUp).Row
End With

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues _
= Worksheets("datatable").Range("f2:F" & LastCellx) _
.Address(external:=True)
**.SeriesCollection(1).Values _
= Worksheets("datatable").Range("g2:G" & LastCelly).Value
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


CLamar wrote:

First let me say, I do appreciate your assistance with this.
Here is my code below, I made some minor changes which is probably the cause
of the error. Here is the entire Sub:

Sub VRHCharts()
Dim LastCellx As Long
Dim LastCelly As Long
On Error Resume Next
Application.DisplayAlerts = False
Charts("VRH1").Delete
On Error GoTo 0

Application.ScreenUpdating = False
Charts.Add
ActiveChart.Name = "VRH1"
LastCellx = Sheets("DataTable").Cells(Rows.Count, "F").End(xlUp).Row
LastCelly = Sheets("DataTable").Cells(Rows.Count, "G").End(xlUp).Row
With ActiveChart
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Range("f2:F" & LastCellx)
.SeriesCollection(1).Values = Range("g2:G" & LastCelly)
.HasTitle = True
.ChartType = xlXYScatterSmooth
.HasLegend = False
.ApplyDataLabels Type:=xlDataLabelsShowValue
.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
.PlotArea.Top = 18
.PlotArea.Height = 162
.Axes(xlValue).MaximumScale = 0.6
.Deselect
End With
Application.ScreenUpdating = True
End Sub


"Dave Peterson" wrote:

Which line?

You'll have to post more of the code.

CLamar wrote:

It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I really appreciate the help, now i all i have to do is assign the first
SeriesCollection to X and the second to Y

"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