=COUNTIF For unlimited range VBA

[beginvbaanalyst]

I'm trying to figure out how to use a formula within VBA but stop at an unselected amount of range. Basically just read until there's nothing after the last filled cell
Where is a good example of this? I have the Macro Recorder Code but want to condense it. What tags should I be using for these posts?
What exactly does this mean? =COUNTIFS(C[-9],RC[-2],C[-5],""X"")"

Provided below is the Macro Recorder
ActiveSheet.Range("$I$2:$I$601").RemoveDuplicates Columns:=1, Header:=xlNo 'Removes Duplicate Values from Selected Range
Range("J2").Select 'Selected Range Column J, Row Two ("J2")
Application.CutCopyMode = False 'Leaving Copy/Paste Mode
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-8],RC[-1])" 'Formula for Count If
Range("J2").Select 'Selection for Formula ("J2")
Selection.AutoFill Destination:=Range("J2:J2600") 'Formula COuntif for selected Range ("J2:J2600")
Range("J2:J2600").Select 'Pasted Countif Formula within Selected Range ("J2:J2600")
Range("K2").Select 'Selected Range ("K2")
ActiveCell.FormulaR1C1 = "=COUNTIFS(C[-9],RC[-2],C[-5],""X"")"
Range("K2").Select
Selection.AutoFill Destination:=Range("K2:K26")
Range("K2:K26").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = "=COUNTIFS(C[-10],RC[-3],C[-9],""10011202"")"
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L26")
Range("L2:L26").Select
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(C[-11],RC[-4],C[-7],""X"",C[-10],""10011202"")"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M26")
Range("M2:M26").Select
Range("M4").Select
End Sub

 

[braindiesel]

This =COUNTIFS(C[-9],RC[-2],C[-5],""X"") in cell K2 means
COUNTIFS(B:B,I2,F:F,"X")
so it counts if column B contains whatever is in cell I2 but only if Column F for each same row includes an X

 

[Fluff]

How about

Sub beginvba()
   Dim UsdRws As Long
   
   Range("I2", Range("I" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo
   UsdRws = Range("I" & Rows.Count).End(xlUp).Row
   Range("J2:J" & UsdRws).FormulaR1C1 = "=COUNTIF(C[-8],RC[-1])"
   Range("K2:K" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-9],RC[-2],C[-5],""X"")"
   Range("L2:L" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-10],RC[-3],C[-9],""10011202"")"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-11],RC[-4],C[-7],""X"",C[-10],""10011202"")"
End Sub

 

[beginvbaanalyst]

How about

Sub beginvba()
   Dim UsdRws As Long
  
   Range("I2", Range("I" & Rows.Count).End(xlUp)).RemoveDuplicates 1, xlNo
   UsdRws = Range("I" & Rows.Count).End(xlUp).Row
   Range("J2:J" & UsdRws).FormulaR1C1 = "=COUNTIF(C[-8],RC[-1])"
   Range("K2:K" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-9],RC[-2],C[-5],""X"")"
   Range("L2:L" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-10],RC[-3],C[-9],""10011202"")"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=COUNTIFS(C[-11],RC[-4],C[-7],""X"",C[-10],""10011202"")"
End Sub

Thank you for your reply.
I'm trying to understand the code above.
I understand Dim UsdRws As Long. This means declaring UsdRws As Long. Long is numbers that can be stored between a billion?
Can I ask what makes you choose your declaring option as UsdRws?
I don't quite get what (C[-8.],RC[-1]). Are the numbers the position of cells?
The range "J2:J is just the entire column correct?

 

[Fluff]

Can I ask what makes you choose your declaring option as UsdRws?

UsdRws is just my way of saying "Used Rows" ie the number of rows used in the sheet.

I don't quite get what (C[-8.],RC[-1]). Are the numbers the position of cells?

That's your formula not mine, I just copied it

The range "J2:J is just the entire column correct?

Nope, its J2:J & Usdrws, so if UsdRws was 100, it would be J2:J100