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.

 

[Joe4]

For your first question, try this:

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.

 

[Kayslover]

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?

 

[Joe4]

You mean something like this?

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

 

[Kayslover]

Joe4,

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

Thanks for your time.

 

[Kayslover]

Joe4,

Worked like a dream.

I changed your recommented solution for problem 2 to:-

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.

 

[Joe4]

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:

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.

 

[Kayslover]

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.

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.

 

[Joe4]

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?

 

[Kayslover]

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.