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?

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?

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?