From a column e.g "A"
How to determine the 1st cell and the last cell that contain a specific
word?

A B C
1
2 grp
3 grp
4
5 grp
6
7 hex
8 hex
9 hex
10 grp
11 grp

Hi
Assuming Your column A data has a heading

Sub tester()
Dim firstCell As Range, lastCell As Range
With Range("A:A")
Set firstCell = .Find("grp", SearchDirection:=xlNext)
Set lastCell = .Find("grp", SearchDirection:=xlPrevious)
End With
MsgBox "First Cell address is " & firstCell.Address
MsgBox "Last Cell address is " & lastCell.Address
End Sub

The first cell tested is A2 for firstcell and last row of data for
lastcell.
Check the find method Help if you want to specify different start
cells for searching.
regards
Paul

On Feb 22, 2:06*pm, "crapit" <biggerc...@yahoo.com> wrote:
> From a column e.g "A"
> How to determine the 1st cell and the last cell that contain a specific
> word?
>
> * * * *A * * * *B * * * *C
> 1
> 2 * grp
> 3 * grp
> 4
> 5 * grp
> 6
> 7 * hex
> 8 * hex
> 9 * hex
> 10 grp
> 11 grp

Select column A and run:

Sub find_um()
firstt = False
v = "grp"
For Each r In Selection
If v = r.Value Then
If firstt Then
lastone = r.Address
Else
lastone = r.Address
firstone = r.Address
firstt = True
End If
End If
Next
MsgBox (firstone & Chr(10) & lastone)
End Sub

--
Gary''s Student - gsnu2007d


"crapit" wrote:

> From a column e.g "A"
> How to determine the 1st cell and the last cell that contain a specific
> word?
>
> A B C
> 1
> 2 grp
> 3 grp
> 4
> 5 grp
> 6
> 7 hex
> 8 hex
> 9 hex
> 10 grp
> 11 grp
>
>
>

err, possible not to use macro? formula

<(E-Mail Removed)> wrote in message
news:52e57e3c-070f-44ba-9ee2-(E-Mail Removed)...
Hi
Assuming Your column A data has a heading

Sub tester()
Dim firstCell As Range, lastCell As Range
With Range("A:A")
Set firstCell = .Find("grp", SearchDirection:=xlNext)
Set lastCell = .Find("grp", SearchDirection:=xlPrevious)
End With
MsgBox "First Cell address is " & firstCell.Address
MsgBox "Last Cell address is " & lastCell.Address
End Sub

The first cell tested is A2 for firstcell and last row of data for
lastcell.
Check the find method Help if you want to specify different start
cells for searching.
regards
Paul

On Feb 22, 2:06 pm, "crapit" <biggerc...@yahoo.com> wrote:
> From a column e.g "A"
> How to determine the 1st cell and the last cell that contain a specific
> word?
>
> A B C
> 1
> 2 grp
> 3 grp
> 4
> 5 grp
> 6
> 7 hex
> 8 hex
> 9 hex
> 10 grp
> 11 grp

Array enter
=MIN(IF(A2:A21="a",ROW(A2:A21)))
=Max(IF(A2:A21="a",ROW(A2:A21)))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"crapit" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> From a column e.g "A"
> How to determine the 1st cell and the last cell that contain a specific
> word?
>
> A B C
> 1
> 2 grp
> 3 grp
> 4
> 5 grp
> 6
> 7 hex
> 8 hex
> 9 hex
> 10 grp
> 11 grp
>
>

Hi
Post the question in

microsoft.public.excel.worksheetfunctions

to avoid disappointment!
Paul

On Feb 22, 2:31*pm, "crapit" <biggerc...@yahoo.com> wrote:
> err, possible not to use macro? formula
>
> <paul.robin...@it-tallaght.ie> wrote in message
>
> news:52e57e3c-070f-44ba-9ee2-(E-Mail Removed)...
> Hi
> Assuming Your column A data has a heading
>
> Sub tester()
> Dim firstCell As Range, lastCell As Range
> *With Range("A:A")
> * *Set firstCell = .Find("grp", SearchDirection:=xlNext)
> * *Set lastCell = .Find("grp", SearchDirection:=xlPrevious)
> End With
> * * MsgBox "First Cell address is " & firstCell.Address
> * * MsgBox "Last Cell address is " & lastCell.Address
> End Sub
>
> The first cell tested is A2 for firstcell and last row of data for
> lastcell.
> Check the find method Help if you want to specify different start
> cells for searching.
> regards
> Paul
>
> On Feb 22, 2:06 pm, "crapit" <biggerc...@yahoo.com> wrote:
>
>
>
> > From a column e.g "A"
> > How to determine the 1st cell and the last cell that contain a specific
> > word?
>
> > A B C
> > 1
> > 2 grp
> > 3 grp
> > 4
> > 5 grp
> > 6
> > 7 hex
> > 8 hex
> > 9 hex
> > 10 grp
> > 11 grp- Hide quoted text -
>
> - Show quoted text -

