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

2025年12月31日

Introduction

When importing data using Power Query, have you ever encountered a situation where the cell values are not recognized as the intended data type? For example, numbers or dates might not be recognized correctly, preventing calculations or proper display.

In my case, I confirmed that the data type converts correctly if I “manually double-click the cell and press Enter (or press F2 + Enter).”

In this article, I tried creating a VBA macro to automatically execute this operation for all cells.

Why is “F2 + Enter” Needed?

Data imported from Power Query may look correct on the surface, but internally, Excel might still treat it as a text string. In this state, the following problems can occur:

Formulas do not work correctly.
Values are not recognized as valid dates or numbers.
Filters and sorting do not function as expected.

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

Applying “F2 + Enter” Logic to All Cells via VBA

I created a macro to automate this manual operation. Although the processing time can be a bit long depending on the data size, it executes the equivalent of “F2 → Enter” for every single cell.

The VBA Code

Here is the code I implemented:

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

Explanation of the Code

ThisWorkbook.Sheets(1) Targets the first sheet in the workbook (Index number 1). You can change this to "Sheet1" or a specific name if needed.
ws.UsedRange Targets the range of cells actually used in the sheet (excluding empty areas outside the data).
cell.Value = cell.Value By overwriting the cell content with itself, you force Excel to re-evaluate the value. This practically achieves the same result as pressing “F2 → Enter”.

Important Notes and Impact

Performance: The larger the used range, the longer this macro will take to run. If processing thousands of cells, consider processing only specific columns or ranges.
Root Cause: If the data type issue is occurring at the Power Query stage, it is generally better to explicitly set the data type in the Power Query Editor (M Code) for a fundamental solution. However, if that is difficult or if you need a temporary fix, this macro is very effective.

Summary

If data imported via Excel Power Query does not have the expected type, “F2 → Enter” often fixes the reflection of the type. By executing this in bulk using VBA, you can easily handle large numbers of cells.