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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
Hi All

I currently have a statement in a macro that reads
VBA Code:
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
VBA Code:
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
VBA Code:
=CONCATENATE("01/04/",R2)
R2 has the formula
VBA Code:
=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.
 
Mark858,

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top