Define dynamic data range

[SH14]

Hi,
I want to replace the hardcoded data ranges in my macro recording (shown below) with dynamic data ranges.

Please Login or Register  to view this content.

a) My active worksheet has an unknown 'n' number of rows in column A from row A2 onwards.

b) Column C2 contains a COUNTIF formula on A2 using data from Sheet1, column H rows 2 to 168. Sheet1 row H also contains an unknown number of rows from H2 onwards.

c) For every row in the range A3 to A'n' the countif formula in C2 is then copied its corresponding range (C3 to C'n').

[MarvinP]

Hi SH14 and welcome to the forum,

I'm a little confused on what you are looking for.
There is a way to do Dynamic Named Ranges and then use that name in a formula at:
http://www.ozgrid.com/Excel/DynamicRanges.htm
But I don't think that is what you are looking for.

If not then to find the last non blank cell in column C use this line of code

Please Login or Register  to view this content.

Then in your Range statement in your code use this line

Please Login or Register  to view this content.

I hope one of the above answers your question.

[Paul]

Hi SH14, welcome to the forum.

Rather than selecting a cell, entering a formula and then copying/pasting the formula, you can do it in one step. The code below determines the last used row in column H on Sheet1 (variable LR1) as well as the last used row in column A on the active sheet (variable LR2). It then puts your COUNTIF formula in column C of the active sheet from C2:C?, where ? is the same as LR2 (last used row in column A).

Please Login or Register  to view this content.

Hope that helps!

[SH14]

Paul,
Thanks for the elegant & simple solution! I appreciate the help.