Select Range in VBA relative to another cell

  • Thread starter Thread starter Btibert
  • Start date Start date
B

Btibert

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
 
Brock

I think Resize is what you want.

ActiveCell.Resize(5)

will refer to the active cell and the four cells below it.
 
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!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Variable Range, selecting/copying 1
range selection object code 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Modify working code to expand the output. 5
Excel VBA 1
Array with many cells from a row but not in order or together 11
Relative cell position in VBA 2

Back
Top