How to delete same rows or ranges across multiple sheets in Excel?
When working with Excel workbooks that contain multiple worksheets, you may find it necessary to delete identical rows or specific ranges in all sheets simultaneously. For example, you might need to remove summary rows, outdated data, or recurring sections from each worksheet in a standardized workbook. Conducting these deletions manually in each sheet can be inefficient and prone to mistakes, especially in large files. Fortunately, there are practical approaches available to help you efficiently delete the same rows or ranges across all selected sheets. This article introduces two main methods and provides guidance, tips, cautions, and troubleshooting to help you accomplish the task smoothly and accurately.
Delete same rows or ranges across multiple sheets with grouping all sheets
Delete same rows or ranges across multiple sheets with VBA codes
Delete same rows or ranges across multiple sheets with grouping all sheets
Grouping worksheets allows you to apply the same actions to multiple sheets at once. This method is straightforward and especially useful when the sheets are structured identically and you want changes reflected in every sheet. By grouping, any action — such as deleting rows or ranges — will occur in exactly the same place across all group members.
Applicable scenarios: This solution is ideal when:
- All your worksheets have an identical structure (same number and arrangement of rows/columns).
- You want to delete the same row(s) or range(s) in each sheet at the same time.
However, take caution: any operation performed while sheets are grouped will affect all grouped worksheets. If your worksheets vary in structure, unintended deletions may occur.
To delete identical rows or ranges from multiple grouped sheets, follow these steps:
1. Right click on any sheet tab in your current workbook. From the right-click menu, choose Select All Sheets. This will group all worksheets together.
Tip: If you only need to group specific sheets (not all), hold down Ctrl and click the sheet tabs you want to group, then right click and choose Group Sheets.
2. While all worksheets are grouped, select the rows or ranges you wish to delete in the active sheet. When you delete these rows or ranges using the Delete button or by right-clicking and selecting Delete, the same rows or ranges will be deleted automatically in the exact position across all grouped sheets.
Caution: Undo (Ctrl+Z) will also affect all grouped sheets. Double-check that the correct area is selected before deletion, as this action cannot be reversed separately in each sheet.
3. Once you have completed your deletions, it is essential to ungroup the worksheets. Right click on any sheet tab again and select Ungroup Sheets from the menu. This step will prevent any further accidental changes from affecting all sheets simultaneously.
Practical suggestions: Before grouping, consider saving a backup copy of your workbook. If there are formulas dependent on the rows being deleted, review potential impacts. When ungrouping, ensure you see [Group] disappear from the filename in the Excel title bar.
Troubleshooting: If you find that an action was applied to the wrong rows in multiple sheets, use Undo immediately, regroup, and repeat with accurate selection. Always verify group status before further editing.
Delete same rows or ranges across multiple sheets with VBA codes
If you need to remove the same rows or specific ranges in many worksheets, especially when dealing with complex scenarios or a large number of sheets, using VBA (Visual Basic for Applications) provides a fast and flexible alternative. VBA allows customization, batch processing, and can be tailored for more advanced criteria or for deleting non-contiguous rows/ranges.
Applicable scenarios: Choose this method when:
- You need to delete the same rows/ranges in a large number of sheets or such tasks are repeated frequently.
- Your deletion requirements involve more complex logic (such as variable ranges, based on user input).
- Worksheets do not allow easy grouping due to differing or complex structures.
Points for attention: Running VBA code will directly modify your data. It is highly recommended to save a backup of your workbook before proceeding. Also, ensure macros are enabled in your Excel settings.
Delete same rows across multiple sheets
1. Press Alt + F11 at the same time to open the Microsoft Visual Basic for Applications (VBA) editor window.
2. In the VBA editor, click Insert > Module to create a new module. Then, copy and paste the following VBA code into the module's code window:
Sub bleh()
Dim xWs As Worksheet
Set xWs = ActiveSheet
ThisWorkbook.Worksheets.Select
Rows("4:5").Select
Selection.Delete
xWs.Select
End SubNote: In the VBA code, 4:5 means that rows 4 and 5 will be deleted in all worksheets of the current workbook. If you wish to delete different rows, change these values as needed. For example, to remove only row 2, replace 4:5 with 2:2.
3. Press the F5 key to execute the code. The specified rows will be deleted from every sheet in your workbook immediately.
Tips and reminders: If an error occurs (such as attempting to delete rows that do not exist in a shorter sheet), verify that all sheets have sufficient rows. The code will process visible sheets; hidden or protected sheets might cause errors if not handled appropriately.
Troubleshooting: If nothing happens or some rows are not deleted as expected, confirm you have selected the correct module and the code does not have syntax errors. Also check your sheet protection status.
Delete same ranges across multiple sheets
If you want to delete (clear) the same range of cells (e.g., a block of data such as B2:D10) across every worksheet, you can use the following VBA code. This approach is useful for situations where you want to remove data from a specific area rather than entire rows.
Private Sub CommandButton2_Click()
Dim xRg As Range
Dim xTxt As String
Dim xWs As Worksheet
On Error Resume Next
Set xWs = ActiveSheet
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("Please select the range you want to delete across multiple sheets:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
xRg.ClearContents
ThisWorkbook.Worksheets.Select
ActiveWindow.SelectedSheets.FillAcrossSheets xRg, xlFillWithContents
xWs.Select
End SubAfter copying and pasting the VBA code into the module, press F5 to run the macro. In the window that pops up labeled "Kutools for Excel", select the range you wish to delete across all sheets, then click OK.
Once you confirm your selection, the values in the specified range will be cleared from every worksheet in your current workbook.
Practical suggestions: This method is best when you need to clear out values from the same cell range in multiple sheets. Note that "deleting" here removes cell content but does not actually shift cells up/left; structural changes to your worksheet (like deleting entire rows/columns) are not performed unless your VBA code specifically instructs it.
Precautions: Protect your data by saving the file before executing the macro. If a selected sheet is protected or hidden, VBA might return an error or skip that sheet.
Troubleshooting: If the dialog box does not appear, ensure that macros are enabled and you have pasted the full code correctly. If the wrong range is deleted, re-run the macro with the correct selection.
Summary suggestions: Both the worksheet grouping and VBA approaches enable efficient multi-sheet deletions. Decide based on the structure of your workbook and how often you require such tasks. For regular use, save your VBA procedures for quick access and consider customizing code to target specific sheets or ranges.
Other alternatives
- Manual repeat with Find/Replace: If you only have a handful of sheets, you can switch to each one, select the target rows or ranges, and delete them manually. While time-consuming for large numbers, this method avoids the risk of unintended worksheet-wide changes.
- Using Excel Formula (Indirect method): In some complex scenarios, you can use formulas and helper columns (such as INDIRECT, MATCH, and IF functions) to flag rows for removal, then manually delete them after filtering. This technique is more suited to marking data instead of direct deletion across sheets but can aid in visual verification before manual removal.
Related articles:
- How to delete rows with missing/blank values in certain columns in Excel?
- How to delete rows if cell is greater/less than a certain value in Excel?
- How to delete all rows except the first header row in Excel?
- How to delete all rows below certain row or active cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in