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

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

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
>

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

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
>