Force "F2"+"Enter" on range of cells

Question

I have an Excel 2010 worksheet which has macros to copy data from other sheets into a specific format on another sheet.

The data copies but I have an issue with the formatting of cell ranges which hold date or time values.

The data originates from a database extract and everything is in text format. In my worksheet when I copy the date (via VBA) I apply the format "yyyy-mm-dd" for dates and "hh:mm.ss.ss" for times.

There is never a fixed amount of rows so I've set the VBA code to apply the formatting to the range of cells for example:

AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row

shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"

Not all cells in the range have the correct format, they will appear as 15/04/2014 not 2014-04-15. If I manually select the cell and press the F2 then ENTER keys the format appears as I need. This happens randomly through the range and there could be thousands of rows so it is not practical to trawl though the worksheet manually hitting F2+ENTER on each one.

I've looked on the internet and found what should automatically do the F2+ENTER with VBA.

The code below is extracted from a larger set of lines of code, so the Dim statements etc. are further up in the actual copy, but this should show the way I've tackled this so far.

Dim shAss As Worksheet
Dim AssDateLastRow As Long
Dim c As Range

'enter method to format 'Date Craftperson Assigned' and 'Time Craftperson Assigned' in   Assignments sheet
'column "C" and "D", to formats required by Archibus: date "yyyy-mm-dd", time  "hh:mm.ss.ss"
AssDateLastRow = shAss.Range("C" & Rows.Count).End(xlUp).Row
shAss.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd"
'ensure format is applied by forcing F2 edit of cell
For Each c In shAss.Range("C4:C" & AssDateLastRow).Cells
    c.Select
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
'Selection.NumberFormat = "yyyy-mm-dd"
Next

When I run the code, the data copies into my worksheets but the dates and times are still in a mixed format.

The attempt at forcing the F2+ENTER via the VBA doesn't seemed to have done anything. If done manually it works okay.

Below is an example of data copied from the results in the worksheet

Work Request Code       Date Assigned       Time  Assigned
92926                   19/05/2014          14:30.00.00
92927                   19/05/2014          15:00.00.00
92928                   2014-05-19          15:15.00.00
92934                   2014-05-19          14:00.00.00
92527                   12/05/2014          07:30
92528                   12/05/2014          08:00
92804                   2014-05-12          16:15
92805                   2014-05-12          16:20.00.00

Ok, what do the dates/numbers look like before you execute the code? I mean what does the `date assigned` `19/05/2014` and `2014-05-19` look like before you run ANY code on it — , Jun 05 '14 at 13:06
I mean like what is the underlying value - not what is displayed but what is in the recordset? u can try `Range("C5").Value2` but not sure this is going to work — , Jun 05 '14 at 13:14
Not sure what you mean by underlying value. The information is copied from e.g. sheet1 to sheet2, via VBA code. on sheets one the date are entered as dd/mm/yyyy, and the cell formating is date format of the same. the format I'm trying to apply is after the data has been copied in to empty cells on sheet 2 — dan1974, Jun 05 '14 at 13:20
Ok great, so where are you assigning and how are you qualifying your active worksheet? — , Jun 05 '14 at 13:22
With ThisWorkbook Set shWO = .Sheets("Workorders") 'Modify as necessary. Set shAss = .Sheets("Assignments") 'Modify as necessary. End With — dan1974, Jun 05 '14 at 13:40
Try manually replacing "-" with "-" and see if that does the job of making the dates the right format. It should work. Then you can just do that as a macro. It's fast and it works. I use it all the time. — Enigmativity, Jun 06 '14 at 11:28

score 17 · Answer 1 · answered Dec 05 '16 at 14:05

17

I use this simple macro to apply F2 + Enter on the currently selected range:

Sub ApplyF2()
    Selection.Value = Selection.FormulaR1C1
End Sub

answered Dec 05 '16 at 14:05

Thomas F.

381
2
8

1

Does not work same as F2+Enter. I have some blank cells that block text from the immediate left cell to flow over. F2+Enter on the problematic cell fixed the problem, but not this macro. – Lun Dec 25 '18 at 07:58
Doesn't convert URLs to hyperlinks. So definitely not the same as F2+Enter. – ivan_pozdeev Jan 31 '21 at 03:24
This worked for me with instead of using selection I use the range Range("H2:I" & lrow).Value = Range("H2:I" & lrow).FormulaR1C1 lrow beign the last used row – Tom Ruiz Apr 14 '21 at 15:50

score 6 · Answer 2 · answered Jun 10 '14 at 01:48

I struggled to get this to work too. My problem has been not just dates but also data with a single quote in front of it. What I hacked together works great for me. It cleans up over 70,000 cells very fast. Hope it works for you:

(you will change the range and such to suit your needs)

    Dim MyRange As Range

    Set MyRange = Range(Cells(2, 7), [G1].End(xlDown))

    For Each MyRange In MyRange.Cells
    'Mimic F2 without SendKeys
        MyRange.Value = MyRange.Value
    Next

score 3 · Answer 3 · answered Jun 06 '14 at 13:52

I can think of two options to get Excel to apply the formatting to the cells in one step.

The first is to use the Text to columns functionality even though there is nothing in the column to split. The second option is to copy a value of 1 and paste it into the cells using the Paste Special - Multiply option.

