Define dynamic data range

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

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(Sheet1!R2C8:R168C8,RC[-2])"
    Selection.Copy
    Range("C3:C105").Select
    ActiveSheet.Paste

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').

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

Dim LastCRow as Double
LastCRow = Cells(Rows.Count, "C").End(xlUp).Row

Then in your Range statement in your code use this line

Range("C3:C" & LastCRow).Select

I hope one of the above answers your question.

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).

Sub test()
Dim LR1 As Long, LR2 As Long
LR1 = Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row
LR2 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & LR2).Formula = "=COUNTIF(Sheet1!$H$2:$H$" & LR1 & ",A2)"
End Sub

Hope that helps!