Xldown Function

Question

I recorded a macro to format some data, however the range changes on a bi-weekly basis. Hence, I am trying to incorporate the xldown function to select all of the data until the last row. However, I'm not sure how to incorporate this feature into my current VBA, any help would be greatly appreciated as I have had this function appear multiple times. Thank you!

Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C&"",""&RC[-1],RC[-1])"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C161")
Range("C3:C161").Select
Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R2C1:RC[-3],RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D161")
Ran

Does this answer your question? [Better way to find last used row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) — braX, Oct 17 '21 at 17:36
No, because I am not trying to find the last row of data rather - select all the data until the last row — Nina, Oct 17 '21 at 17:42
You need to find the last row to do that. You do not want to use xlDown. — braX, Oct 17 '21 at 17:49
I use the formula earlier in my VBA: Range(Selection, Selection.End(xlDown)) and it copies the data until the last row. What would the alternative function be? — Nina, Oct 17 '21 at 17:59

score 0 · Answer 1 · answered Oct 18 '21 at 15:09

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2").FormulaR1C1 = "=RC[-1]"
Range("C3:C" & LastRow).FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C&"",""&RC[-1],RC[-1])"
Range("D2:D" & LastRow).FormulaR1C1 = "=COUNTIF(R2C1:RC[-3],RC[-3])"

Xldown Function

1 Answers1