UsedRange lastrow computing extra rows

Question

This code is supposed to make a formula go from C9 until the last row used in C. I am using UsedRange because from C1 to C9, there are empty values.

lastrow = ws1.UsedRange.Rows.Count

ws1.Range("C9:C" & lastrow).Formula = _
    "=INDEX(Tableau_Employés[EMPLOYÉ],MATCH('Overtime KRONOS'!$A9,Tableau_Employés[ORDRE D''ANCIENNETÉ]))"

However this pastes the formula too far, going 11 rows after the last row. I'm not sure why.

`UsedRange` has issues. See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Feb 19 '20 at 20:53
@Blade Daniel `rows.count` future proofs it: `lastrow = ws1.Range("A" & rows.count).End(xlUp).Row` — Dan Donoghue, Feb 19 '20 at 20:55

score 0 · Answer 1 · answered Feb 19 '20 at 20:59

0

Changed to

lastrow = ws1.Range("A" & Rows.Count).End(xlUp).Row

answered Feb 19 '20 at 20:59

Jade

77
1
14

UsedRange lastrow computing extra rows

1 Answers1