Although either method should force an update of the cell formating, I would lean towards the first option. This is incase some of your dates are is stored as text.

    Sub Format_Text_to_Columns()

    Dim AssDateLastRow As Long

    AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
    'Set the format

        Range("C4:C" & AssDateLastRow).Select
        Selection.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
         Space:=True, FieldInfo:=Array(1, 5)
    'Use text to columns to force a format update

    End Sub



    Sub Format_Paste_Special_Multiply()

    Dim AssDateLastRow As Long

    AssDateLastRow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("C4:C" & AssDateLastRow).NumberFormat = "yyyy-mm-dd;@"
    'Set the format

        Range("C1").FormulaR1C1 = "1"
        Range("C1").Copy
        Range("C4:C" & AssDateLastRow).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
        Application.CutCopyMode = False
        Range("C1").ClearContents
    'Multiply the dates by 1 to force a format update

    End Sub

score 3 · Answer 4 · answered Sep 03 '15 at 14:44

3

This worked for me.

Dim r As Range
Dim n As Integer
Dim AssDateLastRow As Long

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

Set r = Range("E2:E" & AssDateLastRow)
r.Select
r.NumberFormat = "ddmmyyyy;@"
r.Select
For n = 1 To r.Rows.Count
    SendKeys "{F2}", True
    SendKeys "{ENTER}", True
Next n

answered Sep 03 '15 at 14:44

RafaEsp

31
1

Could you please explain your answer? – PKirby Sep 03 '15 at 14:47

score 3 · Answer 5 · answered May 19 '20 at 14:17

3

It is possible to use Text to Columns to solve this problem

1) Highlight the column of data

2) Go to Data -> Text To Columns -> Delimited -> (deselect everything) -> Next

3) On page 3 of the wizard, set the Column Data Format YMD

4) OK

answered May 19 '20 at 14:17

M_F

57
6

1

This is the proper solution – Fandango68 Feb 08 '23 at 02:09

score 1 · Answer 6 · edited May 15 '18 at 02:22

Sub RefreshCells()

Dim r As Range, rr As Range
Set rr = Selection
For Each r In rr
r.Select
Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False

    Application.SendKeys "{F2}"
    Application.SendKeys "{ENTER}"
    Application.SendKeys "+{ENTER}"
    DoEvents
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Application.CutCopyMode = False

    Application.SendKeys "{ENTER}"

    DoEvents

Next



End Sub

please explain your answer, don't just post a block of code – Claire May 15 '18 at 00:17 — Claire, May 15 '18 at 00:17

score 0 · Answer 7 · answered Jun 05 '14 at 13:11

0

It seems odd that you would need to send keys F2 + Enter. What is the formatting before you run the macro? Try formatting the whole column that way (it won't affect the text).

Columns("C:C").NumberFormat = "yyyy-mm-dd"

answered Jun 05 '14 at 13:11

MatthewHagemann

1,167
2
9
20

3

we have formatted the columns, that way, and when you look at the cell format it shows as the format we want (yyyy-mm-dd), but they actual data in the cell is showing as dd/mm/yyyy, but as mentioned if we manually use F2+Enter it cans the appearance in the cell to the correct format. We just want to try automate the process – dan1974 Jun 05 '14 at 15:35

score 0 · Answer 8 · answered Mar 28 '16 at 20:21

0

My variation

n = Selection.Rows.count
Dim r1 As range, rv As range
Set r1 = Selection.Cells(1, 1)
For I = 1 To n
Set rv = r1.offset(I - 1, 0)
vali = rv.value
 IsNumeric(vali) Then
 vali = CDbl(vali)
 rv.value = 0
 rv.value = vali
 End If

answered Mar 28 '16 at 20:21

anefeletos

672
7
19

score 0 · Answer 9 · answered Jun 14 '16 at 06:20

0

Try to press F9 or File-Option-formulas-workbook calculation- automatic

answered Jun 14 '16 at 06:20

user6462924

1

score 0 · Answer 10 · answered Apr 10 '18 at 22:01

0

I just set the cell to the right of the top entry equal to a formula that multiplied the problem cell times 1. That new cell was a proper number, so then double clicking the handle extended it down the whole column fixed them all!

answered Apr 10 '18 at 22:01

Bill LaPrise

808
17
27

score 0 · Answer 11 · answered Sep 06 '18 at 02:13

Sendkeys are not stable. The better way is to store the text in the clipboard and paste it.

See here on how to store values in the clipboard

Sub CopyText(Text As String)
Dim MSForms_DataObject As Object
Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
MSForms_DataObject.SetText Text
MSForms_DataObject.PutInClipboard
Set MSForms_DataObject = Nothing
End Sub

Sub Test()
CopyText (ActiveCell.Value)
ActiveCell.PasteSpecial
End Sub
'In place of active cell, you may pass a range

score 0 · Answer 12 · answered Jan 07 '22 at 00:45

This works for me

Sub f2Cells(sel as Range)
    Dim rng as Range

    On Error GoTo exitHere

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    For Each rng In sel.Cells
        If Not Intersect(sel, Application.Range(rng.Address)) Is Nothing And _
            Application.Range(rng.Address).EntireColumn.Hidden = False And _
            Application.Range(rng.Address).EntireRow.Hidden = False Then
                Application.Range(rng.Address).Application.SendKeys "({F2}{ENTER})", True
        End If
    Next rng

exitHere:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    Application.SendKeys "{NUMLOCK}", True
End Sub

Then from your function you can just call

f2Cells shAss.Range("C4:C" & AssDateLastRow)

sel.Cells!? Does nothing. Try "Selection" – Fandango68 Feb 08 '23 at 02:09 — Fandango68, Feb 08 '23 at 02:09

score -3 · Answer 13 · answered Feb 13 '19 at 00:52

-3

I just got it, Simple
Select all the cells you want to hit F2 and Enter and run this short macro:

Sub AutoF2Enter()
Selection.Value = Selection.Value
End Sub

Works on date and numbers!
50.000 cells in a second!

answered Feb 13 '19 at 00:52

Tomy

1

Force "F2"+"Enter" on range of cells

13 Answers13

Linked