Getting last row with data from another column and Setting cells in a range to a date

[Kayslover]

Hi All

I currently have a statement in a macro that reads

Range("D3:E202").Select
Selection.ClearContents

For further information there are headers in Rows 1 and 2 and hence the range starting in row 3.

How can I change the range to read

Range("D3:E last used row in Column F").Select

The number of rows in column F changes yearly and therefore I don't want to hardcode absolute cell number in the above formula as I am sure a day will come when my range of E202 will be exceed.

Additionally, I would like Column E to be populated with a date (format dd/mm/yy) that is stored in cell Q2 of the active sheet?

Q2 currently has the following formula in it

=CONCATENATE("01/04/",R2)

R2 has the formula

=RIGHT(Q1,2)

Q1 is populated by the user entering a 4 digit year when the macro runs and the variable is stored within the macro and is called myYear. I am not interested in the century and hence the use of the formula in cell R2 and the concatenate in Q2.

I am sure that the above can be cleaned up, but I have no idea how to do it.

Appreciate any help you can offer.

 

[Kayslover]

Mark858,

Thanks for the additionally pointers, I will bear them in mind for the future.

 

[MARK858]

Mark858,

Thanks for the additionally pointers, I will bear them in mind for the future.

You're welcome