Hi Tony

1)

You can use this function for finding the last row with data on the worksheet

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Use this in your code then

Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1


If you can check one column then use this
Lr = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row


2)

This will select every cell with a C in row 5 of the active sheet

Sub UnionExample()
Dim col As Integer
Dim rng As Range

With ActiveSheet
For col = 1 To 256
If IsError(.Cells(5, col).Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(5, col).Value = "C" Then
If rng Is Nothing Then
Set rng = .Cells(5, col)
Else
Set rng = Application.Union(rng, .Cells(5, col))
End If
End If
Next
End With

If Not rng Is Nothing Then rng.Select

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tony Wainwright" wrote in message ...
Hi guys

In my spreadsheet I have a list of names starting at G4 and continuning along row 4. Underneath each name in row 5 is which
group they are in e.g. A, B, C, etc. Is there a way that I can programtically:-
1) Check which is the last row of the spreadsheet
2) Select everybody who is in group C

Thanks
Tony