How To Autofill (Using VBA) Until Adjacent Cell Equals a Particular Value

Question

I want to autofill a data validation list down in column E. However I want to stop the autofill when it reaches a particular value in an adjacent column (column B).

For example, I have 50 rows in column B. The 51st row however in column B contains a text value of 'TOTAL'. I don't want the autofill to go all the way down to the 51st record, rather stop at row 50.

The reason for this is because the number of records change all the time & there are other things below the 'END' text that I do not want the user to be able to select a value from column E using a data validation drop down list.

Thanks in advance.

Answer 1

If you use an Excel Table you don't need to autofill anything. The total row will move automatically. Data validation, conditional formats and formulas will automatically be applied to new rows.