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" 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

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

"crapit" wrote in message
...
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" wrote:
err, possible not to use macro? formula

wrote in message

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

"crapit" wrote in message
...
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

"crapit" wrote in message
...
do i need to put the array across the same row
"Don Guillett" wrote in message
...
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

"crapit" wrote in message
...
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" wrote in message
...
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

"crapit" wrote in message
...
do i need to put the array across the same row
"Don Guillett" wrote in message
...
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

"crapit" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"crapit" wrote in message
...
do i need to put the array across the same row
"Don Guillett" wrote in message
...
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

"crapit" wrote in message
...
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

More info. Your OP asked to determine TEXT in a column. In the future,
ALWAYS state your requirement in the FIRST post so time is not wasted. I
don't understand what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"crapit" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

"crapit" wrote in message
...
do i need to put the array across the same row
"Don Guillett" wrote in message
...
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

"crapit" wrote in message
...
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