Convert Old Excel Rows from Formulas to Values with VBA in WPS Spreadsheets
Question details
The user wants a VBA macro that checks the date in column A and converts formulas in columns A:Z to static displayed values when the row is more than 40 days old, processing only columns A:Z instead of the entire worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a date-based worksheet and wants old formula-driven rows locked to their current displayed values while avoiding unnecessary processing of other columns.
- Observed behavior
- Rows older than 40 days still contain live formulas in columns A:Z, and the user needs an automated VBA procedure to replace only those formulas with their current values.
Open the workbook in WPS Spreadsheets, confirm the Developer tab is visible, and verify that the dates in column A are recognized as valid dates. Because this macro removes formulas permanently, save a copy of the workbook before running it if you may need the original formulas later.
Run a VBA Macro to Convert Old Row Formulas to Values in WPS Spreadsheets
Use this solution when you have many rows with dates in column A and want to replace formulas in columns A:Z with their current values only for rows older than 40 days.
The macro loops through the used rows, skips rows whose column A value is empty or not a valid date, and compares each date with Date - 40. For qualifying rows, it assigns each cell in columns A:Z back to itself using .Value = .Value, which removes formulas but preserves the displayed result.
The procedure disables screen updating and events during the run to improve performance and restores them when finished, even if an error occurs.
Click the Developer tab, then click the Visual Basic button. If the Developer tab is not visible, go to File > Options > Customize Ribbon and enable Developer.
In the Visual Basic for Applications window, click Insert > Module to create a new module where the macro code will be added.
Paste the following macro into the module, then press Ctrl+S to save: Sub ConvertFormulasToValues() Const AGE_LIMIT As Long = 40 Dim ws As Worksheet Dim r As Long, c As Long Set ws = ActiveSheet Application.ScreenUpdating = False Application.EnableEvents = False On Error GoTo CleanExit For r = 1 To ws.UsedRange.Rows.Count If IsDate(ws.Cells(r, 1).Value) Then If Date - ws.Cells(r, 1).Value > AGE_LIMIT Then For c = 1 To 26 If ws.Cells(r, c).HasFormula Then ws.Cells(r, c).Value = ws.Cells(r, c).Value End If Next c End If End If Next r CleanExit: Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Click Run > Run Sub/UserForm or press F5 while the macro is selected, select the active worksheet, and wait for the procedure to finish.
Check rows older than 40 days in columns A:Z to confirm that formulas have been replaced by their current displayed values.
Manually Paste Values for Older Rows in WPS Spreadsheets
Use this approach when you only need to convert a few rows manually and prefer not to run VBA code.
Convert Old Formula Rows to Values with VBA in WPS Spreadsheets
Run a WPS Spreadsheets VBA macro that checks the date in column A and converts formulas in columns A:Z to their current values when a row is older than 40 days. The macro processes only the required columns and restores application settings after completion.
- 1. Enable the Developer tab: In WPS Spreadsheets, click File > Options > Customize Ribbon, check the Developer option, and click OK.
- 2. Open the Visual Basic editor: On the Developer tab, click the Visual Basic button to open the VBA editor.
- 3. Insert a module and add the macro: Click Insert > Module, paste the VBA code into the module, and press Ctrl+S to save.
- 4. Run the macro: Click Run > Run Sub/UserForm or press F5 while the macro is selected, choose the active worksheet, and confirm the action.
- 5. Check the result: Verify that rows older than 40 days in columns A:Z now contain static values instead of formulas.
Frequently Asked Questions
Does this macro work in WPS Spreadsheets with .xlsx workbooks?
Yes. WPS Spreadsheets supports VBA and can open, edit, and save .xlsx and macro-enabled .xlsm files. Save the workbook as a macro-enabled file type if you want to keep the VBA code.
What should I do if the Developer tab is missing?
Go to File > Options > Customize Ribbon, enable the Developer checkbox, and click OK to show the Developer tab.
Will I lose the original formulas after running the macro?
Yes. The macro replaces formulas with their current values, so keep a copy of the workbook if you may need the original formulas later.
Can I change the 40-day threshold?
Yes. In the VBA code, change the AGE_LIMIT constant from 40 to the number of days you want, then save and run the macro again.
Why does the macro only process columns A:Z?
The loop is limited to 26 columns by design to avoid scanning the entire worksheet. You can adjust the For c = 1 To 26 line if your data uses different columns.
