Prevent a New Excel Table Column from Copying Data Validation in WPS Spreadsheets
Question details
The user needs to add a column at position 1 in a WPS Spreadsheets table without having the new column inherit the existing first column's formatting and data-validation rules.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Adding a column at position 1 in a ListObject table.
- Observed behavior
- The newly added first column inherits formatting and data-validation rules from the original first column.
Open the workbook in WPS Spreadsheets and enable the Developer tab if you plan to use VBA. Identify the exact table name and the validation range of the current first column before inserting or resizing anything.
Remove Inherited Validation from the New First Column with VBA
Use this when you add the column programmatically and want to keep the table structure while deleting the copied data-validation rules.
When a ListObject column is inserted at Position:=1, WPS Spreadsheets can copy display format and validation properties from the adjacent first column. Deleting validation on the newly added data range leaves the table column intact but removes the unwanted dropdown or input rules.
In WPS Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook's VBA project, choose Insert > Module, and open the new module window.
Paste and run a macro similar to: Dim lo As ListObject; Set lo = ActiveSheet.ListObjects(1); lo.ListColumns.Add Position:=1; lo.ListColumns(1).DataBodyRange.Validation.Delete
Click a cell in the new first column, go to Data > Validation, and verify that no validation rule is applied.
Insert a Blank Worksheet Column Outside the Table and Resize the ListObject
Use this if you prefer to avoid VBA or need more control over formatting and validation before the column becomes part of the table.
Stop WPS Spreadsheets Table Columns from Inheriting Unwanted Data Validation
Use the WPS Spreadsheets VBA editor or table-resize workflow to add a first column cleanly, keeping the ListObject structure while removing inherited validation rules.
- 1. Open the VBA editor: Open the workbook in WPS Spreadsheets and press Alt+F11 to access the VBA editor.
- 2. Insert and run the cleanup macro: Insert a module and run a macro that adds the column at Position:=1 and then calls Validation.Delete on the new ListColumn data range.
- 3. Verify and save: Check the new column through Data > Validation, confirm the rule is removed, and save the workbook as a .xlsm file to keep the macro.
Frequently Asked Questions
Why does a new first column inherit data validation from the original first column?
When a ListObject column is inserted at Position:=1, the table can use the existing first column as a template and copy its display format and data-validation rules to the new column.
Does WPS Spreadsheets support VBA for ListObject and ListColumn objects?
Yes, WPS Office supports VBA macros. You can open the VBA editor with Alt+F11 and use ListObject, ListColumns, and Validation objects similarly to Microsoft Excel.
Can I prevent the validation copy without using VBA?
Yes. Insert a blank worksheet column outside the table, resize the table to include that blank column, and then apply data validation manually only to the new column.
Will Validation.Delete remove only the dropdown and not the table formatting?
Validation.Delete removes the data-validation rule from the selected range. Copied cell formatting may remain, so clear formats separately if you do not want the inherited appearance.
Do I need to save as .xlsm after adding VBA code?
Yes. Use a macro-enabled workbook format such as .xlsm so WPS Spreadsheets preserves the VBA module; a regular .xlsx file may not keep the macro.
