Last row in spreadseet
 

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

Last row in spreadseet
 

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

Last row in spreadseet
 

Oops

My example will give you the first empty row
remove +1 or Offset(1, 0). if you want to have the last row number



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


"Ron de Bruin" wrote in message ...
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

Last row in spreadseet
 

Sorry Ron
I'm trying to find the last column in the spreadsheet as the names start at
G4 and continue along row 4. I've tried to adapt your function as follows.
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByCols, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
However when I try this, regardles of the value of After, LastCol only
returns a value of 1. Any ideas where I'm going wrong?

"Ron de Bruin" wrote in message
...
Oops

My example will give you the first empty row
remove +1 or Offset(1, 0). if you want to have the last row number



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


"Ron de Bruin" wrote in message
...
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

Last row in spreadseet
 

the constant is xlByColumns, not xlByCols

SearchOrder:=xlByCols
should be
SearchOrder:=xlByColumns

--
Regards,
Tom Ogilvy

"Tony Wainwright" wrote in message
...
Sorry Ron
I'm trying to find the last column in the spreadsheet as the names start
at
G4 and continue along row 4. I've tried to adapt your function as follows.
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByCols, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
However when I try this, regardles of the value of After, LastCol only
returns a value of 1. Any ideas where I'm going wrong?

"Ron de Bruin" wrote in message
...
Oops

My example will give you the first empty row
remove +1 or Offset(1, 0). if you want to have the last row number



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


"Ron de Bruin" wrote in message
...
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

Last row in spreadseet
 

Stupendous
Thanks guys - everything working as it should

"Tom Ogilvy" wrote in message
...
the constant is xlByColumns, not xlByCols

SearchOrder:=xlByCols
should be
SearchOrder:=xlByColumns

--
Regards,
Tom Ogilvy

"Tony Wainwright" wrote in message
...
Sorry Ron
I'm trying to find the last column in the spreadsheet as the names start
at
G4 and continue along row 4. I've tried to adapt your function as
follows.
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByCols, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
However when I try this, regardles of the value of After, LastCol only
returns a value of 1. Any ideas where I'm going wrong?

"Ron de Bruin" wrote in message
...
Oops

My example will give you the first empty row
remove +1 or Offset(1, 0). if you want to have the last row number



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


"Ron de Bruin" wrote in message
...
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

Last row in spreadseet
 

Hi Tony

I see you got it working.

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
--
Regards Ron de Bruin
http://www.rondebruin.nl


"Tony Wainwright" wrote in message ...
Stupendous
Thanks guys - everything working as it should

"Tom Ogilvy" wrote in message ...
the constant is xlByColumns, not xlByCols

SearchOrder:=xlByCols
should be
SearchOrder:=xlByColumns

--
Regards,
Tom Ogilvy

"Tony Wainwright" wrote in message
...
Sorry Ron
I'm trying to find the last column in the spreadsheet as the names start
at
G4 and continue along row 4. I've tried to adapt your function as follows.
Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByCols, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
However when I try this, regardles of the value of After, LastCol only
returns a value of 1. Any ideas where I'm going wrong?

"Ron de Bruin" wrote in message
...
Oops

My example will give you the first empty row
remove +1 or Offset(1, 0). if you want to have the last row number



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


"Ron de Bruin" wrote in message
...
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