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

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

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

Dim myCell as range
with activesheet
for each myCell in .range("a2",.cells(.rows.count,"A").end(xlup)).cel ls
...

It's the equivalent of starting in A65536 and hitting the End key followed by
the up arrow.

======
If you need it for other stuff:

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)
....




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

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

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

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

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

I don't know which post you are responding to.

But it sounds like things are fixed?????



CLamar wrote:

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

--

Dave Peterson

Well, it is almost. Right now it plots the seriescollection as if they are
seperate. I need the first one to be the X-values and the next one to be the
Y values. It is about the last revision to the code you posted. This
should be the last question, so if you can help it would be very much
appreciated

"Dave Peterson" wrote:

I don't know which post you are responding to.

But it sounds like things are fixed?????



CLamar wrote:

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

--

Dave Peterson

You're still past my knowledge of charts and graphs....

But could you use:

.SetSourceData Source:=Sheets("Datatable").Range("f1:g" & LastCellx), _
PlotBy:=xlColumns

instead of:
' .SeriesCollection.Add _
Source:=Worksheets("datatable").Range("f2:F" & LastCellx)
' .SeriesCollection.Add _
Source:=Worksheets("datatable").Range("g2:G" & LastCelly)


If no one jumps in with a correct answer, I'm still gonna suggest that you ask
in that other .charting newsgroup.

CLamar wrote:

Well, it is almost. Right now it plots the seriescollection as if they are
seperate. I need the first one to be the X-values and the next one to be the
Y values. It is about the last revision to the code you posted. This
should be the last question, so if you can help it would be very much
appreciated

"Dave Peterson" wrote:

I don't know which post you are responding to.

But it sounds like things are fixed?????



CLamar wrote:

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

--

Dave Peterson


--

Dave Peterson

I figured it out yesterday. I used:

Charts.Add
ActiveChart.Name = "VRH1H4"
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = _
WS.Range(WS.Cells(2, 6), WS.Cells(LastCellVRH1H4x, 6))
ActiveChart.SeriesCollection(1).Values = _
WS.Range(WS.Cells(2, 11), WS.Cells(LastCellVRH1H4y, 11))

Thanks for all your help, I must say you were a job saver



"Dave Peterson" wrote:

You're still past my knowledge of charts and graphs....

But could you use:

.SetSourceData Source:=Sheets("Datatable").Range("f1:g" & LastCellx), _
PlotBy:=xlColumns

instead of:
' .SeriesCollection.Add _
Source:=Worksheets("datatable").Range("f2:F" & LastCellx)
' .SeriesCollection.Add _
Source:=Worksheets("datatable").Range("g2:G" & LastCelly)


If no one jumps in with a correct answer, I'm still gonna suggest that you ask
in that other .charting newsgroup.

CLamar wrote:

Well, it is almost. Right now it plots the seriescollection as if they are
seperate. I need the first one to be the X-values and the next one to be the
Y values. It is about the last revision to the code you posted. This
should be the last question, so if you can help it would be very much
appreciated

"Dave Peterson" wrote:

I don't know which post you are responding to.

But it sounds like things are fixed?????



CLamar wrote:

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

--

Dave Peterson


--

Dave Peterson