Selecting a range.

[Tom Walat]

Using Excel 2000
The following code selects a range after finding a match for
"2003" in the A column. (The number of rows between the 2003s can
vary.)
The problem occurs at the line "Range("A898:E911").Select"
Because the range can be variable, I need the macro equivalent
of Shift+Up Arrow, but using the macro recorder I get a specific
range. (I want the range from the first 2003 to the row before the
next 2003 selected.)
I'd appreciate any help.
- Tom

Cells.Find(What:="2003", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("A898:E911").Select
Selection.Copy

 

[Bob Phillips]

Hi Tom,

Here is a shot
Dim oCell As Range
Dim iStart As Long
Dim iEnd As Long
Dim iCol As Long
Dim cColumns As Long

On Error Resume Next
Set oCell = Cells.Find(What:="2003")
If Not oCell Is Nothing Then
iStart = oCell.Row
iCol = oCell.Column
cColumns = Cells(iStart, Columns.Count).End(xlToLeft).Column - iCol
+ 1
Set oCell = Cells.FindNext(oCell)
If Not oCell Is Nothing Then
iEnd = oCell.Row
Range(Cells(iStart, iCol), Cells(iEnd, iCol)) _
.Resize(, cColumns).Select
Selection.Copy
End If
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Walat]

Bob:
Thanks.
I was able to get it working with your coding.
- Tom