I have a unique problem I need help with. I have account numbers in column
A, the range will vary at times. Finding the last cell automatically would
be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts, the trick,
I want to exclude the 2nd, 3rd, 4th, etc., matching records and display the
count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5 Records
were Selected'

Thanks in advance for the help.

MyCount = WorksheetFunction.Count("A : A")

Msgbox MyCount

or in one line

MsgBox WorksheetFunction.CountA(Range("A : A"))



"Donnie Stone" wrote in message
...
I have a unique problem I need help with. I have account numbers in
column
A, the range will vary at times. Finding the last cell automatically
would
be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts, the
trick,
I want to exclude the 2nd, 3rd, 4th, etc., matching records and display
the
count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5 Records
were Selected'

Thanks in advance for the help.

This is based on John Walkenbach's code at:
http://j-walk.com/ss/excel/tips/tip47.htm

Sub RemoveDuplicates()
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

' The items are in A1:A105
Set AllCells = Range("A:A").SpecialCells(xlCellTypeConstants)

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

MsgBox NoDupes.Count & " records were selected"

End Sub

--
Dianne

In ,
Donnie Stone typed:
I have a unique problem I need help with. I have account numbers in
column A, the range will vary at times. Finding the last cell
automatically would be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts, the
trick, I want to exclude the 2nd, 3rd, 4th, etc., matching records
and display the count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5
Records were Selected'

Dianne,

Thanks for the help, this works great. How can I change the default Title
on the MsgBox?

Regards,
Donnie

"Dianne" wrote in message
...
This is based on John Walkenbach's code at:
http://j-walk.com/ss/excel/tips/tip47.htm

Sub RemoveDuplicates()
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

' The items are in A1:A105
Set AllCells = Range("A:A").SpecialCells(xlCellTypeConstants)

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

MsgBox NoDupes.Count & " records were selected"

End Sub

--
Dianne

In ,
Donnie Stone typed:
I have a unique problem I need help with. I have account numbers in
column A, the range will vary at times. Finding the last cell
automatically would be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts, the
trick, I want to exclude the 2nd, 3rd, 4th, etc., matching records
and display the count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5
Records were Selected'

From the VBA help file, here's the syntax for MsgBox:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

So to change the title:

MsgBox NoDupes.Count _
& " records were selected", _
vbInformation, "My Title"

There are a number of buttons and icons you can use on the message
box -- above I have used the vbInformation icon. You can even change
what buttons appear on the message box -- instead of OK, you can show
Yes/No or Abort/Retry/Cancel.

Check the Help for more options.

--
HTH,
Dianne

In ,
Donnie Stone typed:
Dianne,

Thanks for the help, this works great. How can I change the default
Title on the MsgBox?

Regards,
Donnie

"Dianne" wrote in message
...
This is based on John Walkenbach's code at:
http://j-walk.com/ss/excel/tips/tip47.htm

Sub RemoveDuplicates()
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

' The items are in A1:A105
Set AllCells = Range("A:A").SpecialCells(xlCellTypeConstants)

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a
string Next Cell

MsgBox NoDupes.Count & " records were selected"

End Sub

--
Dianne

In ,
Donnie Stone typed:
I have a unique problem I need help with. I have account numbers in
column A, the range will vary at times. Finding the last cell
automatically would be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts,
the trick, I want to exclude the 2nd, 3rd, 4th, etc., matching
records and display the count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5
Records were Selected'

Thanks for the help.

"Dianne" wrote in message
...
From the VBA help file, here's the syntax for MsgBox:
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

So to change the title:

MsgBox NoDupes.Count _
& " records were selected", _
vbInformation, "My Title"

There are a number of buttons and icons you can use on the message
box -- above I have used the vbInformation icon. You can even change
what buttons appear on the message box -- instead of OK, you can show
Yes/No or Abort/Retry/Cancel.

Check the Help for more options.

--
HTH,
Dianne

In ,
Donnie Stone typed:
Dianne,

Thanks for the help, this works great. How can I change the default
Title on the MsgBox?

Regards,
Donnie

"Dianne" wrote in message
...
This is based on John Walkenbach's code at:
http://j-walk.com/ss/excel/tips/tip47.htm

Sub RemoveDuplicates()
' This example is based on a tip by J.G. Hussey,
' published in "Visual Basic Programmer's Journal"

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

' The items are in A1:A105
Set AllCells = Range("A:A").SpecialCells(xlCellTypeConstants)

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a
string Next Cell

MsgBox NoDupes.Count & " records were selected"

End Sub

--
Dianne

In ,
Donnie Stone typed:
I have a unique problem I need help with. I have account numbers in
column A, the range will vary at times. Finding the last cell
automatically would be a big plus.

A
1 103456
2 34567
3 589123
4 103456
5 761234
6 34568
7 34567

I would like to search column A, counting the number of accounts,
the trick, I want to exclude the 2nd, 3rd, 4th, etc., matching
records and display the count in a MsgBox.

Based on the example above, I would like a MsgBox to display: '5
Records were Selected'