View Full Version : Solved: Extend range of cells from active cells
LLEW1
05-13-2009, 08:11 PM
Hi,
a simple one
I want to extend the range of cells from the active cell - what is the code for this?
Simon Lloyd
05-13-2009, 09:09 PM
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
05-13-2009, 09:14 PM
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
05-14-2009, 09:30 AM
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
05-14-2009, 03:47 PM
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
05-14-2009, 04:33 PM
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
05-14-2009, 04:53 PM
Simon,
Perfect - thankyou sir..
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.