Is There A Better Way To Force Cells To Refresh

TkdKidSnake

Board Regular
Joined
Nov 27, 2012
Messages
245
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have come up with the code below to enter a range of cells and then force the cell to be entered and refreshed, the reason I need to do this is the data is actually dates and some aren't being picked up as such, I was wondering if anyone knows a quicker way of doing this because fundamentally there are 4 columns that this is required and each column can have more than 2,500 cells.

What I have is below:

Code:
Sub EnterF2()'
Application.ScreenUpdating = False
'
Dim r As Range
Dim n As Integer
Dim AssDateLastRow As Long


AssDateLastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


Set r = Range("P2:S" & AssDateLastRow)
r.Select
r.NumberFormat = "yyyy-mm-dd;@"
r.Select
For n = 1 To r.Rows.Count
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
Next n
'
Application.ScreenUpdating = False


End Sub


Thanks all.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Do the cells that aren't being picked as dates display the month before the day (e.g. mm-dd or yy-mm-dd) ?
If so, perhaps this :
Code:
Sub EnterF2()
Dim r As Range, AssDateLastRow As Integer
AssDateLastRow = Range("A" & Rows.Count).End(xlUp).Row
Set r = Range("P2:S" & AssDateLastRow)
Application.ScreenUpdating = False
r.NumberFormat = "yyyy-mm-dd;@"
r = Evaluate("IF(" & r.Address & "="""",""""," & r.Address & "+0)")
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Added "IFERROR" and tidied up code :
Code:
Sub EnterF2()
Dim r$: r = Range("P2:S" & Range("A" & Rows.Count).End(xlUp).Row).Address
Range(r).NumberFormat = "yyyy-mm-dd;@"
Range(r) = Evaluate("IFERROR(IF(" & r & "="""",""""," & r & "+0)," & r & ")")
End Sub
 
Last edited:
Upvote 0
Many thanks for this, it does considerably better than mine however some of the dates appear to be changing by using this code, so for example

If the date was 11/01/2019 and I enter the cell manually and press enter it stays as 11/01/2019
When I use this code it changes to 01/11/2019

any idea why this is happening?

Added "IFERROR" and tidied up code :
Code:
Sub EnterF2()
Dim r$: r = Range("P2:S" & Range("A" & Rows.Count).End(xlUp).Row).Address
Range(r).NumberFormat = "yyyy-mm-dd;@"
Range(r) = Evaluate("IFERROR(IF(" & r & "="""",""""," & r & "+0)," & r & ")")
End Sub
 
Upvote 0
11/01/2019
Is it Nov 1 or Jan 11 ?
If the date was 11/01/2019 and I enter the cell manually and press enter it stays as 11/01/2019
When I use this code it changes to 01/11/2019
I thought the format is supposed to be yyyy-mm-dd, not mm-dd-yyyy (or dd-mm-yyyy).
Check the format of the cell in question - manually change it to yyyy-mm-dd and see what happens.
 
Last edited:
Upvote 0
It's quite weird to be fair because when I run the macro in the RawData spreadsheet it reacts as required however the code is ran from another spreadsheet / dashboard this is when the date anomalies occur, is there a way using VBA to add this code to the file open which is RawData.xlsm?


Is it Nov 1 or Jan 11 ?

I thought the format is supposed to be yyyy-mm-dd, not mm-dd-yyyy (or dd-mm-yyyy).
Check the format of the cell in question - manually change it to yyyy-mm-dd and see what happens.
 
Upvote 0
Code:
Private Sub Workbook_Open()
Sheets("Sheet1").Activate 'Change sheet name as required
Dim r$: r = Range("P2:S" & Range("A" & Rows.Count).End(xlUp).Row).Address
Range(r).NumberFormat = "yyyy-mm-dd;@"
Range(r) = Evaluate("IFERROR(IF(" & r & "="""",""""," & r & "+0)," & r & ")")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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