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

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" <[email protected]> wrote in message news:[email protected]...
> 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
>

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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
>>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

the constant is xlByColumns, not xlByCols

SearchOrder:=xlByCols
should be
SearchOrder:=xlByColumns

--
Regards,
Tom Ogilvy

"Tony Wainwright" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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
> >>>
> >>
> >>
> >
> >
>
>

Stupendous
Thanks guys - everything working as it should

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> the constant is xlByColumns, not xlByCols
>
> SearchOrder:=xlByCols
> should be
> SearchOrder:=xlByColumns
>
> --
> Regards,
> Tom Ogilvy
>
> "Tony Wainwright" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> 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
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message news:[email protected]...
> Stupendous
> Thanks guys - everything working as it should
>
> "Tom Ogilvy" <[email protected]> wrote in message news:[email protected]...
>> the constant is xlByColumns, not xlByCols
>>
>> SearchOrder:=xlByCols
>> should be
>> SearchOrder:=xlByColumns
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Tony Wainwright" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >>> 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
>>> >>>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>