extract names based on conditions
 

In Excel I have a long list of names. I want to write a macro to extract all
those names that are a certain age into a new list

eg if my spreadsheet shows

A B
1 Name Age
2 Adam 40
3 Eve 28
4 Frank 40
5 John 43

I want to create the list of people aged 40 ie Adam, Frank in 2 new cells

Any ideas?

extract names based on conditions
 

Hi,

Right click your sheet tab, view code and paste this in and run it.

Sub stantial()
Dim myrange As Range, copyrange As Range
Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A2:A" & Lastrow)
Set copyrange = Range("A1")
For Each c In myrange
If c.Offset(, 1).Value = 40 Then
Set copyrange = Union(copyrange, c)
End If
Next
copyrange.Copy
Range("C1").PasteSpecial
End Sub


Mike

"Bobak" wrote:

In Excel I have a long list of names. I want to write a macro to extract all
those names that are a certain age into a new list

eg if my spreadsheet shows

A B
1 Name Age
2 Adam 40
3 Eve 28
4 Frank 40
5 John 43

I want to create the list of people aged 40 ie Adam, Frank in 2 new cells

Any ideas?

extract names based on conditions
 

I would just use DataAutofilter on the age column and copy the visible
cells to a new spot.


Gord Dibben MS Excel MVP

On Wed, 3 Sep 2008 07:38:01 -0700, Bobak
wrote:

In Excel I have a long list of names. I want to write a macro to extract all
those names that are a certain age into a new list

eg if my spreadsheet shows

A B
1 Name Age
2 Adam 40
3 Eve 28
4 Frank 40
5 John 43

I want to create the list of people aged 40 ie Adam, Frank in 2 new cells

Any ideas?