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
136
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,514
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
 
Solution

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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
VBA Code:
=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

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,514
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
DATE(R1C17,4,1)
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
VBA Code:
Range("A1:H2").Value = Range("A1:H2").Value
which is also more efficient.

The code as you posted would be
VBA Code:
Range("A1:H2").Copy
Range("A1:H2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
or
VBA Code:
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
VBA Code:
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
 
Last edited:

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Joe4,

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

Thank you for all all help in this query. (y) (y) :) :)
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Please note that the below can also be removed as they are all default settings of PasteSpecial
VBA Code:
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
you just need
VBA Code:
Range("A1:H2").PasteSpecial Paste:=xlPasteValues
or
VBA Code:
Range("A1:H2").PasteSpecial xlValues
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Joe4,

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,514
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,121
Messages
5,622,861
Members
415,935
Latest member
kes1973

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
Top