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]

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

Ok, now I am REALLY confused. What I already gave should work for existing data.

My best guess is that you want to populate not a value, but a dynamic formula in column E that will display data if there is column D, and nothing if there is not.
This should work, as long as your don't add any new data below the last populated cell in column F.

Sub MyCode2()
    Range("E3:E" & Cells(Rows.Count, "F").End(xlUp).Row).FormulaR1C1 = "=IF(LEN(RC[-1])>0,DATE(""20""&RIGHT(R1C17,2),4,1),"""")"
End Sub

If you add data to column D between rows 3 and the last row with data in F, the formula will return the value instead of a blank.

If that is not what you want, please provide us screen prints of before/after images, and walk us through an actual example of what you want to happen.

 

[Kayslover]

Joe4,

Sorry I am causing a lot of confusion. Hopefully the following will help.

I have the following code, and E3 onwards is populated with the date due to the 2nd statement that you provided.

Range("D3:E" & Cells(Rows.Count, "F").End(xlUp).Row).ClearContents
Range("E3:E" & Cells(Rows.Count, "F").End(xlUp).Row) = DateSerial(Right(Range("Q1"), 2), 4, 1)
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

The loop you have above checks, Cell D3 onwards and if it is not blank, it inserts myDate.

The problem is that whilst the macro is running, D3 onwards will ALWAYS be blanks as we have just cleared the contents of D3 onwards by the code in the first line above. Therefore the loop doesn't do anything.

In the spreadsheet, I have inserted a new column F after column E and inserted the following formula in Column F and hidden Column E

=IF(ISBLANK(D3),"",E3)

Basically, can the loop not have the above formula in cells E3 onwards? This will avoid inserting a new column, and inserting the above formula in all cells as the formula will exist in cells E3 onwards.

So when the macro has completed, and you go into the spreadsheet, the date will only show in Column E3 onwards when you populate column D3 onwards with a value.

I hope the above clarifies things.

 

[Kayslover]

Joe4,

I didn't see your post #11, tried it out and perfect (just as I described in my post # 12.

Can you please explain what the DATE(""20""&RIGHT(R1C17,2),4,1) is doing?

 

[Joe4]

This should explain it: MS Excel: How to use the DATE Function (WS)
Note that we if don't use a 4 digit year, it may use 1900s instead of 2000s.
That is what we are appending "20" before the two digit year we are extracting from cell Q1.
If cell Q1 is already the actual 4 digit year (i.e. 2021), then we can just use:

DATE(R1C17,4,1)

 

[Kayslover]

Joe4,

Thank you for the above explanation and I am truly sorry for the confusion I caused, and the additional time you had to spend on my query.

In your post #2 you mentioned that SELECTING in Excel is inefficient. So rather than a statement like

Range("A1:H2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Can just define the selection cells as a range and do what you want as in the code below

Range("A1:H2").Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Thank you very much for your time and patient.

 

[MARK858]

Can just define the selection cells as a range and do what you want as in the code below

You can do that just with

Range("A1:H2").Value = Range("A1:H2").Value

which is also more efficient.

The code as you posted would be

Range("A1:H2").Copy
Range("A1:H2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

or

Dim myRng As Range
Set myRng = Range("A1:H2")
myRng.Copy
myRng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

or

Dim myRng As Range
Set myRng = Range("A1:H2")
With myRng
  .Copy
  .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False

 

[Kayslover]

Joe4,

Thanks for the above, hopefully it will speed up the code.

Thank you for all all help in this query.

 

[MARK858]

Please note that the below can also be removed as they are all default settings of PasteSpecial

, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

you just need

Range("A1:H2").PasteSpecial Paste:=xlPasteValues

or

Range("A1:H2").PasteSpecial xlValues

 

[Kayslover]

Joe4,

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

 

[Joe4]

Joe4,

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

I am guessing you meant Mark858, not me, as he provided those additional pointers.