Selection range to last non empty cell
 

Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

Selection range to last non empty cell
 

Maybe...

Option Explicit
Sub testme2()

Dim FirstCell As Range
Dim SecondCell As Range
Dim RngToSearch As Range

With ActiveSheet
Set FirstCell = .Range("a3")
Set RngToSearch = .Range("a3", .Cells(.Rows.Count, "A").End(xlUp))

With RngToSearch
Set SecondCell = .Cells.Find(what:="Team", _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If SecondCell Is Nothing Then
'not found
Set SecondCell = FirstCell
End If
End With

.Range(FirstCell, SecondCell).Select
End With

End Sub




Jaan wrote:

Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

--

Dave Peterson

Selection range to last non empty cell
 

Try this

Sub test()
Dim rng As Range
With Sheets("Sheet1").Range("A3:A" & Rows.Count)
Set rng = .Find(What:="Team", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Sheets("Sheet1").Range("A3:A" & rng.Row).Select
Else
MsgBox "Nothing found"
End If
End With
End Sub


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


"Jaan" wrote in message ...
Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

Selection range to last non empty cell
 

Hi

Thanks. This working.



"Dave Peterson" kirjutas:

Maybe...

Option Explicit
Sub testme2()

Dim FirstCell As Range
Dim SecondCell As Range
Dim RngToSearch As Range

With ActiveSheet
Set FirstCell = .Range("a3")
Set RngToSearch = .Range("a3", .Cells(.Rows.Count, "A").End(xlUp))

With RngToSearch
Set SecondCell = .Cells.Find(what:="Team", _
after:=.Cells(1), lookat:=xlWhole, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)

If SecondCell Is Nothing Then
'not found
Set SecondCell = FirstCell
End If
End With

.Range(FirstCell, SecondCell).Select
End With

End Sub




Jaan wrote:

Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?

--

Dave Peterson

Selection range to last non empty cell
 

Hi

and this working
Thanks!

"Ron de Bruin" kirjutas:

Try this

Sub test()
Dim rng As Range
With Sheets("Sheet1").Range("A3:A" & Rows.Count)
Set rng = .Find(What:="Team", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Sheets("Sheet1").Range("A3:A" & rng.Row).Select
Else
MsgBox "Nothing found"
End If
End With
End Sub


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


"Jaan" wrote in message ...
Does anyone know how I could write a macro to select range from A3 to value
"TEAM"?