Cách xóa công thức trong Excel bằng VBA nhưng vẫn giữ giá trị và định dạng

Trong bài viết này, tôi sẽ hướng dẫn bạn cách loại bỏ công thức khỏi bảng tính Excel trong khi vẫn giữ nguyên giá trị và định dạng bằng VBA. Trước tiên, bạn sẽ học cách xóa công thức từ một phạm vi ô được chọn, sau đó là từ toàn bộ bảng tính.

VBA để xóa công thức trong Excel giữ giá trị và định dạng (Xem nhanh)

Sub Remove_Formulas_from_Selected_Range()
    Dim Rng As Range
    Set Rng = Selection
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

⧭ Giải thích mã:

Mã này tạo một Macro tên là Remove_Formulas_from_Selected_Range.
Đầu tiên, nó sao chép giá trị từ tất cả các ô trong phạm vi do người dùng chọn.
Sau đó, nó dán các giá trị vào các ô tương ứng nhưng giữ nguyên định dạng.
Như vậy, nó loại bỏ tất cả công thức khỏi phạm vi ô được chọn nhưng vẫn giữ giá trị và định dạng.

2 cách xóa công thức trong Excel giữ giá trị và định dạng bằng VBA

Chúng ta có một tập dữ liệu với tên, lương khởi điểm và lương hiện tại của một số nhân viên công ty Jupyter Group.

Ngoài ra, chúng ta còn có lương trung bình, tên nhân viên có lương cao nhất và nhân viên có lương thấp nhất ở các ô riêng biệt trong bảng tính.

1. VBA để xóa công thức trong Excel giữ giá trị và định dạng từ phạm vi được chọn

Trước hết, hãy phát triển một Macro để xóa công thức từ một phạm vi ô cụ thể, không phải toàn bộ bảng tính.

Ví dụ, hãy thử xóa công thức từ bản ghi nhân viên (B4:D13).

⧭ Mã VBA:

Sub Remove_Formulas_from_Selected_Range()
    Dim Rng As Range
    Set Rng = Selection
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

⧪ Lưu ý: Mã này tạo một Macro tên Remove_Formulas_from_Selected_Range.

⧭ Kết quả:

Trước tiên, lưu tệp dưới dạng Excel Macro-Enabled Workbook. Sau đó chọn phạm vi ô mà bạn muốn xóa công thức.

Ở đây tôi muốn xóa công thức từ bản ghi nhân viên (B4:D13), nên tôi chọn phạm vi B4:D13.

Sau đó chạy Macro Remove_Formulas_from_Selected_Range.

Bạn sẽ thấy tất cả công thức bị xóa khỏi phạm vi ô đã chọn, chỉ còn lại giá trị và định dạng.

Đọc thêm: Chuyển công thức thành giá trị không dùng Paste Special trong Excel (5 phương pháp dễ dàng)

2. VBA để xóa công thức trong Excel giữ giá trị và định dạng từ toàn bộ bảng tính

Trong phương pháp trước, chúng ta đã phát triển Macro để xóa công thức từ phạm vi ô được chọn.

Bây giờ, chúng ta sẽ thử xóa công thức từ toàn bộ bảng tính.

⧭ Mã VBA:

Sub Remove_Formulas_from_the_Whole_Worksheet()
    Sheet_Name = InputBox("Enter the Name of the Worksheet to Remove Formulas: ")
    Dim Rng As Range
    Set Rng = Sheets(Sheet_Name).Cells
    Rng.Copy
    Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
End Sub

⧪ Lưu ý: Mã này tạo một Macro tên Remove_Formulas_from_the_Whole_Worksheet.

⧭ Kết quả:

Quay lại bảng tính và chạy Macro tên Remove_Formulas_from_the_Whole_Worksheet.

Bạn sẽ thấy một Hộp nhập liệu yêu cầu nhập tên bảng tính cần xóa công thức.

Ở đây, tôi muốn xóa công thức từ Sheet2, nên nhập Sheet2.

Nhấn OK.

Tất cả công thức trong toàn bộ bảng tính sẽ bị xóa, chỉ giữ lại giá trị và định dạng.

Lưu ý quan trọng

Ở đây chúng ta sử dụng tùy chọn xlPasteValuesAndNumberFormats để dán với VBA, tùy chọn này giữ nguyên giá trị và định dạng khi dán dữ liệu trong Excel.

Ngoài ra, còn có 11 tùy chọn dán khác trong VBA, mỗi tùy chọn thực hiện một loại dán cụ thể.

Kết luận

Sử dụng các phương pháp này, bạn có thể xóa công thức khỏi bảng tính Excel nhưng vẫn giữ nguyên giá trị và định dạng bằng VBA. Bạn có thắc mắc gì? Hãy đặt câu hỏi cho chúng tôi.