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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe4

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

Kayslover

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
You mean something like this?
VBA Code:
Range("D3:E" & Cells(Rows.Count, "F").End(xlUp).Row) = DateSerial(Right(Range("Q1"),2),4,1)
 

Kayslover

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

ADVERTISEMENT

Joe4,

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

Thanks for your time.
 

Kayslover

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Kayslover

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

Joe4

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,118
Messages
5,622,831
Members
415,934
Latest member
adstocking

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