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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
For your first question, try this:
VBA Code:
Range("D3:E" & Cells(Rows.Count, "F").End(xlUp).Row).ClearContents
(note that you do not need to select your range in order to work with it - it is actually more efficient NOT to select it).

For your second question, what exactly is in cell Q1?
Is it an actual date, or just a 4 digit year. If just a four digit year, I think your formula should work.
If it does not, cite us an example of what is in cell Q1, and show us what your formula is returning.
 
Upvote 0
Joe4,

Firstly thank you for looking at this thread and your assistance so fay.

Q1 is a 4 digit year (i.e. 2021).

My 2nd question should have said "How can populate the dates in Cells Range("D3:E" & Cells(Rows.Count, "F").End(xlUp).Row)". e.g populate Column E with say 01/04/21?
 
Upvote 0
You mean something like this?
VBA Code:
Range("D3:E" & Cells(Rows.Count, "F").End(xlUp).Row) = DateSerial(Right(Range("Q1"),2),4,1)
 
Upvote 0
Joe4,

I have switched off my PC and will try out your recommendations tomorrow and report back.

Thanks for your time.
 
Upvote 0
Joe4,

Worked like a dream.

I changed your recommented solution for problem 2 to:-
VBA Code:
Range("E3:E" & Cells(Rows.Count, "F").End(xlUp).Row) = DateSerial(Right(Range("Q1"), 2), 4, 1)
as I only wanted E to be populated.

Is there any way that the formula in the macro can be updated to ONLY show the date in Column E if the cell in column D is NOT BLANKS?
i.e.

If D3 is not blank then the date should not show in E3
if D4 has a value, then E4 should show the value
If D5 has a value, then E5 should show the value.
 
Upvote 0
Is there any way that the formula in the macro can be updated to ONLY show the date in Column E if the cell in column D is NOT BLANKS?
i.e.

If D3 is not blank then the date should not show in E3
if D4 has a value, then E4 should show the value
If D5 has a value, then E5 should show the value.
I assume that you mean "If D3 is blank then the date should not show in E3".

Try this:
VBA Code:
Sub MyCode()

    Dim myDate As Date
    Dim lastRow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False

    myDate = DateSerial(Right(Range("Q1"), 2), 4, 1)
    
    lastRow = Cells(Rows.Count, "F").End(xlUp).Row
    
    For r = 3 To lastRow
        If Len(Cells(r, "D")) > 0 Then Cells(r, "E") = myDate
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
Some of these steps could be combined, but I broke out the calculations so it is easier to follow what each step is doing.
 
Upvote 0
Joe4,

Thank you for your solution, but I don’t think I explained myself clearly.

Your previous solution of inserting the date worked as it inserted the required date in all cells in column E3 to the last row.

VBA Code:
Range("E3:E" & Cells(Rows.Count, "F").End(xlUp).Row) = DateSerial(Right(Range("Q1"), 2), 4, 1)

Once the macro finishes, you can go into the spreadsheet, you see the date as expected in Column E.

What I should have said is that I want the above formula you gave to be amended/expanded so that when the macro has completed and you go into the spreadsheet, then say you enter 123 in D3, then E3 would show the date inserted by the macro. If you don’t enter anything in D4, then the date entered by the macro should not show.

Sorry I was not clearer.
 
Upvote 0
I am getting very confused.
Do you want this to run on existing data?
Or do you want it to run on new data as it is added to the sheet?
Will new data ever be added beneath the initial last row that is determined when the sheet is first opened?
 
Upvote 0
Joe4,

I am quite happy if it is just for the existing data.

New rows can be added during the year and the user can just add a date in colum E for the new rows they add.

Hope the above clarifies things.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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