Ask a question Report Reply to this topic

Selecting a block of cells depending on the values of two others [Closed]

jwilson28 3 Posts Friday April 21, 2017Registration date December 5, 2017 Last seen - Apr 21, 2017 at 06:53 AM - Latest reply: TrowaD 2349 Posts Sunday September 12, 2010Registration dateModeratorStatus April 26, 2018 Last seen

- Apr 25, 2017 at 12:09 PM

Excel Ask a question Report Reply to this topic

Hi all

I'm pretty new to macros and I have been unable to find the answer to this question anywhere.

I'm trying to write a macro that will select a block of cells around the active cell depending on the values of 2 other cells

For example if C4 was the active cell and A50 had a value in it which would indicate the number of columns that would need to be selected and C50 had a value which would be the number of rows down that would need to be highlighted.

So if A50 was 4 and C50 was 5 there would be a block of cells highlighted from C4 to F8.

The active cell will change and so will the values in A50 and C50.

Thanks

Jon

4 replies

Report Respond to ac3mark

ac3mark 9220 Posts Monday June 3, 2013Registration dateModeratorStatus April 25, 2018 Last seen - Apr 21, 2017 at 01:21 PM

Helpful

Try this. Record your Macro, and view it (edit it). Then change the part of the macro you want to have be dynamic. Post back if you have any questions.

Report

jwilson28 3 Posts Friday April 21, 2017Registration date December 5, 2017 Last seen - Apr 22, 2017 at 08:10 AM

Hi ac3mark

I have the code

Sub Select6()
Range(ActiveCell(1, 1), ActiveCell(6, 6)).Select

This code highlights a a block of 6 rows and 6 columns to the right of the active cell

I am struggling to replace the numbers so rather than highlighting the number of cells that are in the code it highlights number of cells from the the values in cells A50 and C50

Jon

Report Respond to ac3mark

ac3mark 9220 Posts Monday June 3, 2013Registration dateModeratorStatus April 25, 2018 Last seen - Apr 24, 2017 at 04:45 PM

Helpful

OK, that is a start then. Thanks for you posting back!

Initilize a variable, call it a "ARANGE", and set it as a RANGE.

Set ARANGE to "A50:C50"

now, utilize it as RANGE(ARANGE).select

Try that. You can then set ARANGE to what ever variable (range, cell) you wish!

Report Respond to TrowaD

TrowaD 2349 Posts Sunday September 12, 2010Registration dateModeratorStatus April 26, 2018 Last seen - Apr 25, 2017 at 12:09 PM

Helpful

Or try this:

Sub RunMe()
ActiveCell.Resize(Range("A50").Value + 1, Range("C50").Value + 1).Select
End Sub

When your active cell is C4 and you add 4 rows you get row 8.
When you add 5 to column C (=3) you will get column H (=8).

So when A50=4 and C50=5 and C4 is active cell, then selected range after macro is run, will be C4 to H8.

Best regards,
Trowa

Ask a question