Variance in an array

[DejaVu]

In one column, there are values like:
Andy
Bob
Cindy
Dan
Ed
etc...

I'm wanting some sort of procedure to go through this column, find how many variations there are and store that number as a variable. Also, I'd like to be able to store all of the string values into an array to be used later. For example; if the above was my data, there would be five variations (Andy, Bob, Cindy, Dan, Ed).
Then use that array to take all the rows that have "Andy" in them and move them to a sheet that will be created named "Andy". And loop through each name.

I've worked on a somewhat similar project before, but I'm having trouble figuring this one out. Here is the code I used before that would look for one word and move all those rows to another column. This is code that was previously suggested to me on this message board (Thanks Jim Thomlinson!). Link

Sub CopyCells()
Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range
Dim rngToSearch As Range, rngToPaste As Range
Dim wksToSearch As Worksheet, wksToPaste As Worksheet

Set wksToSearch = Sheets("Rough")
Set wksToPaste = Worksheets.Add(, Sheets.Count)
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)

If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow, rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
rngFoundCells.Delete
End If

End Sub

TIA for all help!

DejaVu

[STEVE BELL]

You can do this with Advance filters.
To get the code just record macro(s) while you perform it,
than edit the code to simplify it.

Do an advanced filter on the name column and put the results in another
place using Unique
Records. Than just count the number (you can erase this at anytime through
code.)

But you can use that list to perform Advanced Filter on the database and
hide all but one of the names. You can either have the filter put the data
elsewhere or filter it in place. Than use Edit > Goto > Special > visible
cells only, copy and paste.

And you should be able to do all of this in code without any selecting...

--
steveB

Remove "AYN" from email to respond
"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> In one column, there are values like:
> Andy
> Bob
> Cindy
> Dan
> Ed
> etc...
>
> I'm wanting some sort of procedure to go through this column, find how
> many variations there are and store that number as a variable. Also,
> I'd like to be able to store all of the string values into an array to
> be used later. For example; if the above was my data, there would be
> five variations (Andy, Bob, Cindy, Dan, Ed).
> Then use that array to take all the rows that have "Andy" in them and
> move them to a sheet that will be created named "Andy". And loop
> through each name.
>
> I've worked on a somewhat similar project before, but I'm having
> trouble figuring this one out. Here is the code I used before that
> would look for one word and move all those rows to another column.
> This is code that was previously suggested to me on this message board
> (Thanks Jim Thomlinson!). 'Link'
> (http://www.excelforum.com/showthread.php?t=385313)
>
> Sub CopyCells()
> Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range
> Dim rngToSearch As Range, rngToPaste As Range
> Dim wksToSearch As Worksheet, wksToPaste As Worksheet
>
> Set wksToSearch = Sheets("Rough")
> Set wksToPaste = Worksheets.Add(, Sheets.Count)
> Set rngToSearch = wksToSearch.Cells
> Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
> Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)
>
> If rngCurrent Is Nothing Then
> MsgBox strWordToFind & " was not found"
> Else
> Set rngFirst = rngCurrent
> Set rngFoundCells = rngCurrent.EntireRow
> Do
> Set rngFoundCells = Union(rngCurrent.EntireRow,
> rngFoundCells)
> Set rngCurrent = rngToSearch.FindNext(rngCurrent)
> Loop Until rngFirst.Address = rngCurrent.Address
> rngFoundCells.Copy rngToPaste
> rngFoundCells.Delete
> End If
>
> End Sub
>
> TIA for all help!
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
> http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=389078
>

[Tom Ogilvy]

Ron de Bruin has already written sample code for this:

http://www.rondebruin.nl/copy5.htm#all

--
Regards,
Tom Ogilvy


"DejaVu" <[email protected]> wrote in
message news:[email protected]...
>
> In one column, there are values like:
> Andy
> Bob
> Cindy
> Dan
> Ed
> etc...
>
> I'm wanting some sort of procedure to go through this column, find how
> many variations there are and store that number as a variable. Also,
> I'd like to be able to store all of the string values into an array to
> be used later. For example; if the above was my data, there would be
> five variations (Andy, Bob, Cindy, Dan, Ed).
> Then use that array to take all the rows that have "Andy" in them and
> move them to a sheet that will be created named "Andy". And loop
> through each name.
>
> I've worked on a somewhat similar project before, but I'm having
> trouble figuring this one out. Here is the code I used before that
> would look for one word and move all those rows to another column.
> This is code that was previously suggested to me on this message board
> (Thanks Jim Thomlinson!). 'Link'
> (http://www.excelforum.com/showthread.php?t=385313)
>
> Sub CopyCells()
> Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range
> Dim rngToSearch As Range, rngToPaste As Range
> Dim wksToSearch As Worksheet, wksToPaste As Worksheet
>
> Set wksToSearch = Sheets("Rough")
> Set wksToPaste = Worksheets.Add(, Sheets.Count)
> Set rngToSearch = wksToSearch.Cells
> Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
> Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)
>
> If rngCurrent Is Nothing Then
> MsgBox strWordToFind & " was not found"
> Else
> Set rngFirst = rngCurrent
> Set rngFoundCells = rngCurrent.EntireRow
> Do
> Set rngFoundCells = Union(rngCurrent.EntireRow,
> rngFoundCells)
> Set rngCurrent = rngToSearch.FindNext(rngCurrent)
> Loop Until rngFirst.Address = rngCurrent.Address
> rngFoundCells.Copy rngToPaste
> rngFoundCells.Delete
> End If
>
> End Sub
>
> TIA for all help!
>
> DejaVu
>
>
> --
> DejaVu
> ------------------------------------------------------------------------
> DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
> View this thread: http://www.excelforum.com/showthread...hreadid=389078
>