Solved: Extend range of cells from active cells

[LLEW1]

Hi,

a simple one

I want to extend the range of cells from the active cell - what is the code for this?

[Simon Lloyd]

Perhaps:
Dim LstCol as Long, LstColRow As Long
LstCol = Range("IV" & ActiveCell.Row).End(xlToLeft).Column
LstColRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(LstColRow, LstCol)).Select

[LLEW1]

Thanks Simon

works well,

I also have a need to limit the extended range to the rows in A1 that match the found search criteria, say "Code1" which appear in below the found cell then including cells 7 columns to the right.

[Simon Lloyd]

Thanks Simon

works well,

I also have a need to limit the extended range to the rows in A1 that match the found search criteria, say "Code1" which appear in below the found cell then including cells 7 columns to the right.I'm sorry but just like your first post that made no sense at all - the only "rows" in A1 will be 1!, how about you attach a workbook with your before and after look with a better explanation.

[LLEW1]

Hi Simon,
sorry for being vague and thank you for persisting; I was really on the 'fly' yesterday and under pressure to produce. What you sent saved me...thanks.

Ive actually figured a method (in part) to limit the range of cells to choose, based on the active cell location, using your code - so as below:

'Select range from active cell postion
Dim LstCol As Long, LstColRow As Long
LstCol = Range("IV" & ActiveCell.Row).End(xlToLeft).Column
LstColRow = Cells.Find(What:=Name, After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(LstColRow, LstCol)).Select


Where the rows below the active cell to include in the selection are based on a variable
Name on Sheet1.

While this has succeeded, Im still not sure on how to control the selection of columns to the right of the active cell; so any thoughts with that would be great.

Have attached now a simple reprsentation - the Active cell being A8 on Sheet 2 - and explained more on the sheet.

[Simon Lloyd]

Dim LstCol As Long, LstColRow As Long
Dim nCount As Long
LstCol = 3 'change to suit number of columns
LstColRow = Cells.Find(What:="Shop2", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(LstColRow, LstCol)).Select

[LLEW1]

Simon,

Perfect - thankyou sir..