do i need to put the array across the same row
"Don Guillett" <(E-Mail Removed)> wrote in message
news:eW7tu%(E-Mail Removed)...
> Array enter
> =MIN(IF(A2:A21="a",ROW(A2:A21)))
> =Max(IF(A2:A21="a",ROW(A2:A21)))
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "crapit" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> From a column e.g "A"
>> How to determine the 1st cell and the last cell that contain a specific
>> word?
>>
>> A B C
>> 1
>> 2 grp
>> 3 grp
>> 4
>> 5 grp
>> 6
>> 7 hex
>> 8 hex
>> 9 hex
>> 10 grp
>> 11 grp
>>
>>
>

This is an Array formula that could be put anywhere on the worksheet. It
refers to the indicated range.
Instead of entering/editing with just ENTER, hold down the ctrl & shift keys
and touch enter at the same time.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"crapit" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> do i need to put the array across the same row
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:eW7tu%(E-Mail Removed)...
>> Array enter
>> =MIN(IF(A2:A21="a",ROW(A2:A21)))
>> =Max(IF(A2:A21="a",ROW(A2:A21)))
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "crapit" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> From a column e.g "A"
>>> How to determine the 1st cell and the last cell that contain a specific
>>> word?
>>>
>>> A B C
>>> 1
>>> 2 grp
>>> 3 grp
>>> 4
>>> 5 grp
>>> 6
>>> 7 hex
>>> 8 hex
>>> 9 hex
>>> 10 grp
>>> 11 grp
>>>
>>>
>>
>
>

If the reference is in another worksheet, can I just change to =MIN(IF("abc
num"(A2:A21)="a",ROW("abc num"(A2:A21))))
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This is an Array formula that could be put anywhere on the worksheet. It
> refers to the indicated range.
> Instead of entering/editing with just ENTER, hold down the ctrl & shift
> keys and touch enter at the same time.
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "crapit" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> do i need to put the array across the same row
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:eW7tu%(E-Mail Removed)...
>>> Array enter
>>> =MIN(IF(A2:A21="a",ROW(A2:A21)))
>>> =Max(IF(A2:A21="a",ROW(A2:A21)))
>>> --
>>> Don Guillett
>>> Microsoft MVP Excel
>>> SalesAid Software
>>> (E-Mail Removed)
>>> "crapit" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> From a column e.g "A"
>>>> How to determine the 1st cell and the last cell that contain a specific
>>>> word?
>>>>
>>>> A B C
>>>> 1
>>>> 2 grp
>>>> 3 grp
>>>> 4
>>>> 5 grp
>>>> 6
>>>> 7 hex
>>>> 8 hex
>>>> 9 hex
>>>> 10 grp
>>>> 11 grp
>>>>
>>>>
>>>
>>
>>
>

Pls ignore the worksheet Qs. If I need to use the min and max in a range
e.g sum('abc ge'!a3:a33), where a3 is the value in MIN array and a33 in MAX
array,is that possible

"crapit" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> If the reference is in another worksheet, can I just change to
> =MIN(IF("abc num"(A2:A21)="a",ROW("abc num"(A2:A21))))
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> This is an Array formula that could be put anywhere on the worksheet. It
>> refers to the indicated range.
>> Instead of entering/editing with just ENTER, hold down the ctrl & shift
>> keys and touch enter at the same time.
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "crapit" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> do i need to put the array across the same row
>>> "Don Guillett" <(E-Mail Removed)> wrote in message
>>> news:eW7tu%(E-Mail Removed)...
>>>> Array enter
>>>> =MIN(IF(A2:A21="a",ROW(A2:A21)))
>>>> =Max(IF(A2:A21="a",ROW(A2:A21)))
>>>> --
>>>> Don Guillett
>>>> Microsoft MVP Excel
>>>> SalesAid Software
>>>> (E-Mail Removed)
>>>> "crapit" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> From a column e.g "A"
>>>>> How to determine the 1st cell and the last cell that contain a
>>>>> specific word?
>>>>>
>>>>> A B C
>>>>> 1
>>>>> 2 grp
>>>>> 3 grp
>>>>> 4
>>>>> 5 grp
>>>>> 6
>>>>> 7 hex
>>>>> 8 hex
>>>>> 9 hex
>>>>> 10 grp
>>>>> 11 grp
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>
>