I use Cells.Find to locate a column. How do I select a certain range
within the column of the found range?

'-------------------Here is the dumbed down version of the code:
Set testrng = Cells.Find(What:="Highbridge Selections",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not testrng Is Nothing Then
Selection.copy


I know this selects the whole column:

testrng.EntireColumn.Copy

I want to be able to do this:

Range(testrng.column & "65536").end(xlup).
(offset(1,0).copy '(or paste)

Hi Kigol,

I am not really sure what range you are trying to copy or paste. that is a
range or only one cell. Below is a couple of examples of copy which might
help. If not what you want then can you describe what range in relation to
the found cell that you are trying to copy.

There is really nothing wrong with using testRng.Column but I like to assign
the number to a variable because I find the code easier to follow. I also
like to use line breaks in long code like find and tabulate the parameters
because I think that is also easier to follow; especially if the code is not
finding what I expect it to find and I need to check the parameters which
have been set.


Sub test_select()
Dim testRng As Range
Dim lngColumn As Long

Set testRng = Cells.Find(What:="Highbridge Selections", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

lngColumn = testRng.Column

'The following line copies the cells from 1 below
'the found cell to the last cell in the column
'that contains data.

Range(testRng.Offset(1, 0), Cells(65536, lngColumn).End(xlUp)).Copy

'The following copies one cell only below the found cell
testRng.Offset(1, 0).Copy


'The following is same as first example but uses
'the total number of rows in the worksheet in lieu of 65536
Range(testRng.Offset(1, 0), Cells(Rows.Count, lngColumn).End(xlUp)).Copy

End Sub

--
Regards,

OssieMac


"Kigol" wrote:

I use Cells.Find to locate a column. How do I select a certain range
within the column of the found range?

'-------------------Here is the dumbed down version of the code:
Set testrng = Cells.Find(What:="Highbridge Selections",
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not testrng Is Nothing Then
Selection.copy


I know this selects the whole column:

testrng.EntireColumn.Copy

I want to be able to do this:

Range(testrng.column & "65536").end(xlup).
(offset(1,0).copy '(or paste)