[Power Query x VBA] How to Create a Macro to Automatically Apply “F2” + “Enter” to All Cells

2025年12月30日

When importing data with Power Query, cell values sometimes do not end up as the intended data type. For example, numbers or dates may not be recognized correctly, preventing expected calculations or display. I confirmed that manually double-clicking a cell and pressing Enter (equivalent to “F2” + “Enter”) converts it to the correct type. In this article, I tried a method to automatically execute this operation for all cells using VBA.

Why is “F2 + Enter” Necessary?

Data imported from Power Query may be treated internally as text, even if it looks correct. In this state, the following problems can occur:

Formulas do not work correctly.
Data is not recognized as dates or numbers.
Filtering and sorting do not function as expected.

In such cases, manually entering edit mode and confirming (F2 -> Enter) often forces Excel to correctly convert the data type.

Applying “F2 + Enter” Equivalent to All Cells with VBA

I created a macro to automate this manual operation using VBA. Although the processing time is slightly longer, it can execute the equivalent of “F2 -> Enter” for all cells.

Implemented VBA Code

Below is the code used.

Sub EditAndEnterAllCells()
    Dim ws As Worksheet
    Dim cell As Range

    ' Set the first sheet as active
    Set ws = ThisWorkbook.Sheets(1)
    ws.Activate

    ' Execute operation for all used cells
    For Each cell In ws.UsedRange
        cell.Activate
        cell.Value = cell.Value
    Next cell
End Sub

Code Explanation

ThisWorkbook.Sheets(1) This targets the first sheet in the workbook (index number 1). You can change this to “Sheet1” if necessary.
ws.UsedRange This targets the range of cells actually used in the sheet (excluding blanks).
cell.Value = cell.Value By overwriting the cell content with itself, this forces Excel to re-evaluate the value. This practically achieves the same behavior as “F2 -> Enter”.

Notes and Processing Impact

This macro takes longer to execute as the used range increases. If the target covers thousands of cells, it may be effective to split the processing into smaller ranges. Also, if the data type issue occurs at the Power Query stage, explicitly specifying the type in the M code is a more fundamental solution. However, if that is difficult or for a temporary fix, this macro is very effective.

Summary

If data imported by Excel Power Query does not become the expected type, “F2 -> Enter” can often reflect the correct type. By executing this operation all at once with VBA, you can easily handle a large number of cells.