Deleting Duplicate items in a ListBox

C

CLamar

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
 
C

CLamar

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
 
D

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

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

CLamar

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"
 
D

Dave Peterson

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

John has an "on error resume next" line that you dropped.
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"
Click to expand...
 
D

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



How would you locate the last cell in a column using the R1C1 method?
Click to expand...
 
C

CLamar

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
 
D

Dave Peterson

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?
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
Click to expand...
 
C

CLamar

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
"
 
D

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?
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
"
Click to expand...
 
C

CLamar

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

Dave Peterson

Which line?

You'll have to post more of the code.
It is now giving me the error "Method of rows object _Global failed" and it
is not in the help section of VBA
Click to expand...
 
C

CLamar

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
 
D

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

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
Click to expand...
 
C

CLamar

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
 
D

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.

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
Click to expand...
 
C

CLamar

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