Select Range in VBA relative to another cell
 

HOpefully quick question.

I have code that looks to see if a block of cells meets my criteria.
If it does, then I need to perform some operations on that block.
Everything is in 1 column (Col A) if that helps.

What my code does it to first identify a block of rows in Column
where 6 consecutive rows are non-blank. I need to copy the bottom fiv
rows of this block and transpose them in column B, on the same row tha
starts the block.

When I hard code cell references, it works fine. My problem is that
can not hard code cell references for the entire procedure, becuaus
rows will be deleted. My long winded question is as follows:

How can I set the Range(Cells(8,1),Cells(12,1).select to be define
dynamically relative to the active cell (the first cell of the block)?

Thanks in advance,

Broc

--
Btiber
-----------------------------------------------------------------------
Btibert's Profile: http://www.excelforum.com/member.php...nfo&userid=855
View this thread: http://www.excelforum.com/showthread.php?threadid=52975

Select Range in VBA relative to another cell
 

Brock

I think Resize is what you want.

ActiveCell.Resize(5)

will refer to the active cell and the four cells below it.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.coms

Btibert wrote:
HOpefully quick question.

I have code that looks to see if a block of cells meets my criteria.
If it does, then I need to perform some operations on that block.
Everything is in 1 column (Col A) if that helps.

What my code does it to first identify a block of rows in Column A
where 6 consecutive rows are non-blank. I need to copy the bottom
five rows of this block and transpose them in column B, on the same
row that starts the block.

When I hard code cell references, it works fine. My problem is that I
can not hard code cell references for the entire procedure, becuause
rows will be deleted. My long winded question is as follows:

How can I set the Range(Cells(8,1),Cells(12,1).select to be defined
dynamically relative to the active cell (the first cell of the block)?

Thanks in advance,

Brock

Select Range in VBA relative to another cell
 

thats great! While by no means am I an expert at VBA, I have never
heard of that. Thanks alot! With a little tweak, I am wel on my way.
Thanks again!


--
Btibert
------------------------------------------------------------------------
Btibert's Profile: http://www.excelforum.com/member.php...fo&userid=8559
View this thread: http://www.excelforum.com/showthread...hreadid=529755