copying unique values to different cell
 

I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941

copying unique values to different cell
 

wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25.

And you know that there are exactly 23 unique values???

--

Vasant

copying unique values to different cell
 

I don't know if this is exactly what you need but you could try
something like this:

Sub UniqueValues()
For Each Cell In Sheets(1).Range("O1:O100")
If Application.WorksheetFunction.CountIf(Range("O1:O6 5536"),
Cell) = 1 Then
Counter = Counter + 1
Sheets(2).Range("C" & Counter + 2) = Cell
End If
Next Cell
End Sub

copying unique values to different cell
 

Hi R4ranjith,

If your intent is to copy unique values, rather than values which only occur
once, then try:

'=======================.
Sub Tester01()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim srcRng As Range
Dim destRng As Range

Set sh1 = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set sh2 = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set srcRng = sh1.Range("O1:O200") '<<==== CHANGE
Set destRng = sh2.Range("C3") '<<==== CHANGE

' If the source and destination ranges are _
' not on the same sheet, the advanced filter _
' must be invoked from the destination sheet, so:
sh2.Activate

srcRng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=destRng, _
Unique:=True

End Sub
''<<'=======================

This will delete any existing data below the destination cell C3.

If this constitutes a problem, then you can replace:

Set destRng = sh2.Range("C3")

with

Set destRng = sh2.Range("C3C25")

where the range C3:C25 is sufficient to accomodate all potential unique
values in the source range. If this range is too small, the macro will
produce a warning message.


---
Regards,
Norman



wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941

copying unique values to different cell
 

Hey Norman,

Thank you for that reply. I did exactly the same and it worked
perfectly fine.

Sorry I could not respond quickly :)

Regards,
Ranjith

Norman Jones wrote:
Hi R4ranjith,

If your intent is to copy unique values, rather than values which only occur
once, then try:

'=======================.
Sub Tester01()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim srcRng As Range
Dim destRng As Range

Set sh1 = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE
Set sh2 = ThisWorkbook.Sheets("Sheet2") '<<==== CHANGE

Set srcRng = sh1.Range("O1:O200") '<<==== CHANGE
Set destRng = sh2.Range("C3") '<<==== CHANGE

' If the source and destination ranges are _
' not on the same sheet, the advanced filter _
' must be invoked from the destination sheet, so:
sh2.Activate

srcRng.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=destRng, _
Unique:=True

End Sub
''<<'=======================

This will delete any existing data below the destination cell C3.

If this constitutes a problem, then you can replace:

Set destRng = sh2.Range("C3")

with

Set destRng = sh2.Range("C3C25")

where the range C3:C25 is sufficient to accomodate all potential unique
values in the source range. If this range is too small, the macro will
produce a warning message.


---
Regards,
Norman



wrote in message
oups.com...
I have a coloumn(O:O) in sheet1 that has many different values. I want
to copy the unique values to sheet 2 cell C3 to C25. How can I do that
using a VBA codec. I am very new to VB that is why I am asking to help
me.

I saw one which does this but the problem is that it asks for a prompt.
I dont want the prompt but it should directly paste to the cell
refference.

The link is http://www.freevbcode.com/ShowCode.asp?ID=4941